Deadlock – I do not understand the lock sequence. Are these table-locks or index-locks??
In the 2 Deadlock graphs you provide, you have the situation where each transaction is trying to access the same key ranges in 2 indexes on the same table. Both transactions are asking for either an Update lock or and Exclusive lock on the same resources resulting in the stalemate or a Deadlock condition.
In the Delete Deadlock example, here is the flow of the locking sequence resulting in the Deadlock.
- Spid 27 obtains an Update Key lock on ESS.dbo.tblWorkflowServerQueue1.IX_WorkflowItemID
- Spid 183 gets Exclusive lock on ESS.dbo.tblWorkflowServerQueue1.IX_ExecutionPriority
- Spid 27 gets an Update lock on ESS.dbo.tblWorkflowServerQueue1.IX_ExecutionPriority
- Spid 183 gets Exclusive lock on ESS.dbo.tblWorkflowServerQueue1.IX_WorkflowItemID