Deadlock

Question

Post reply

Deadlock

Rajesh Kumaran

SSC Eights!

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

#166627

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 4 posts - 1 through 3 (of 3 total)

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

Anton Pieterse Newbie Points: 9 More actions · Answer 1

Anton Pieterse

Newbie

Points: 9

November 9, 2005 at 2:26 am

#602964

What command did you use to get this in SQL?

Rajesh Kumaran SSC Eights! Points: 971 More actions · Answer 2

I ran a particular option( by two users) from the application and this situation arised.

Okafor Mr or Mrs. 500 Points: 596 More actions · Answer 3

You could try enabling certain trace flags 1204 and 1205. These have helped me identify the cause when I have a deadlock problem.

This link may help... http://www.sql-server-performance.com/ac_2000_trace_flags.asp