SQL Server - How the same page can be exclusively (X) locked by two processes?

Question

If I deciphered the following deadlock graph correctly, it looks like two processes (SPIDs: 216 and 209) own the exclusive (X) lock on the very same page:

The XDL <resource-list> shows

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

And a bit further down

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

How it is even possible and what does it mean?

The full deadlock definition is available here: http://pastebin.com/A4Te3Chx.

UPD: I've filed an item on Microsoft Connect to try to gather authoritative response: https://connect.microsoft.com/SQLServer/Feedback/Details/3119334.

Answer 1

This just means that there was a queue waiting on that lock.

You can reproduce it with the following (run the setup and then tran 1. You then have 15 seconds to start tran 2 and tran 3 in sequence in different connections).

Setup

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 

The result of the query against tran_locks immediately before requesting the lock that will cause deadlock shows

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

The deadlock graph I received is as follows.

Though it says the deadlock victim was waiting on a lock owned by tran 2 this is not actually the case. At the time of the deadlock the lock was owned by tran 1 and tran 2 was first in line for it before tran 3.

The deadlock graph XML shows this as it has two nodes for the same resource (page 416) and in one the "owner" has a 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>