SQL Server Performance Forum – Threads Archive

Trying to remove deadlocks

Hi, I am new to the forum and pretty new to SQL. We have one app that is always creating deadlocks. Yesterday I turned on some trace flags and I have put the info into a text file but it may as well be Chinese to me. I was wondering if it is ok to post some of the text here for help deciphering. If not any links or help would be greatly appreciated.[?] Thanks (moved from Analysis forum)

Post your SQL Statement and txt file (if not to big) and we’ll see if there’s anything rather obvious. Other question – does this query ever work? or does it only fail at a set time of day? If it only fails at a set time of day it could be that another process is using the same tables used in the query you are trying to run. Howard.

To overcome this situation, you need to increase the Query Timeout for logreader, which is set to a default of 300 seconds. For example, you’d add the following parameter to log reader’s "Run Agent" step, to increase the query timeout: -QueryTimeOut 1000 To capture detailed deadlock information into the error logs, enable the trace flags 1204 and 3605 at the session level using the DBCC TRACEON command. When you enable these trace flags at the session level, only those deadlocks are captured into the error log, in which this session has participated. To enable these trace flags at the server level, start your SQL Server from command prompt (sqlservr.exe) with -T1204 and -T3605 parameters. You could also set these trace flags from Enterprise Manager. (Right click on the server, select ‘Properties’. Click on ‘Startup parameters…’. Add the parameters -T1204 and -T3605 one after another by clicking the ‘Add’ button.). After setting these trace flags in Enterprise Manager, you must restart your SQL Server service for these trace flags to take effect.

For tips and tricks refer the following links
http://www.sql-server-performance.com/deadlocks.asp
http://www.sql-server-performance.com/sf_block_prevention.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

