Sample Header Ad - 728x90

Unusual deadlock occuring

0 votes
1 answer
149 views
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