I have spent a few days trying to debug a deadlock that occurs in production. This is one I didn't understand however. Below is pseudo code for the stored procedure. The stored procedure generates a deadlock with another instance of itself.

I know that the code is not brilliant, this old code on the system. But I want to understand why this deadlocks. The graph always shows the second selects deadlocking.

The first select performs a table scan, so I believe that places click on all rows in table.

I'd have thought this would queue one stored procedure behind the other. I cannot recreate this after days if trying.

Begin tran

Begin tran

Set isolation level repeatable read

//This select does a table scan, so I believe does X lock on all rows

Select a,b,c
From queuetable with(xlock, rowlock)
Where a = x

Delete from queuetable
Where a = X

Commit tran

Begin tran 

//Get oldest row - this is the deadlocking query

Select top 1 a,b,c
From queuetable
Order by b

//Mark row to be processed
Update queuetable
Set c=processing
Where a = y

Commit tran

Commit tran
  • Repeatable read is the culprit here. I had faced similar scenario, changed that to read committed and issue is gone to a majority extent. Nov 21 '19 at 4:11
  • Is there a reason for the nested transactions? The inner BEGIN/COMMIT has no effect... (There are no such things as subtransactions in SQL Server - nested transactions are only in syntax, not semantics.) Nov 21 '19 at 7:28
  • There is no reason for the nested transaction, it's old code and one of the things I will remove. I believe the repeatable read is the problem, however I want to understand why. The first select does a table scan and uses click, rowlock, which I think means all the rows have to be scanned and that all rows are given and X lock. Therefore the other instance should queue at the first select. Nov 21 '19 at 7:50
  • 1
    Do you by any chance have the deadlock xml? Adding that could help in explaining. Nov 21 '19 at 7:55
  • Maybe, you should add HOLDLOCK hint on your select query, so that locks are held until a transaction is completed. Nov 21 '19 at 7:56

Try without the first BEGIN TRAN and last COMMIT TRAN.

If you are using a stored procedure, the procedure itself already creates a transaction.

Your transaction locks because of the following script:

//This select does a table scan, so I believe does X lock on all rows
Select a,b,c
From queuetable with(xlock, rowlock)
Where a = x

From what I see in the script, the with(xlock, rowlock) is not needed.

Try with the following script:


-- This select does a table scan, so I believe does X lock on all rows
SELECT a, b, c
  FROM queuetable
 WHERE a = x

DELETE queuetable
 WHERE a = X

-- Get oldest row - this is the deallocking query
SELECT TOP 1 a, b, c
  FROM queuetable

-- Mark row to be processed
UPDATE queuetable
   SET c = processing
 WHERE a = y
  • I have tried this but that causes more deadlocks earlier on at the delete. 2 instances do the select getting compatible shares lock. However because of the repeatable read they both when one tries to delete the select lock is escalated to a X lock so the read is repeatable in the transaction. However, it can't go on to delete because the over instance also wants an X lock on the select so it's repeatable. Neither can move on and we deadlock. Nov 21 '19 at 9:24
  • Hi Dan, roger that. Can you please tell why do you need both SELECT?
    – pncsoares
    Nov 21 '19 at 9:36
  • First select is find current item that's has finished processing. The delete this. That could all be in the delete itself I know, as I say old code someone else wrote. Second select is get next oldest item in queue to process. The system is multi threaded at app level. Nov 21 '19 at 9:39
  • Ok, but you can change the script right? The "old code someone wrote" is not excuse. In my opinion you need to rethink the script based on what you need. I think that you don't need to SELECT the table in this script operation. Are you showing this data in some view or saving it in a model?
    – pncsoares
    Nov 21 '19 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.