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