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

Question

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

Answer 1

这仅意味着有一个队列正在等待该锁。

您可以使用以下命令重现它(运行安装程序，然后运行 ​​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);

Tran 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 

Tran 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 

Tran 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>