Export (0) Print
Expand All

Deadlocks Involving Parallelism

SQL Server 2000

Deadlocks involving parallelism always involve thread or lock resources. Parallelism alone cannot cause a deadlock. The information reported by Trace Flag 1204 allows users to trace the separate parallel workers and find the other resource contention issues that are causing the deadlock. Treat all parallel threads (for example, threads with the same SPID) as a single unit when tracing the deadlock.

Trace Flag 1204 reports on these sets of participants in the cycle: Coordinator, Consumer, and Producer. The Coordinator is the creator and destroyer of the sub-threads. The Producers produce information on which the consumers wait.

To resolve the deadlock involving one nonparallel SPID AA and a parallel SPID BB, follow the same procedure as you would to resolve a deadlock between two nonparallel SPIDs.

Examples
A. Deadlock Involving Parallelism

The following example shows how the Coordinator, Consumer, and Producer are presented in a Trace Flag 1204 report.

Node:2
Port: 0x2b968100  Xid Slot: -1, EC: 0x2bba53b0, SPID:59 ECID: 0 (Coordinator), Exchange
Wait Type :e_etypeClose
Coordinator: EC = 0x2bba53b0, SPID:59 ECID: 0, Not Blocking
Consumer List::
Consumer: Xid Slot: 0, EC = 0x2bbda090, SPID:59 ECID: 2, Blocking
Consumer: Xid Slot: 1, EC = 0x2bbce090, SPID:59 ECID: 4, Blocking
Consumer: Xid Slot: 4, EC = 0x2bb60090, SPID:59 ECID: 1, Blocking
Consumer: Xid Slot: 5, EC = 0x2bbd4090, SPID:59 ECID: 3, Blocking
Producer List::
Producer: Xid Slot: 2, EC = 0x2bb64090, SPID:59 ECID: 6, Blocking
Producer: Xid Slot: 3, EC = 0x2bbbe090, SPID:59 ECID: 8, Blocking
Producer: Xid Slot: 6, EC = 0x2bbca090, SPID:59 ECID: 5, Blocking
Producer: Xid Slot: 7, EC = 0x2bbc6090, SPID:59 ECID: 7, Blocking
B. Deadlocks with Branches

Trace Flag 1204 reports deadlock cycles that have branches. For example, the following excerpt shows one branch, Node 6, in which a thread has an exclusive (X) lock on RID 1:1:1253:0, while SPID 55 is waiting for an update (U) lock on it and SPID 60 has just requested another update (U) lock on it.

-- next branch --
Node:6
 RID: 1:1:1253:0                CleanCnt:5 Mode: X Flags: 0x2 
 Wait List:
   Owner:0x98b9d7c Mode: U        Flg:0x0 Ref:1  Life:02000000 SPID:55 ECID:0
 Requested By:
   ResType:LockOwner Stype:'OR' Mode: U SPID:60

-- next branch --

Node:9
 RID: 1:1:1253:0                CleanCnt:5 Mode: X Flags: 0x2
 Wait List:
   Owner:0x98b9d7c Mode: U        Flg:0x0 Ref:1 Life:02000000 SPID:55 ECID:0
 Requested By:
   ResType:LockOwner Stype:'OR' Mode: U SPID:59 ECID:0 Ec:(0x9b09494) Value:0x98b9c5c Cost:(0/0)
-- next branch --

In deadlocks involving parallelism and threads, a thread can wait on multiple threads. Consequently, each instance of "Next Branch" in the Trace Flag 1204 report represents the next wait for a particular thread.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft