Geek Answers Handbook

Deadlock Sql Server object identifiers are too large

I am trying to track the deadlock that occurs in our SQL 2005 database (64-bit). Image lock is currently disabled.

I turned on tf-1204 and got the result below.

From this conclusion, I can determine that Node 1 is a stored procedure that selects data and only modifies the values ​​in #temp tables.

Node 2 — Another stored procedure performs a simple update based on a primary key in a single data row.

What I cannot determine is the actual resource that was in dispute here. Keys 10: 72057594060734464 and 10: 72057594038910976 allow me to define the database, but these object identifiers cannot be resolved using object_name. In fact, they should be int values, so I'm not sure where these big numbers come from.

When investigating the problem, I was able to get similar values ​​from the Activity Monitor for the object ID.

How to resolve these object identifiers?

Here is the output from the tf-1204 dead end:

 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: (0x0000000101E82598) Value: 0x1063d000 2008-12-05 07: 00 48: 28.19 spid4s 3: SearchOR Considering new blocker - task: 0000000000C3FC18, Worker 00000000F847C1C0 2008-12-05 07: 48: 28.19 spid4s 4: InsertKnown Cycle fo  und 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-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-1  2-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 E  nd deadlock search 634 ... a deadlock was found.  2008-12-05 07: 48: 28.19 spid4s ----------------------------------
+6
sql-server deadlock
source share
1 answer

These identifiers actually refer to the hobts ​​(Heap Or Binary Tree) found in sys.partitions. Try the following query inside database 10 and you will find out which object and which index are affected.

SELECT hobt_id, object_name(p.[object_id]), index_id FROM sys.partitions p WHERE hobt_id = 72057594060734464
+10
source share

More articles:


All Articles