SQL Server. Как одна страница может быть исключительно (X) заблокирована двумя процессами?

Если я правильно дешифровал следующий тупиковый график, похоже, что два процесса (SPIDs: 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.

Ответ 1

Это означает, что на этой блокировке была очередь.

Вы можете воспроизвести его следующим образом (запустите настройку, а затем перейдите к 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.

График deadlock graph показывает это, поскольку он имеет два узла для одного и того же ресурса (страница 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>

Ответ 2

Эта проблема связана с табличной переменной @tmp, которую вы используете.

Когда вы используете переменную таблицы в своем запросе, план оптимизирован для одной строки, так что точка вашего механизма запроса блокирует одну строку (тактично она требовала блокировки страницы), но блокировка была увеличена и приобрела блокировку страницы, поэтому этот тупик произошло.