Unsual DEADLOCK info

Question

Unsual DEADLOCK info

Bhuvnesh

SSC Guru

Points: 59351
More actions
December 29, 2014 at 12:56 am

#293863

Please help with DEADLOCK info,i am unable to find anything here
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x00000000B532C560 Xid Slot: 11, Wait Slot: 2, Task: 0x000000017A442748, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:31 TaskProxy:(0x00000001553EB0A0) Value:0x7a442748 Cost:(20/0)
SPID: 24 ECID: 31 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:2
Port: 0x00000000A949C490 Xid Slot: 15, Wait Slot: 1, Task: 0x00000000A8974088, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:27 TaskProxy:(0x00000001553EAF20) Value:0xa8974088 Cost:(20/0)
NULL
Node:3
Port: 0x000000013AC79760 Xid Slot: 2, Wait Slot: 15, Task: 0x000000008E99FB88, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:23 TaskProxy:(0x00000001553EADA0) Value:0x8e99fb88 Cost:(0/10000)
NULL
Node:4
Port: 0x000000015B7DE2E0 Xid Slot: 1, Wait Slot: 11, Task: 0x000000013B3AB708, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:18 TaskProxy:(0x00000001553EABC0) Value:0x3b3ab708 Cost:(0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x00000000A8B43280 Xid Slot: 8, Wait Slot: 3, Task: 0x0000000119F6F288, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:30 TaskProxy:(0x00000001553EB040) Value:0x19f6f288 Cost:(20/0)
SPID: 24 ECID: 30 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:2
Port: 0x00000000A949C490 Xid Slot: 15, Wait Slot: 1, Task: 0x00000000A8974088, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:27 TaskProxy:(0x00000001553EAF20) Value:0xa8974088 Cost:(20/0)
NULL
Node:3
Port: 0x0000000113432F40 Xid Slot: 3, Wait Slot: 15, Task: 0x000000017A443288, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:22 TaskProxy:(0x00000001553EAD40) Value:0x7a443288 Cost:(0/10000)
SPID: 24 ECID: 22 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:4
Port: 0x000000009645D100 Xid Slot: 1, Wait Slot: 8, Task: 0x000000013B3AB708, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:18 TaskProxy:(0x00000001553EABC0) Value:0x3b3ab708 Cost:(0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x000000010D301830 Xid Slot: 12, Wait Slot: 6, Task: 0x00000000E9A7E988, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:26 TaskProxy:(0x00000001553EAEC0) Value:0xe9a7e988 Cost:(20/0)
SPID: 24 ECID: 26 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:2
Port: 0x00000000A949C490 Xid Slot: 15, Wait Slot: 1, Task: 0x00000000A8974088, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:27 TaskProxy:(0x00000001553EAF20) Value:0xa8974088 Cost:(20/0)
NULL
Node:3
Port: 0x000000015E58AC80 Xid Slot: 6, Wait Slot: 15, Task: 0x00000000FDBAABC8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:21 TaskProxy:(0x00000001553EACE0) Value:0xfdbaabc8 Cost:(0/10000)
SPID: 24 ECID: 21 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:4
Port: 0x00000001637DAC40 Xid Slot: 1, Wait Slot: 12, Task: 0x000000013B3AB708, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:24 BatchID:0 ECID:18 TaskProxy:(0x00000001553EABC0) Value:0x3b3ab708 Cost:(0/10000)
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Using 'dbghelp.dll' version '4.0.5'
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x00000000F676D590 Xid Slot: 13, Wait Slot: 1, Task: 0x00000000B58A1288, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:27 BatchID:0 ECID:27 TaskProxy:(0x000000017A0CCF20) Value:0xb58a1288 Cost:(20/0)
SPID: 27 ECID: 27 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:2
Port: 0x000000015D9394A0 Xid Slot: 11, Wait Slot: 2, Task: 0x00000000E9A7E748, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:27 BatchID:0 ECID:29 TaskProxy:(0x000000017A0CCFE0) Value:0xe9a7e748 Cost:(20/0)
SPID: 27 ECID: 29 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:3
Port: 0x000000013D264210 Xid Slot: 2, Wait Slot: 13, Task: 0x000000013B3ABDC8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:27 BatchID:0 ECID:18 TaskProxy:(0x000000017A0CCBC0) Value:0x3b3abdc8 Cost:(0/10000)
SPID: 27 ECID: 18 Statement Type: INSERT Line #: 17
Input Buf: No Event:
NULL
Node:4
Port: 0x00000000874BAA30 Xid Slot: 1, Wait Slot: 11, Task: 0x000000008E99F288, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:27 BatchID:0 ECID:17 TaskProxy:(0x000000017A0CCB60) Value:0x8e99f288 Cost:(0/10000)
SPID: 27 ECID: 17 Statement Type: INSERT Line #: 17
Input Buf: No Event:
Using 'dbghelp.dll' version '4.0.5'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

To start, turn traceflag 1204 off and use the 'newer' one that was introduced in SQL 2005 and which produces better deadlock graphs with more details. 1204 often doesn't produce enough information to debug.

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

ScottPletcher SSC Guru Points: 100949 More actions · Answer 2

Or, more specifically, run these commands:

DBCC TRACEOFF ( 1204, -1 )

DBCC TRACEON ( 1222, -1 )

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

thanks scott and gail

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ScottPletcher SSC Guru Points: 100949 More actions · Answer 4

Btw, either add 1222 setting directly to the start-up flags or add it to a start-up proc. Otherwise you'll lose that flag setting when SQL stops and restarts.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.