It does seem to mostly happen around the same time of the day. Then there are times when we will go a few days without any. Anyway here is a portion of the deadlock trace. I will keep it small. They all seem to be almost the same anyway. Thanks for your help…. 2005-03-11 14:14:41.39 spid115 DBCC TRACEON 3605, server process ID (SPID) 115. 2005-03-11 14:14:41.39 spid115 DBCC TRACEON 1204, server process ID (SPID) 115. 2005-03-11 14:14:41.39 spid115 DBCC TRACEON 1, server process ID (SPID) 115. 2005-03-11 15:07:47.44 spid4 Deadlock encountered …. Printing deadlock information 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 Wait-for graph 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 Node:1 2005-03-11 15:07:47.44 spid4 RID: 7:1:190506:34 CleanCnt:1 Mode: X Flags: 0x2 2005-03-11 15:07:47.44 spid4 Grant List 3:: 2005-03-11 15:07:47.44 spid4 Owner:0x6313fa00 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:121 ECID:0 2005-03-11 15:07:47.44 spid4 SPID: 121 ECID: 0 Statement Type: SELECT Line #: 1 2005-03-11 15:07:47.44 spid4 Input Buf: Language Event: SELECT OrderID, OrderNum, CustomerID, PO#, OrderTotal, OrderType, OrderStatus, LastInvoiceNum FROM tblOEOrders WHERE (CustomerID = 43173) ORDER BY OrderNum DESC 2005-03-11 15:07:47.44 spid4 Requested By: 2005-03-11 15:07:47.44 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:92 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x63B1B9A<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x2c370300 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/41C) 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 Node:2 2005-03-11 15:07:47.44 spid4 Port: 0x19dc0100 Xid Slot: 0, EC: 0x7ee659a8, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers 2005-03-11 15:07:47.44 spid4 Coordinator: EC = 0x7ee659a8, SPID: 121, ECID: 0, Not Blocking 2005-03-11 15:07:47.44 spid4 Consumer List:: 2005-03-11 15:07:47.44 spid4 Consumer: Xid Slot: 0, EC = 0x7ee659a8, SPID: 121, ECID: 0, Blocking 2005-03-11 15:07:47.44 spid4 Producer List:: 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 1, EC = 0x41e78098, SPID: 121, ECID: 1, Blocking 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 2, EC = 0x41a36098, SPID: 121, ECID: 2, Blocking 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 3, EC = 0x48200098, SPID: 121, ECID: 3, Blocking 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 4, EC = 0x5489e098, SPID: 121, ECID: 4, Blocking 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 Node:3 2005-03-11 15:07:47.44 spid4 PAG: 7:1:202362 CleanCnt:4 Mode: IX Flags: 0x2 2005-03-11 15:07:47.44 spid4 Wait List: 2005-03-11 15:07:47.44 spid4 Owner:0x54520c40 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:121 ECID:2 2005-03-11 15:07:47.44 spid4 Requested By: 2005-03-11 15:07:47.44 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:121 ECID:1 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x41E7809<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x651690e0 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/7BC) 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 Node:4 2005-03-11 15:07:47.44 spid4 PAG: 7:1:202362 CleanCnt:4 Mode: IX Flags: 0x2 2005-03-11 15:07:47.44 spid4 Grant List 2:: 2005-03-11 15:07:47.44 spid4 Owner:0x2c371ca0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:92 ECID:0 2005-03-11 15:07:47.44 spid4 SPID: 92 ECID: 0 Statement Type: DELETE Line #: 56 2005-03-11 15:07:47.44 spid4 Input Buf: RPC Event: phssp_ConvertToOrder ;1 2005-03-11 15:07:47.44 spid4 Requested By: 2005-03-11 15:07:47.44 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:121 ECID:2 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x41A3609<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x54520c40 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/7BC) 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 — next branch – 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 Node:2 2005-03-11 15:07:47.44 spid4 Port: 0x19dc0100 Xid Slot: 0, EC: 0x7ee659a8, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers 2005-03-11 15:07:47.44 spid4 Coordinator: EC = 0x7ee659a8, SPID: 121, ECID: 0, Not Blocking 2005-03-11 15:07:47.44 spid4 Consumer List:: 2005-03-11 15:07:47.44 spid4 Consumer: Xid Slot: 0, EC = 0x7ee659a8, SPID: 121, ECID: 0, Blocking 2005-03-11 15:07:47.44 spid4 Producer List:: 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 1, EC = 0x41e78098, SPID: 121, ECID: 1, Blocking 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 2, EC = 0x41a36098, SPID: 121, ECID: 2, Blocking 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 3, EC = 0x48200098, SPID: 121, ECID: 3, Blocking 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 4, EC = 0x5489e098, SPID: 121, ECID: 4, Blocking 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 — next branch – 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 Node:2 2005-03-11 15:07:47.44 spid4 Port: 0x19dc0100 Xid Slot: 0, EC: 0x7ee659a8, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers 2005-03-11 15:07:47.44 spid4 Coordinator: EC = 0x7ee659a8, SPID: 121, ECID: 0, Not Blocking 2005-03-11 15:07:47.44 spid4 Consumer List:: 2005-03-11 15:07:47.44 spid4 Consumer: Xid Slot: 0, EC = 0x7ee659a8, SPID: 121, ECID: 0, Blocking 2005-03-11 15:07:47.44 spid4 Producer List:: 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 1, EC = 0x41e78098, SPID: 121, ECID: 1, Blocking 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 2, EC = 0x41a36098, SPID: 121, ECID: 2, Blocking 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 3, EC = 0x48200098, SPID: 121, ECID: 3, Blocking 2005-03-11 15:07:47.44 spid4 Producer: Xid Slot: 4, EC = 0x5489e098, SPID: 121, ECID: 4, Blocking 2005-03-11 15:07:47.44 spid4 2005-03-11 15:07:47.44 spid4 Node:9 2005-03-11 15:07:47.44 spid4 PAG: 7:1:202362 CleanCnt:4 Mode: IX Flags: 0x2 2005-03-11 15:07:47.44 spid4 Wait List: 2005-03-11 15:07:47.44 spid4 Owner:0x54520c40 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:121 ECID:2 2005-03-11 15:07:47.44 spid4 Requested By: 2005-03-11 15:07:47.44 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:121 ECID:3 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x4820009<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x2c3711c0 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x5489E09<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x7edf2c00 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/7BC) 2005-03-11 15:07:47.46 spid4 2005-03-11 15:07:47.46 spid4 — next branch – 2005-03-11 15:07:47.46 spid4 2005-03-11 15:07:47.46 spid4 2005-03-11 15:07:47.46 spid4 Node:2 2005-03-11 15:07:47.46 spid4 Port: 0x19dc0100 Xid Slot: 0, EC: 0x7ee659a8, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers 2005-03-11 15:07:47.46 spid4 Coordinator: EC = 0x7ee659a8, SPID: 121, ECID: 0, Not Blocking 2005-03-11 15:07:47.46 spid4 Consumer List:: 2005-03-11 15:07:47.46 spid4 Consumer: Xid Slot: 0, EC = 0x7ee659a8, SPID: 121, ECID: 0, Blocking 2005-03-11 15:07:47.46 spid4 Producer List:: 2005-03-11 15:07:47.46 spid4 Producer: Xid Slot: 1, EC = 0x41e78098, SPID: 121, ECID: 1, Blocking 2005-03-11 15:07:47.46 spid4 Producer: Xid Slot: 2, EC = 0x41a36098, SPID: 121, ECID: 2, Blocking 2005-03-11 15:07:47.46 spid4 Producer: Xid Slot: 3, EC = 0x48200098, SPID: 121, ECID: 3, Blocking 2005-03-11 15:07:47.46 spid4 Producer: Xid Slot: 4, EC = 0x5489e098, SPID: 121, ECID: 4, Blocking 2005-03-11 15:07:47.46 spid4 Victim Resource Owner: 2005-03-11 15:07:47.46 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:92 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x63B1B9A<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x2c370300 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/41C) 2005-03-11 15:10:30.08 spid4 Deadlock encountered …. Printing deadlock information 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Wait-for graph 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Node:1 2005-03-11 15:10:30.08 spid4 Port: 0x19dc0100 Xid Slot: 0, EC: 0x7ee659a8, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers 2005-03-11 15:10:30.08 spid4 SPID: 121 ECID: 0 Statement Type: SELECT Line #: 1 2005-03-11 15:10:30.08 spid4 Input Buf: Language Event: SELECT OrderID, OrderNum, CustomerID, PO#, OrderTotal, OrderType, OrderStatus, LastInvoiceNum FROM tblOEOrders WHERE (CustomerID = 43173) ORDER BY OrderNum DESC 2005-03-11 15:10:30.08 spid4 Coordinator: EC = 0x7ee659a8, SPID: 121, ECID: 0, Not Blocking 2005-03-11 15:10:30.08 spid4 Consumer List:: 2005-03-11 15:10:30.08 spid4 Consumer: Xid Slot: 0, EC = 0x7ee659a8, SPID: 121, ECID: 0, Blocking 2005-03-11 15:10:30.08 spid4 Producer List:: 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 1, EC = 0x465e0098, SPID: 121, ECID: 3, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 2, EC = 0x5cc76098, SPID: 121, ECID: 2, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 3, EC = 0x1f8fc098, SPID: 121, ECID: 1, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 4, EC = 0x65d38098, SPID: 121, ECID: 4, Blocking 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Node:2 2005-03-11 15:10:30.08 spid4 PAG: 7:1:2921 CleanCnt:4 Mode: IX Flags: 0x2 2005-03-11 15:10:30.08 spid4 Wait List: 2005-03-11 15:10:30.08 spid4 Owner:0x5c3f9600 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:121 ECID:1 2005-03-11 15:10:30.08 spid4 Requested By: 2005-03-11 15:10:30.08 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:121 ECID:3 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x465E009<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x5c311aa0 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/ED<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Node:3 2005-03-11 15:10:30.08 spid4 PAG: 7:1:2921 CleanCnt:4 Mode: IX Flags: 0x2 2005-03-11 15:10:30.08 spid4 Grant List 2:: 2005-03-11 15:10:30.08 spid4 Owner:0x68cd35a0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:105 ECID:0 2005-03-11 15:10:30.08 spid4 SPID: 105 ECID: 0 Statement Type: DELETE Line #: 56 2005-03-11 15:10:30.08 spid4 Input Buf: Language Event: UPDATE tblOEOrdersDetail SET ProductID = 26655, ItemNumber = 7, ItemType = 0, Qty = 1.0, ListPrice = 0, Cost = 0, SellingPrice = 0, Price = 0, StateAmount = 0.0, Amount = 0, ItemDescription = ‘ INSTAL KIT 6.5” SD 2005-03-11 15:10:30.08 spid4 Requested By: 2005-03-11 15:10:30.08 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:121 ECID:1 Ec<img src=’/community/emoticons/emotio n-6.gif’ alt=’

