October 16, 2003 at 8:44 am
I need help regarding a deadlock. I setup in sql2000 a trace (-T3604 -T1204 -T1205).
I have something in the errorlog in sql.
Could someone give me explanation regarding my example or where can I find explanation regarding how read this trace ?
short example of a deadlock trace :
Deadlock encountered .... Printing deadlock information
2003-10-07 15:07:38.94 spid2
2003-10-07 15:07:38.94 spid2 Wait-for graph
2003-10-07 15:07:38.94 spid2
2003-10-07 15:07:38.94 spid2 Node:1
2003-10-07 15:07:38.94 spid2 Port: 0x42be8300 Xid Slot: 0, EC: 0x27bd39a0, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2003-10-07 15:07:38.94 spid2 SPID: 131 ECID: 0 Statement Type: UPDATE Line #: 186
2003-10-07 15:07:38.94 spid2 Input Buf: Language Event: INSERT INTO lfdeals ( lfd_code, lfd_number, lfd_seq, lfd_orderid, lfd_orderseq, lfd_frontofficeid, lfd_status, lfd_iuser, lfd_idate, lfd_vuser, lfd_vdate, lfd_customer, lfd_pfolio, lfd_acttype, lfd_transcode, lfd_ccy, lfd_ccyrate, lfd_1set_method, lfd_1an
2003-10-07 15:07:38.94 spid2 Coordinator: EC = 0x27bd39a0, SPID: 131, ECID: 0, Not Blocking
2003-10-07 15:07:38.94 spid2 Consumer List::
2003-10-07 15:07:38.94 spid2 Consumer: Xid Slot: 0, EC = 0x27bd39a0, SPID: 131, ECID: 0, Not Blocking
2003-10-07 15:07:38.94 spid2 Producer List::
2003-10-07 15:07:38.94 spid2 Producer: Xid Slot: 1, EC = 0x2a426098, SPID: 131, ECID: 2, Blocking
2003-10-07 15:07:38.94 spid2 Producer: Xid Slot: 2, EC = 0x3a9f2098, SPID: 131, ECID: 1, Blocking
2003-10-07 15:07:38.94 spid2
2003-10-07 15:07:38.94 spid2 Node:2
2003-10-07 15:07:38.94 spid2 PAG: 6:1:56182 CleanCnt:3 Mode: IX Flags: 0x0
2003-10-07 15:07:38.94 spid2 Wait List:
2003-10-07 15:07:38.94 spid2 Owner:0x55332e60 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:57 ECID:1
2003-10-07 15:07:38.94 spid2 SPID: 57 ECID: 1 Statement Type: UPDATE Line #: 208
2003-10-07 15:07:38.94 spid2 Input Buf: Language Event: INSERT INTO lfdeals ( lfd_code, lfd_number, lfd_seq, lfd_orderid, lfd_orderseq, lfd_frontofficeid, lfd_status, lfd_iuser, lfd_idate, lfd_vuser, lfd_vdate, lfd_customer, lfd_pfolio, lfd_acttype, lfd_transcode, lfd_ccy, lfd_ccyrate, lfd_1set_method, lfd_1an
2003-10-07 15:07:38.94 spid2 Requested By:
2003-10-07 15:07:38.94 spid2 ResType:LockOwner Stype:'OR' Mode: S SPID:131 ECID:2 Ec:(0x2A426098) Value:0x55408680 Cost:(0/4AC)
2003-10-07 15:07:38.94 spid2
2003-10-07 15:07:38.94 spid2 Node:3
2003-10-07 15:07:38.94 spid2 PAG: 6:1:56182 CleanCnt:3 Mode: IX Flags: 0x0
2003-10-07 15:07:38.94 spid2 Grant List 0::
2003-10-07 15:07:38.94 spid2 Grant List 1::
2003-10-07 15:07:38.94 spid2 Owner:0x553b52e0 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:131 ECID:0
2003-10-07 15:07:38.94 spid2 Requested By:
2003-10-07 15:07:38.94 spid2 ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:1 Ec:(0x3AF90098) Value:0x55332e60 Cost:(0/45C)
2003-10-07 15:07:38.94 spid2
2003-10-07 15:07:38.95 spid2 -- next branch --
2003-10-07 15:07:38.95 spid2
2003-10-07 15:07:38.95 spid2
2003-10-07 15:07:38.95 spid2 Node:1
2003-10-07 15:07:38.95 spid2 Port: 0x42be8300 Xid Slot: 0, EC: 0x27bd39a0, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2003-10-07 15:07:38.95 spid2 Coordinator: EC = 0x27bd39a0, SPID: 131, ECID: 0, Not Blocking
2003-10-07 15:07:38.95 spid2 Consumer List::
2003-10-07 15:07:38.95 spid2 Consumer: Xid Slot: 0, EC = 0x27bd39a0, SPID: 131, ECID: 0, Not Blocking
2003-10-07 15:07:38.95 spid2 Producer List::
2003-10-07 15:07:38.95 spid2 Producer: Xid Slot: 1, EC = 0x2a426098, SPID: 131, ECID: 2, Blocking
2003-10-07 15:07:38.95 spid2 Producer: Xid Slot: 2, EC = 0x3a9f2098, SPID: 131, ECID: 1, Blocking
2003-10-07 15:07:38.95 spid2
2003-10-07 15:07:38.95 spid2 Node:6
2003-10-07 15:07:38.95 spid2 PAG: 6:1:56182 CleanCnt:3 Mode: IX Flags: 0x0
2003-10-07 15:07:38.95 spid2 Wait List:
2003-10-07 15:07:38.95 spid2 Owner:0x55332e60 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:57 ECID:1
2003-10-07 15:07:38.95 spid2 Requested By:
2003-10-07 15:07:38.95 spid2 ResType:LockOwner Stype:'OR' Mode: S SPID:131 ECID:1 Ec:(0x3A9F2098) Value:0x5536a500 Cost:(0/4AC)
2003-10-07 15:07:38.95 spid2
2003-10-07 15:07:38.95 spid2 -- next branch --
Thanks you in advance
October 16, 2003 at 9:26 am
I know only a few things:
The input buf lines give you the first 255 characters of the statement. I hope you can figure out the rest of the statement from that.
PAG: 6:1:56182 tells you the dbid, filenr and page.
If the page did not change since then you can see what is there with:
<code>
DBCC TRACEON (3604)
DBCC PAGE (6,1,56182,3)
DBCC TRACEOFF (3604)
</code>
It will give you the table anyway.
October 16, 2003 at 9:50 am
Your statements craeted deadlock on page PAG: 6:1:56182 involving parallelism from both spid 131 and spid 57. SPID: 131 main thread has locked above page but its sub-threads (1,2) are either waitting or requested lock resource on same page. At same time, spid 57 is waitting/requesting lock resource to the same page.
You may try to add hints "MAXDOP 1" to your statement to force SQL Server to suppress parallel plan generation, ie use one CPU to run the statement.
As general rules to help minimize deadlocks:
Access objects in the same order.
Avoid user interaction in transactions.
Keep transactions short and in one batch.
Use a low isolation level.
Use bound connections.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply