CODEWITHSUNDEEP
sql-serverstored-procedures
SuperFrog
SuperFrog

Reputation: 7674

Transaction deadlocked while long select

I have a nightly job which execute a stored procedure that goes over a table and fetches records to be inserted to another table.

The procedure duration is about 4-5 minutes in which it executes 6 selects over a table with ~3M records.

While this procedure is running there are exceptions thrown from another stored procedure which trying to update the same table:

  1. Transaction (Process ID 166) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

  2. Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I have read Why use a READ UNCOMMITTED isolation level? question, but didn't come to a conclusion what best fits my scenario, as one of the comments stated:

"The author seems to imply that read uncommitted / no lock will return whatever data was last committed. My understanding is read uncommitted will return whatever value was last set even from uncommitted transactions. If so, the result would not be retrieving data "a few seconds out of date". It would (or at least could if the transaction that wrote the data you read gets rolled back) be retrieving data that doesn't exist or was never committed"

Taking into consideration that I only care about the state of the rows at the moment the nightly job started (the updates in the meanwhile will be calculated in the next one) What would be most appropriate approach?

Upvotes: 2

Views: 893

Answers (1)

PSK
PSK

Reputation: 17943

Transaction (Process ID 166) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

This normally happens when you read data with the intention to update it later by just putting a shared lock, the following UPDATE statement can’t acquire the necessary Update Locks, because they are already blocked by the Shared Locks acquired in the different session causing the deadlock.

To resolve this you can select the records using UPDLOCK like following

SELECT * FROM [Your_Table] WITH (UPDLOCK) WHERE A=B

This will take the necessary Update lock on the record in advance and will stop other sessions to acquire any lock (shared/exclusive) on the record and will prevent from any deadlocks.

Another common reason for the deadlock (Cycle Deadlock) is due to the order of the statements your put in your query, where in the end every query waits for another one in different transactions. For this type of scenarios you have to visit your query and fix the ordering issue.

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

This is very clear, you need to work on the query performance, and keep the record locking as less as possible.

Upvotes: 3

Related Questions