How to fix An error has occurred during report processing. (rsProcessingAborted) Cannot read the next data row for the dataset 'Work'. (rsErrorReadingNextDataRow) Transaction (Process ID 1461) was deadlocked on lock resources with another process and has

Question

How do I fix the following error message?

An error has occurred during report processing. (rsProcessingAborted) Cannot read the next data row for the dataset 'Work'. (rsErrorReadingNextDataRow) Transaction (Process ID 1461) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Answer 1

The error message you are encountering indicates that there is a deadlock situation occurring in SQL Server, which means that two or more processes are waiting for each other to release locks on resources, causing them to be unable to proceed. Here are some steps you can take to resolve this issue:

1. Identify and Optimize Queries: Review the queries involved in the report processing to ensure they are optimized. Look for any long-running queries that may be contributing to the deadlock.
2. Use Retry Logic: Implement retry logic in your application to handle deadlocks gracefully. When a deadlock occurs, the system will choose one of the transactions as a victim and roll it back, allowing the other transaction to proceed. By retrying the transaction, you may be able to complete it successfully.
3. Increase Isolation Level: If appropriate, consider changing the isolation level of your transactions to reduce the likelihood of deadlocks. However, be aware that this may have other implications on performance and concurrency.
4. Check for Blocking Processes: Use SQL Server Management Studio (SSMS) to identify any blocking processes that may be contributing to the deadlock. You can use the Activity Monitor or run queries against system views like sys.dm_exec_requests to find blocking sessions.
5. Review Indexes: Ensure that the tables involved in the report have appropriate indexing. Proper indexing can help reduce the time locks are held and minimize the chances of deadlocks.
6. Update SQL Server: Ensure that you are running the latest cumulative updates for SQL Server, as updates often include fixes for known issues, including those related to deadlocks.

If the issue persists after trying these steps, you may need to analyze the deadlock graph to get more insights into the specific resources that are being locked and the processes involved.

---

References:

• Troubleshoot processing of Reporting Services reports

Answer 2

Deadlocks are an expected behavior. While you might be able to redesign the schema and queries to limit deadlocks, you can not always achieve this. The proper coding is to catch the error, sleep a random time and retry the transaction.