Menu
I am trying to trace a deadlock that is occurring in our SQL 2005 database (64-bit). We do not currently have snapshot isolation enabled.
I turned on tf-1204 and received the output below.
From this output I am able to determine that Node 1 is a stored procedure that selects data and only modifies values in #temp tables.
Node 2 is another stored procedure does a simple primary key based update on a single row of data.
What I can't determine is the actual resource that was in contention here. The Keys of 10:72057594060734464 and 10:72057594038910976 allow me to determine the database, but these object ids cannot be resolved with object_name. In fact, they should be int values, so I'm not sure where these large numbers are coming from.
In researching the problem I was able to get similar values out of Activity Monitor for Object ID as well.
How do I resolve these object identifiers?
Here is the deadlock tf-1204 output:
2008-12-05 07:48:28.19 spid4s ---------------------------------- 2008-12-05 07:48:28.19 spid4s Starting deadlock search 634 2008-12-05 07:48:28.19 spid4s Target Resource Owner: 2008-12-05 07:48:28.19 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 2008-12-05 07:48:28.19 spid4s 0:Insert new node: Node:1 ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 2008-12-05 07:48:28.19 spid4s 1:SearchOR Considering new blocker - task: 0000000000EC5198, Worker 00000000C89881C0 2008-12-05 07:48:28.19 spid4s 2:Insert new node: Node:2 ResType:LockOwner Stype:'OR'Xdes:0x00000000808F1A80 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x0000000129E82598) Value:0x1063d000 2008-12-05 07:48:28.19 spid4s 3:SearchOR Considering new blocker - task: 0000000000C3FC18, Worker 00000000F847C1C0 2008-12-05 07:48:28.19 spid4s 4:InsertKnown Cycle found between old res owner: [ ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980] and new res owner [ ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980] 2008-12-05 07:48:28.19 spid4s 4:InsertKnown search result: Deadlock found (blocking owner is on a stack) 2008-12-05 07:48:28.19 spid4s 3:SearchOR search result: Deadlock found (cycle on this level or before) 2008-12-05 07:48:28.19 spid4s 1:SearchOR search result: Deadlock found (cycle on this level or before) 2008-12-05 07:48:28.19 spid4s 2008-12-05 07:48:28.19 spid4s Deadlock cycle was encountered .... verifying cycle 2008-12-05 07:48:28.19 spid4s 0:Insert new node: Node:1 ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 Cost:(0/1544) 2008-12-05 07:48:28.19 spid4s 1:SearchOR Considering new blocker - task: 0000000000EC5198, Worker 00000000C89881C0 2008-12-05 07:48:28.19 spid4s 2:Insert new node: Node:2 ResType:LockOwner Stype:'OR'Xdes:0x00000000808F1A80 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x0000000129E82598) Value:0x1063d000 Cost:(0/0) 2008-12-05 07:48:28.19 spid4s 3:SearchOR Considering new blocker - task: 0000000000C3FC18, Worker 00000000F847C1C0 2008-12-05 07:48:28.19 spid4s 4:InsertKnown Cycle found between old res owner: [ ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 Cost:(0/1544)] and new res owner [ ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 Cost:(0/1544)] 2008-12-05 07:48:28.19 spid4s 4:InsertKnown search result: Deadlock found (blocking owner is on a stack) 2008-12-05 07:48:28.19 spid4s 3:SearchOR search result: Deadlock found (cycle on this level or before) 2008-12-05 07:48:28.19 spid4s 1:SearchOR search result: Deadlock found (cycle on this level or before) 2008-12-05 07:48:28.19 spid4s Deadlock encountered .... Printing deadlock information 2008-12-05 07:48:28.19 spid4s Wait-for graph 2008-12-05 07:48:28.19 spid4s 2008-12-05 07:48:28.19 spid4s Node:1 2008-12-05 07:48:28.19 spid4s KEY: 10:72057594060734464 (c80089667602) CleanCnt:3 Mode:S Flags: 0x0 2008-12-05 07:48:28.19 spid4s Grant List 1: 2008-12-05 07:48:28.19 spid4s Owner:0x000000011063CDC0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:79 ECID:0 XactLockInfo: 0x00000000808F1AB8 2008-12-05 07:48:28.19 spid4s SPID: 79 ECID: 0 Statement Type: INSERT Line #: 220 2008-12-05 07:48:28.19 spid4s Input Buf: RPC Event: Proc [Database Id = 10 Object Id = 1751794144] 2008-12-05 07:48:28.19 spid4s Requested By: 2008-12-05 07:48:28.19 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 Cost:(0/1544) 2008-12-05 07:48:28.19 spid4s 2008-12-05 07:48:28.19 spid4s Node:2 2008-12-05 07:48:28.19 spid4s KEY: 10:72057594038910976 (0c0092f62b82) CleanCnt:2 Mode:X Flags: 0x0 2008-12-05 07:48:28.19 spid4s Grant List 0: 2008-12-05 07:48:28.19 spid4s Owner:0x000000011043F300 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0 XactLockInfo: 0x00000000813B8738 2008-12-05 07:48:28.19 spid4s SPID: 77 ECID: 0 Statement Type: UPDATE Line #: 23 2008-12-05 07:48:28.19 spid4s Input Buf: RPC Event: Proc [Database Id = 10 Object Id = 1791462302] 2008-12-05 07:48:28.19 spid4s Requested By: 2008-12-05 07:48:28.19 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000808F1A80 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x0000000129E82598) Value:0x1063d000 Cost:(0/0) 2008-12-05 07:48:28.19 spid4s 2008-12-05 07:48:28.19 spid4s Victim Resource Owner: 2008-12-05 07:48:28.19 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000808F1A80 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x0000000129E82598) Value:0x1063d000 Cost:(0/0) 2008-12-05 07:48:28.19 spid4s End deadlock search 634 ... a deadlock was found. 2008-12-05 07:48:28.19 spid4s ----------------------------------
916
Useful ways to avoid and minimize SQL Server deadlocksTry to keep transactions short; this will avoid holding locks in a transaction for a long period of time. Access objects in a similar logical manner in multiple transactions. Create a covering index to reduce the possibility of a deadlock.
Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order - meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.
A SQL Server deadlock occurs when exclusive locks are held on resources required by multiple processes and those processes cannot continue to completion.
View a graphical representation of the deadlock eventOpen the deadlock event in Event Viewer. On the General tab, go to the SQL Server deadlock xml report section, and then copy the text in the deadlock tag (including the start and end tag) to a text editor such as Notepad or Visual Studio Code. Save the file as a .
These IDs actually refer to hobts (Heap Or Binary Tree), found in sys.partitions. Try the following query inside database 10, and you will find which object and which index is affected.
SELECT hobt_id, object_name(p.[object_id]), index_id
FROM sys.partitions p
WHERE hobt_id = 72057594060734464
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
