如果我正确地解密了以下死锁图,则看起来两个进程(SPID:216和209)在同一页面上拥有排他(X)锁:
XDL <resource-list>
显示
<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。
最佳答案
这只是意味着有一个队列在等待该锁。
您可以使用以下内容重现它(运行安装程序,然后运行tran1。然后您有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 ('',0,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在tran 3之前首先处于锁定状态。
死锁图XML对此进行了显示,因为它具有用于同一资源的两个节点(第416页),并且其中一个“所有者”具有
requestType="wait"
<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>