2

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?

SuperFrog
  • 7,631
  • 9
  • 51
  • 81

1 Answers1

3

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.

PSK
  • 17,547
  • 5
  • 32
  • 43
  • Thank you. If I understood you correctly, then while the 4-5 minutes of the select run, there will be no updates to the table? Or is the lock on specific rows? – SuperFrog Apr 09 '18 at 10:52
  • You don't need to apply this to every select, as you can have any number of shared lock on a record, you only need to take the UPDLOCK on the records when you are going to update those records back in the same transaction. – PSK Apr 09 '18 at 10:54
  • I have 6 selects on the table, which I use the result in a cursor. I then iterate this cursor and insert to another table. So in this case do I need to use UPDLOCK on each of the 6 selects? – SuperFrog Apr 09 '18 at 12:13
  • If it is only select and insert, you don't need UPDLOCK for sure. – PSK Apr 09 '18 at 12:20
  • But you mentioned there are other transactions which are updating the same table, you need put the lock there. – PSK Apr 09 '18 at 12:21
  • Maybe I didn't clarify it in the original question. There is a stored procedure which only selects and inserts and takes about 4-5 minutes. In the meanwhile users are updating some data, using another SP, in the same table from which the first selects. The second SP throws the exceptions in the question while the first is executing. – SuperFrog Apr 09 '18 at 12:26
  • SELECT and INSERT will not block, only update will have issue, you try chaining the SP which is updating the table. – PSK Apr 09 '18 at 12:36