SQL Server死锁对象ID太大 [英] Sql Server Deadlock Object IDs are too large
问题描述
我正在尝试跟踪SQL 2005数据库（64位）中发生的死锁。我们目前尚未启用快照隔离。
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.
我打开tf-1204并收到以下输出。
I turned on tf-1204 and received the output below.
从此输出中，我可以确定节点1是一个存储过程，该存储过程选择数据并且仅修改#temp表中的值。
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.
节点2是另一个存储过程，它对单行数据执行基于简单主键的更新。
Node 2 is another stored procedure does a simple primary key based update on a single row of data.
我无法确定是此处争用的实际资源。 10：72057594060734464和10：72057594038910976的键允许我确定数据库，但是这些对象ID无法用object_name解析。实际上，它们应该是int值，所以我不确定这些大数字来自何处。
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?
这里是死锁tf-1204输出：
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 ----------------------------------
推荐答案
这些ID实际上是指sys.partitions中的hobt（堆或二叉树）。在数据库10中尝试以下查询，您将找到受影响的对象和索引。
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
这篇关于SQL Server死锁对象ID太大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
