您可以使用以下命令重新生成(运行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>