sql-server - SQL Server-同一页如何被两个进程排他(X)锁定？

如果我正确地解密了以下死锁图，则看起来两个进程（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>