Deadlock – I do not understand the lock sequence. Are these table-locks or index-locks??

RichardBrianSmith 2016-02-23 11:19:35

SDyckes2 2016-02-23 16:00:20
A Key Lock is defined as a row lock within an index used to protect key ranges in serializable transactions. So these are 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.

  1. Spid 27 obtains an Update Key lock on ESS.dbo.tblWorkflowServerQueue1.IX_WorkflowItemID
  2. Spid 183 gets Exclusive lock on ESS.dbo.tblWorkflowServerQueue1.IX_ExecutionPriority
  3. Spid 27 gets an Update lock on ESS.dbo.tblWorkflowServerQueue1.IX_ExecutionPriority
  4. Spid 183 gets Exclusive lock on ESS.dbo.tblWorkflowServerQueue1.IX_WorkflowItemID
RichardBrianSmith 2016-02-24 16:53:33
Thanks SDyckes2
RichardBrianSmith 2016-02-25 16:27:55
The direction of the arrows is interesting. I am imagining it as

  1. Spid 27 requested a lock, and then got a lock (a two way thing)
  2. spid 183 requested a lock, and then got a lock
  3. spid 27 requested a lock, and is waiting (arrow pointing away from spid)
  4. spid 183 requested a lock, and is waiting
SDyckes2 2016-02-25 22:30:56
You are about in the direction of the arrows, they do tell you if they have attained the lock (arrow points to the SPID) or if they are waiting for on the requested lock (arrow points to the resource, away from SPID).