Deadlocks Involving Locks

When a deadlock involves locks, Trace Flag 1204 reports the resource on which the deadlock is waiting, the lock mode in effect and being requested, and the threads participating in the cycle of dependency.

Examples

A Complete 1204 Report on Deadlock Involving Locks

In this example, SPID 51 is blocked from its request for an exclusive (X) lock on Table 2:2009058193 because SPID 52 already has an exclusive lock on it. In Node 2, SPID 52 is blocked from its request for an exclusive lock on Table 2:1993058136 because SPID 51 has an exclusive lock on it.

SQL Server chose SPID 52 as the deadlock victim to break the deadlock, as indicated by the Victim Resource Owner entry.

Deadlock encountered .... Printing deadlock information

Wait-for graph

Node:1
 TAB: 2:2009058193 []           CleanCnt:1 Mode: X Flags: 0x0
 Grant List::
   Owner:0x1c3b5260 Mode: X        Flg:0x0 Ref:1 Life:02000000 SPID:52 ECID:0
   SPID: 52 ECID: 0 Statement Type: SELECT Line #: 1
   Input Buf: Language Event: select * from a (tablockx)

 Requested By:
   ResType:LockOwner Stype:'OR' Mode: X SPID:51 ECID:0 Ec:(0x1c657890) Value:0x1c3b51c0 Cost:(0/0)

Node:2
 TAB: 2:1993058136 []           CleanCnt:1 Mode: X Flags: 0x0
 Grant List::
   Owner:0x1c3b52e0 Mode: X        Flg:0x0 Ref:1 Life:02000000 SPID:51 ECID:0
   SPID: 51 ECID: 0 Statement Type: SELECT Line #: 1
   Input Buf: Language Event: select * from b (tablockx)

 Requested By:
   ResType:LockOwner Stype:'OR' Mode: X SPID:52 ECID:0 Ec:(0x1c593890) Value:0x1c3b5380 Cost:(0/0)
Victim Resource Owner:
 ResType:LockOwner Stype:'OR' Mode: X SPID:52 ECID:0 Ec:(0x1c593890) Value:0x1c3b5380 Cost:(0/0)