Deadlock

Question

Post reply

Deadlock

Rajesh Kumaran

SSC Eights!

Points: 971
More actions
November 9, 2005 at 2:13 am

#90803

If anyone could hep me out to interpret the below given deadlock situation that would be very much appreciated.
2005-11-08 17:03:10.87 spid4 ResType:ExchangeId Stype:'AND' SPID:70 ECID:0 Ec0x2CEB5558) Value:0x42bf2dac
2005-11-08 17:03:10.87 spid4 Victim Resource Owner:
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2cd5e098, SPID: 61, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d18a098, SPID: 61, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d756098, SPID: 61, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2ca86098, SPID: 61, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Port: 0x42bf1e00 Xid Slot: 0, EC: 0x2cb9f558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:1
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 -- next branch --
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:2 Ec0x2CD5E098) Value:0x5cd
2005-11-08 17:03:10.87 spid4 Requested By:
2005-11-08 17:03:10.87 spid4 Owner:0xf0372e0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:1
2005-11-08 17:03:10.87 spid4 Wait List:
2005-11-08 17:03:10.87 spid4 PAG: 11:1:17609                CleanCnt:4 Mode: IX Flags: 0x0
2005-11-08 17:03:10.87 spid4 Node:24
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2cd5e098, SPID: 61, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d18a098, SPID: 61, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d756098, SPID: 61, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2ca86098, SPID: 61, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Port: 0x42bf1e00 Xid Slot: 0, EC: 0x2cb9f558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:1
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 -- next branch --
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec0x2D18A098) Value:0x5f1
2005-11-08 17:03:10.87 spid4 Requested By:
2005-11-08 17:03:10.87 spid4 Owner:0xf0372e0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:1
2005-11-08 17:03:10.87 spid4 Wait List:
2005-11-08 17:03:10.87 spid4 PAG: 11:1:17609                CleanCnt:4 Mode: IX Flags: 0x0
2005-11-08 17:03:10.87 spid4 Node:21
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2cd5e098, SPID: 61, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d18a098, SPID: 61, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d756098, SPID: 61, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2ca86098, SPID: 61, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Port: 0x42bf1e00 Xid Slot: 0, EC: 0x2cb9f558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:1
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 -- next branch --
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2cd5e098, SPID: 61, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d18a098, SPID: 61, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d756098, SPID: 61, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2ca86098, SPID: 61, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Port: 0x42bf1e00 Xid Slot: 0, EC: 0x2cb9f558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:1
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 -- next branch --
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2d574098, SPID: 70, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d64a098, SPID: 70, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d560098, SPID: 70, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2d22c098, SPID: 70, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Port: 0x42bf0600 Xid Slot: 0, EC: 0x2ceb5558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 -- next branch --
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:70 ECID:1 Ec0x2D574098) Value:0x5cc
2005-11-08 17:03:10.87 spid4 Requested By:
2005-11-08 17:03:10.87 spid4 Owner:0x5cd03740 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:62 ECID:3
2005-11-08 17:03:10.87 spid4 Wait List:
2005-11-08 17:03:10.87 spid4 PAG: 11:1:1244                 CleanCnt:5 Mode: IX Flags: 0x0
2005-11-08 17:03:10.87 spid4 Node:15
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2d574098, SPID: 70, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d64a098, SPID: 70, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d560098, SPID: 70, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2d22c098, SPID: 70, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Port: 0x42bf0600 Xid Slot: 0, EC: 0x2ceb5558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 -- next branch --
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:70 ECID:4 Ec0x2D64A098) Value:0x5f1
2005-11-08 17:03:10.87 spid4 Requested By:
2005-11-08 17:03:10.87 spid4 Owner:0x5cd03740 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:62 ECID:3
2005-11-08 17:03:10.87 spid4 Wait List:
2005-11-08 17:03:10.87 spid4 PAG: 11:1:1244                 CleanCnt:5 Mode: IX Flags: 0x0
2005-11-08 17:03:10.87 spid4 Node:12
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2d574098, SPID: 70, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d64a098, SPID: 70, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d560098, SPID: 70, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2d22c098, SPID: 70, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Port: 0x42bf0600 Xid Slot: 0, EC: 0x2ceb5558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 -- next branch --
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:70 ECID:2 Ec0x2D560098) Value:0x6fd
2005-11-08 17:03:10.87 spid4 Requested By:
2005-11-08 17:03:10.87 spid4 Owner:0x5cd03740 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:62 ECID:3
2005-11-08 17:03:10.87 spid4 Wait List:
2005-11-08 17:03:10.87 spid4 PAG: 11:1:1244                 CleanCnt:5 Mode: IX Flags: 0x0
2005-11-08 17:03:10.87 spid4 Node:9
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2d574098, SPID: 70, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d64a098, SPID: 70, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d560098, SPID: 70, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2d22c098, SPID: 70, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Port: 0x42bf0600 Xid Slot: 0, EC: 0x2ceb5558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 -- next branch --
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:62 ECID:3 Ec0x2D5A4098) Value:0x5cd
2005-11-08 17:03:10.87 spid4 Requested By:
2005-11-08 17:03:10.87 spid4 Owner:0x5af1f440 Mode: IX       Flg:0x0 Ref:0 Life:02000000 SPID:61 ECID:0
2005-11-08 17:03:10.87 spid4 Grant List 1::
2005-11-08 17:03:10.87 spid4 PAG: 11:1:1244                 CleanCnt:5 Mode: IX Flags: 0x0
2005-11-08 17:03:10.87 spid4 Node:6
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:70 ECID:3 Ec0x2D22C098) Value:0x5cd
2005-11-08 17:03:10.87 spid4 Requested By:
2005-11-08 17:03:10.87 spid4 Input Buf: Language Event: SELECT
2005-11-08 17:03:10.87 spid4 SPID: 62 ECID: 3 Statement Type: SELECT Line #: 1
2005-11-08 17:03:10.87 spid4 Owner:0x5cd03740 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:62 ECID:3
2005-11-08 17:03:10.87 spid4 Wait List:
2005-11-08 17:03:10.87 spid4 PAG: 11:1:1244                 CleanCnt:5 Mode: IX Flags: 0x0
2005-11-08 17:03:10.87 spid4 Node:5
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2d574098, SPID: 70, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d64a098, SPID: 70, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d560098, SPID: 70, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2d22c098, SPID: 70, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2ceb5558, SPID: 70, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Port: 0x42bf0600 Xid Slot: 0, EC: 0x2ceb5558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:4
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:1 Ec0x2D756098) Value:0xf03
2005-11-08 17:03:10.87 spid4 Requested By:
2005-11-08 17:03:10.87 spid4 Grant List 3::
2005-11-08 17:03:10.87 spid4 Input Buf: Language Event: SELECT
2005-11-08 17:03:10.87 spid4 SPID: 70 ECID: 0 Statement Type: SELECT Line #: 1
2005-11-08 17:03:10.87 spid4 Owner:0x74edaac0 Mode: IX       Flg:0x0 Ref:0 Life:02000000 SPID:70 ECID:0
2005-11-08 17:03:10.87 spid4 Grant List 1::
2005-11-08 17:03:10.87 spid4 PAG: 11:1:17609                CleanCnt:4 Mode: IX Flags: 0x0
2005-11-08 17:03:10.87 spid4 Node:3
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:4 Ec0x2CA86098) Value:0xf82
2005-11-08 17:03:10.87 spid4 Requested By:
2005-11-08 17:03:10.87 spid4 Owner:0xf0372e0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:1
2005-11-08 17:03:10.87 spid4 Wait List:
2005-11-08 17:03:10.87 spid4 PAG: 11:1:17609                CleanCnt:4 Mode: IX Flags: 0x0
2005-11-08 17:03:10.87 spid4 Node:2
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 4, EC = 0x2cd5e098, SPID: 61, ECID: 2, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 3, EC = 0x2d18a098, SPID: 61, ECID: 3, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 2, EC = 0x2d756098, SPID: 61, ECID: 1, Blocking
2005-11-08 17:03:10.87 spid4 Producer: Xid Slot: 1, EC = 0x2ca86098, SPID: 61, ECID: 4, Blocking
2005-11-08 17:03:10.87 spid4 Producer List::
2005-11-08 17:03:10.87 spid4 Consumer: Xid Slot: 0, EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Consumer List::
2005-11-08 17:03:10.87 spid4 Coordinator: EC = 0x2cb9f558, SPID: 61, ECID: 0, Not Blocking
2005-11-08 17:03:10.87 spid4 Input Buf: Language Event: SELECT
2005-11-08 17:03:10.87 spid4 SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
2005-11-08 17:03:10.87 spid4 Port: 0x42bf1e00 Xid Slot: 0, EC: 0x2cb9f558, ECID: 0 (Coordinator), Exchange
2005-11-08 17:03:10.87 spid4 Node:1
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 Wait-for graph
2005-11-08 17:03:10.87 spid4
2005-11-08 17:03:10.87 spid4 ...

Regards
Rajesh

Viewing 2 posts - 1 through 1 (of 1 total)

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

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 1

This problem is due to parallel query execution, blocking resources.

I have gone through this and recommend you to find the query.

In my case it was one Stored Proc. where I have then

used #temptable to hold results,separately and then process data through

update table and that has worked fine. My understanding is, it is due to too

much of memory allocation requirement and if realease of memory is slow you

start seeing blocking everywhere.

Check you have latest service pack, as well go through this MS article.

http://support.microsoft.com/?id=293232

Good luck,

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com