我试图追踪 SQL 2005 数据库(64 位)中发生的死锁。我们目前没有启用快照隔离。
我打开 tf-1204 并收到以下输出。
从这个输出中,我可以确定节点 1 是一个存储过程,它选择数据并且仅修改 #temp 表中的值。
节点 2 是另一个存储过程,对单行数据执行基于主键的简单更新。
我无法确定这里争夺的实际资源。 10:72057594060734464 和 10:72057594038910976 的键允许我确定数据库,但这些对象 ID 无法用 object_name 解析。事实上,它们应该是int值,所以我不确定这些大数字是从哪里来的。
在研究这个问题时,我也能够从活动监视器中获取对象 ID 的类似值。
如何解析这些对象标识符?
这是死锁 tf-1204 的输出:
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 中的 hobts(堆或二叉树)。在数据库 10 中尝试以下查询,您将发现哪个对象和哪个索引受到影响。
SELECT hobt_id, object_name(p.[object_id]), index_id
FROM sys.partitions p
WHERE hobt_id = 72057594060734464
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)