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
Asked by BeginnerDBADan
(99 rep)
Nov 20, 2019, 11:20 PM
Last activity: Nov 21, 2019, 12:32 PM
Last activity: Nov 21, 2019, 12:32 PM