Deadlock due to intra-query parallelism

Question

Deadlock due to intra-query parallelism

Abdul Rahman

Say Hey Kid

Points: 708
More actions
December 10, 2012 at 11:36 pm

#271817

Hi
We have been having deadlocks a lot and to track them I enabled Trace Flags 1204 and 1222.
The output does not give adequate information and object details.
,spid6s,Unknown,Input Buf: No Event:
,spid6s,Unknown,SPID: 74 ECID: 24 Statement Type: SELECT Line #: 1
,spid6s,Unknown,ResType:ExchangeId Stype:'AND' SPID:74 BatchID:0 ECID:24 TaskProxy:(0x0000000A03F03DA0) Value:0x418d708 Cost:(0/10000)
,spid6s,Unknown,Port: 0x00000000803B3B00 Xid Slot: 4<c/> Wait Slot: 5<c/> Task: 0x000000000418D708<c/> (Consumer)<c/> Exchange Wait Type: e_waitPortOpen<c/> Merging: 0
,spid6s,Unknown,Node:2
,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
,spid6s,Unknown,Input Buf: No Event:
,spid6s,Unknown,SPID: 74 ECID: 23 Statement Type: SELECT Line #: 1
,spid6s,Unknown,ResType:ExchangeId Stype:'AND' SPID:74 BatchID:0 ECID:23 TaskProxy:(0x0000000A03F03D40) Value:0x4173048 Cost:(0/10000)
,spid6s,Unknown,Port: 0x00000000803B3B00 Xid Slot: 3<c/> Wait Slot: 4<c/> Task: 0x0000000004173048<c/> (Consumer)<c/> Exchange Wait Type: e_waitPortOpen<c/> Merging: 0
,spid6s,Unknown,Node:1
,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
,spid6s,Unknown,Wait-for graph
,spid6s,Unknown,Deadlock encountered .... Printing deadlock information
I even enabled service-side tracing with profiler and the output remains the same.
Parallel query worker thread was involved in a deadlock
Even the Deadlock Graph looks weird and does not provide any information.
I have checked online for solutions and have failed.
I doubt it could be because of any SSIS package or any application.
But one of the spid shows it's a system process and similar deadlocks have been happening a lot recently.
My question here is except for changing the threshold and MaxDOP, is there any other way to track down the objects for a solution ?
Any insight would be helpful. Thank you:-)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Firstly, turn traceflag 1204 off. It produces the old-style deadlock graph that's very hard to read. With just 1222 on, wait until another deadlock occurs and post the entire deadlock graph here please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Abdul Rahman Say Hey Kid Points: 708 More actions · Answer 2

Traceflag 1204 is off and this is the output of 1222.

,spid6s,Unknown,waiter id=process4a66748

,spid6s,Unknown,waiter-list

,spid6s,Unknown,owner id=process4a67b88

,spid6s,Unknown,owner id=process4a67dc8

,spid6s,Unknown,owner id=process4a67288

,spid6s,Unknown,owner id=process4a674c8