Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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>

deadlock graph

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.

like image 687
Eugene D. Gubenkov Avatar asked Jan 04 '17 07:01

Eugene D. Gubenkov


People also ask

What is exclusive lock in SQL Server?

Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released.

Is exclusive lock mode available in SQL?

SQL Server automatically acquires exclusive locks on data when the data is modified by an INSERT, UPDATE, or DELETE operation.


Video Answer


1 Answers

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.

enter image description here

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>
like image 195
Martin Smith Avatar answered Oct 24 '22 03:10

Martin Smith