如果我正确地解密了以下死锁图,它看起来像两个进程(SPID：216和209)在同一页上拥有独占(X)锁：
XDL<资源列表>节目
<pagelock
fileid="1"
pageid="17410848"
dbid="21"
subresource="FULL"
objectname="33bd93e0-f5b2-43f6-93ca-56bbe6493e0c.dbo.sync_publishers2"
id="lock630b1d5380"
mode="X"
associatedObjectId="72057608416264192">
<owner-list>
<owner
id="process90763f08c8"
mode="X"
requestType="wait" />
</owner-list>
<waiter-list>
<waiter
id="process861129bc28"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
还有一点点
<pagelock
fileid="1"
pageid="17410848"
dbid="21"
subresource="FULL"
objectname="33bd93e0-f5b2-43f6-93ca-56bbe6493e0c.dbo.sync_publishers2"
id="lock630b1d5380"
mode="X"
associatedObjectId="72057608416264192">
<owner-list>
<owner
id="process90763f04e8"
mode="X" />
</owner-list>
<waiter-list>
<waiter
id="process90763f08c8"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
怎么可能,这是什么意思？
完整的死锁定义如下：http://pastebin.com/A4Te3Chx.
UPD：我已经在Microsoft Connect上提交了一个项目来尝试收集权威回应：https://connect.microsoft.com/SQLServer/Feedback/Details/3119334.
解决方法
这只是意味着有一个等待该锁的队列.
您可以使用以下命令重新生成(运行setup,然后是tran 1.然后,您可以在15秒内按顺序在不同的连接中启动tran 2和tran 3).
建立
USE tempdb
CREATE TABLE T
(
X INT PRIMARY KEY WITH(ALLOW_ROW_LOCKS = OFF),Filler AS CAST('A' AS CHAR(8000)) PERSISTED
);
INSERT INTO T VALUES (1),(2),(3);
特兰1
SET XACT_ABORT ON
USE tempdb -- t1
BEGIN TRAN
UPDATE T SET X = X WHERE X = 1
WAITFOR DELAY '00:00:15'
--See what locks are granted just before the deadlock
SELECT resource_description,request_status,request_session_id,X
FROM sys.dm_tran_locks tl
LEFT JOIN T WITH(NOLOCK)
ON sys.fn_PhysLocFormatter(T.%% physloc%%) = '(' + RTRIM(resource_description) + ':0)'
WHERE resource_associated_entity_id = (SELECT partition_id
FROM sys.partitions
WHERE object_id = object_id('T'));
RAISERROR ('',1) WITH NOWAIT;
UPDATE T SET X = X WHERE X = 3
WAITFOR DELAY '00:00:20'
ROLLBACK
特兰2
SET XACT_ABORT ON USE tempdb -- t2 BEGIN TRAN UPDATE T SET X = X WHERE X = 2 UPDATE T SET X = X WHERE X = 1 WAITFOR DELAY '00:00:20' ROLLBACK
特兰3
SET XACT_ABORT ON USE tempdb -- t3 BEGIN TRAN UPDATE T SET X = X WHERE X = 3 UPDATE T SET X = X WHERE X = 1 ROLLBACK
在请求锁定之前立即对tran_locks进行查询的结果将会导致死锁
+----------------------+----------------+--------------------+---+ | resource_description | request_status | request_session_id | X | +----------------------+----------------+--------------------+---+ | 4:416 | GRANT | 61 | 1 | | 4:416 | WAIT | 64 | 1 | | 4:416 | WAIT | 65 | 1 | | 4:418 | GRANT | 64 | 2 | | 4:419 | GRANT | 65 | 3 | +----------------------+----------------+--------------------+---+
我收到的死锁图如下.
虽然它说死锁的受害者正在等待tran 2拥有的锁,但实际上并不是这样.在僵局的时候,锁由tran 1拥有,而tran 2在第3轮之前首先排在第一位.
死锁图XML显示了这一点,因为它具有相同资源的两个节点(第416页),而“所有者”中有一个requestType =“等待”
<resource-list>
<pagelock
fileid="4"
pageid="416"
dbid="2"
subresource="FULL"
objectname="tempdb.dbo.T"
id="lock2486d8c4380"
mode="X"
associatedObjectId="936748728230805504">
<owner-list>
<owner
id="process2486ba0cca8"
mode="X"
requestType="wait" />
</owner-list>
<waiter-list>
<waiter
id="process2485370c8c8"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
<pagelock
fileid="4"
pageid="416"
dbid="2"
subresource="FULL"
objectname="tempdb.dbo.T"
id="lock2486d8c4380"
mode="X"
associatedObjectId="936748728230805504">
<owner-list>
<owner
id="process2485370c4e8"
mode="X" />
</owner-list>
<waiter-list>
<waiter
id="process2486ba0cca8"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
<pagelock
fileid="4"
pageid="419"
dbid="2"
subresource="FULL"
objectname="tempdb.dbo.T"
id="lock248636ace80"
mode="X"
associatedObjectId="936748728230805504">
<owner-list>
<owner
id="process2485370c8c8"
mode="X" />
</owner-list>
<waiter-list>
<waiter
id="process2485370c4e8"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