‘ />0x1F8FC09<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x5c3f9600 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/ED<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Node:4 2005-03-11 15:10:30.08 spid4 RID: 7:1:190506:44 CleanCnt:1 Mode: X Flags: 0x2 2005-03-11 15:10:30.08 spid4 Grant List 3:: 2005-03-11 15:10:30.08 spid4 Owner:0x6898dce0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:121 ECID:0 2005-03-11 15:10:30.08 spid4 Requested By: 2005-03-11 15:10:30.08 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:105 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x64FA99A<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x5c311f60 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/5C0) 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 — next branch – 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Node:1 2005-03-11 15:10:30.08 spid4 Port: 0x19dc0100 Xid Slot: 0, EC: 0x7ee659a8, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers 2005-03-11 15:10:30.08 spid4 Coordinator: EC = 0x7ee659a8, SPID: 121, ECID: 0, Not Blocking 2005-03-11 15:10:30.08 spid4 Consumer List:: 2005-03-11 15:10:30.08 spid4 Consumer: Xid Slot: 0, EC = 0x7ee659a8, SPID: 121, ECID: 0, Blocking 2005-03-11 15:10:30.08 spid4 Producer List:: 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 1, EC = 0x465e0098, SPID: 121, ECID: 3, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 2, EC = 0x5cc76098, SPID: 121, ECID: 2, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 3, EC = 0x1f8fc098, SPID: 121, ECID: 1, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 4, EC = 0x65d38098, SPID: 121, ECID: 4, Blocking 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Node:7 2005-03-11 15:10:30.08 spid4 PAG: 7:1:2921 CleanCnt:4 Mode: IX Flags: 0x2 2005-03-11 15:10:30.08 spid4 Wait List: 2005-03-11 15:10:30.08 spid4 Owner:0x5c3f9600 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:121 ECID:1 2005-03-11 15:10:30.08 spid4 Requested By: 2005-03-11 15:10:30.08 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:121 ECID:2 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x5CC7609<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x5c9fd560 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/ED<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 — next branch – 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Node:1 2005-03-11 15:10:30.08 spid4 Port: 0x19dc0100 Xid Slot: 0, EC: 0x7ee659a8, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers 2005-03-11 15:10:30.08 spid4 Coordinator: EC = 0x7ee659a8, SPID: 121, ECID: 0, Not Blocking 2005-03-11 15:10:30.08 spid4 Consumer List:: 2005-03-11 15:10:30.08 spid4 Consumer: Xid Slot: 0, EC = 0x7ee659a8, SPID: 121, ECID: 0, Blocking 2005-03-11 15:10:30.08 spid4 Producer List:: 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 1, EC = 0x465e0098, SPID: 121, ECID: 3, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 2, EC = 0x5cc76098, SPID: 121, ECID: 2, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 3, EC = 0x1f8fc098, SPID: 121, ECID: 1, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 4, EC = 0x65d38098, SPID: 121, ECID: 4, Blocking 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 — next branch – 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Node:1 2005-03-11 15:10:30.08 spid4 Port: 0x19dc0100 Xid Slot: 0, EC: 0x7ee659a8, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers 2005-03-11 15:10:30.08 spid4 Coordinator: EC = 0x7ee659a8, SPID: 121, ECID: 0, Not Blocking 2005-03-11 15:10:30.08 spid4 Consumer List:: 2005-03-11 15:10:30.08 spid4 Consumer: Xid Slot: 0, EC = 0x7ee659a8, SPID: 121, ECID: 0, Blocking 2005-03-11 15:10:30.08 spid4 Producer List:: 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 1, EC = 0x465e0098, SPID: 121, ECID: 3, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 2, EC = 0x5cc76098, SPID: 121, ECID: 2, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 3, EC = 0x1f8fc098, SPID: 121, ECID: 1, Blocking 2005-03-11 15:10:30.08 spid4 Producer: Xid Slot: 4, EC = 0x65d38098, SPID: 121, ECID: 4, Blocking 2005-03-11 15:10:30.08 spid4 2005-03-11 15:10:30.08 spid4 Node:12 2005-03-11 15:10:30.08 spid4 PAG: 7:1:2921 CleanCnt:4 Mode: IX Flags: 0x2 2005-03-11 15:10:30.08 spid4 Wait List: 2005-03-11 15:10:30.08 spid4 Owner:0x5c3f9600 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:121 ECID:1 2005-03-11 15:10:30.08 spid4 Requested By: 2005-03-11 15:10:30.08 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:121 ECID:4 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x65D3809<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x6313ff60 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x64FA99A<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x5c311f60 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/5C0) 2005-03-11 15:10:47.58 spid4 Deadlock encountered …. Printing deadlock information 2005-03-11 15:10:47.58 spid4 2005-03-11 15:10:47.58 spid4 Wait-for graph 2005-03-11 15:10:47.58 spid4 2005-03-11 15:10:47.58 spid4 Node:1 2005-03-11 15:10:47.58 spid4 RID: 7:1:190506:48 CleanCnt:1 Mode: X Flags: 0x2 2005-03-11 15:10:47.58 spid4 Grant List 3:: 2005-03-11 15:10:47.58 spid4 Owner:0x6898dce0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:121 ECID:0 2005-03-11 15:10:47.58 spid4 SPID: 121 ECID: 0 Statement Type: SELECT Line #: 1 2005-03-11 15:10:47.58 spid4 Input Buf: Language Event: SELECT OrderID, OrderNum, CustomerID, PO#, OrderTotal, OrderType, OrderStatus, LastInvoiceNum FROM tblOEOrders WHERE (CustomerID = 43173) ORDER BY OrderNum DESC 2005-03-11 15:10:47.58 spid4 Requested By: 2005-03-11 15:10:47.58 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:105 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x64FA99A<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x68cd3300 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/5C0) 2005-03-11 15:10:47.58 spid4 2005-03-11 15:10:47.58 spid4 Node:2 2005-03-11 15:10:47.58 spid4 Port: 0x19dc0100 Xid Slot: 0, EC: 0x7ee659a8, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeOpenWorkers 2005-03-11 15:10:47.58 spid4 Coordinator: EC = 0x7ee659a8, SPID: 121, ECID: 0, Not Blocking 2005-03-11 15:10:47.58 spid4 Consumer List:: 2005-03-11 15:10:47.58 spid4 Consumer: Xid Slot: 0, EC = 0x7ee659a8, SPID: 121, ECID: 0, Blocking 2005-03-11 15:10:47.58 spid4 Producer List:: 2005-03-11 15:10:47.58 spid4 Producer: Xid Slot: 1, EC = 0x5ce5c098, SPID: 121, ECID: 1, Blocking 2005-03-11 15:10:47.58 spid4 Producer: Xid Slot: 2, EC = 0x64f04098, SPID: 121, ECID: 4, Blocking 2005-03-11 15:10:47.58 spid4 Producer: Xid Slot: 3, EC = 0x46958098, SPID: 121, ECID: 3, Blocking 2005-03-11 15:10:47.58 spid4 Producer: Xid Slot: 4, EC = 0x47e90098, SPID: 121, ECID: 2, Blocking 2005-03-11 15:10:47.58 spid4 2005-03-11 15:10:47.58 spid4 Node:3 2005-03-11 15:10:47.58 spid4 PAG: 7:1:2921 CleanCnt:4 Mode: IX Flags: 0x2 2005-03-11 15:10:47.58 spid4 Grant List 2:: 2005-03-11 15:10:47.58 spid4 Owner:0x5c311520 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:105 ECID:0 2005-03-11 15:10:47.58 spid4 SPID: 105 ECID: 0 Statement Type: DELETE Line #: 56 2005-03-11 15:10:47.58 spid4 Input Buf: Language Event: UPDATE tblOEOrdersDetail SET ProductID = 26655, ItemNumber = 7, ItemType = 0, Qty = 1.0, ListPrice = 0, Cost = 0, SellingPrice = 0, Price = 0, StateAmount = 0.0, Amount = 0, ItemDescription = ‘INSTAL KIT 6.5” SD 2005-03-11 15:10:47.58 spid4 Requested By: 2005-03-11 15:10:47.58 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:121 ECID:1 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x5CE5C09<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x5bc757c0 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/79<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 2005-03-11 15:10:47.58 spid4

Let#%92s analyze the log file. In the above case 2 nodes were involved in deadlock. Node :1 belongs to SPID :115 and Node :2 belongs to SPID :4. To know the source query that was under execution during deadlock, can be found from the input buffer. This line can be found from the line starting with â€œinput Buf:â€ For Node:1 its â€œSproc_TPS_reportsâ€ and for Node :2 its â€œSproc_TPS_CaptureMissingEventâ€. SELECT OrderID, OrderNum, CustomerID, PO#,
OrderTotal, OrderType, OrderStatus, LastInvoiceNum FROM tblOEOrders
WHERE (CustomerID = 43173) ORDER BY OrderNum DESC
.. and also consider the statement involved and reported in this deadlock information, and using the same statements on the query analzyer to optimize. Refer this KBAhttp://support.microsoft.com/?kbid=832524 to resolve a deadlock situation and also the above referred links. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>