Sql-server – How is it possible that two deletes give a deadlock, if they do have different primary key data

deadlockdeletesql server

I am getting a deadlock by the same delete stored procedures that is called by two different threads for deleting two concurrent records.

The deadlock happened on the primary key index of the X table.
Do you have any idea for solve this problem?

Here is the delete query:

DELETE FROM "MetadataContexts_data" WHERE iid=@iidvalue AND locked=@lockedvalue;

where iid and locked values for those two different delete are is 1845, 2015-02-23T10:33:23:384.719 and 1846, 2015-02-23T10:33:23:509.9806 respectively.
There is no index on the locked column while iid is the primary key column.

Here is the deadlock graph: (table names obfuscated)

<deadlock-list>
<deadlock victim="process16a525c38">
    <process-list>
        <process id="process16a525c38" taskpriority="0" logused="1296" waitresource="KEY: 11:72057594325368832 (d197ab30ed5e)" waittime="3108" ownerId="46495" transactionname="user_transaction" lasttranstarted="2015-02-23T11:33:23.617" 
        XDES="0x17305f378" lockMode="U" schedulerid="4" kpid="6748" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" 
        lastbatchstarted="2015-02-23T11:33:23.617" lastbatchcompleted="2015-02-23T11:33:23.617" lastattention="1900-01-01T00:00:00.617" 
        clientapp=".Net SqlClient Data Provider" hostname="VL081" hostpid="7332" loginname="NT AUTHORITY\NETWORK SERVICE" isolationlevel="read committed (2)" 
        xactid="46495" currentdb="11" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
            <executionStack>
                <frame procname="DevDb.dbo.MetadataContexts_sidedata_delete" line="3" stmtstart="346" stmtend="644" sqlhandle="0x03000b001d519e72419502013aa4000000000000000000000000000000000000000000000000000000000000">
                    Delete  FROM &quot;MetadataContexts_data&quot; WHERE &quot;MetadataContexts_data&quot;.iid in (SELECT iid from Deleted)     </frame>
                <frame procname="DevDb.dbo.MetadataContexts_delete" line="9" stmtstart="608" stmtend="824" sqlhandle="0x03000b0098ac9718739bde003ba4000001000000000000000000000000000000000000000000000000000000">
                    DELETE FROM &quot;MetadataContexts_data&quot; WHERE iid=@iidvalue AND locked=@lockedvalue;     </frame>
            </executionStack>
            <inputbuf>
    Proc [Database Id = 11 Object Id = 412593304]    </inputbuf>
        </process>
        <process id="process16a51d498" taskpriority="0" logused="1296" waitresource="KEY: 11:72057594325368832 (5891aeb5d1b8)" waittime="3108" ownerId="46654" transactionname="user_transaction" 
        lasttranstarted="2015-02-23T11:33:23.653" XDES="0x16ac443a8" lockMode="U" schedulerid="2" kpid="7556" status="suspended" spid="51" sbid="0" ecid="0" priority="0" trancount="2" 
        lastbatchstarted="2015-02-23T11:33:23.653" lastbatchcompleted="2015-02-23T11:33:23.653" 
        lastattention="1900-01-01T00:00:00.653" clientapp=".Net SqlClient Data Provider" hostname="VL081" 
        hostpid="7332" loginname="NT AUTHORITY\NETWORK SERVICE" isolationlevel="read committed (2)" xactid="46654" currentdb="11" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
            <executionStack>
                <frame procname="DevDb.dbo.MetadataContexts_sidedata_delete" line="3" stmtstart="346" stmtend="644" sqlhandle="0x03000b001d519e72419502013aa4000000000000000000000000000000000000000000000000000000000000">
                    Delete  FROM &quot;MetadataContexts_data&quot; WHERE &quot;MetadataContexts_data&quot;.iid in (SELECT iid from Deleted)     </frame>
                <frame procname="DevDb.dbo.MetadataContexts_delete" line="9" stmtstart="608" stmtend="824" sqlhandle="0x03000b0098ac9718739bde003ba4000001000000000000000000000000000000000000000000000000000000">
                    DELETE FROM &quot;MetadataContexts_data&quot; WHERE iid=@iidvalue AND locked=@lockedvalue;     </frame>
            </executionStack>
            <inputbuf>
                    Proc [Database Id = 11 Object Id = 412593304]    </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594325368832" dbid="11" objectname="DevDb.dbo.MetadataContexts_data" indexname="2" id="lock16fabe080" mode="X" associatedObjectId="72057594325368832">
            <owner-list>
                <owner id="process16a51d498" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="process16a525c38" mode="U" requestType="wait"/>
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594325368832" dbid="11" objectname="DevDb.dbo.MetadataContexts_data" indexname="2" id="lock16fabd400" mode="X" associatedObjectId="72057594325368832">
            <owner-list>
                <owner id="process16a525c38" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="process16a51d498" mode="U" requestType="wait"/>
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

Best Answer

You might be getting lock escalation. This is when SQL Server replaces many fine-grained (row) locks with a single coarse-grained (table) lock to save system resources. This is most likely if you are processing many rows within a transaction. That link suggests some work-arounds.

Related Solutions

Sql-server – Given two known statements, how can I replicate deadlock

Nothing can prevent deadlocks in a RDBMS. However, you can greatly reduce the chance of the occurring by following a few simple rules:

Make sure your queries are tuned well, using appropriate indexes.
If you have to take concurrent locks on multiple tables, all your statements and procedures should access those tables in the same order.
Keep your transactions as short as possible.

That will make it a lot less likely for you to encounter deadlocks. However, you cannot completely avoid them, so your code should be resilient and just automatically wait a random time interval (a few milliseconds) and then try again. Only if after three or so retries you still cant get the statement executed log the error.

Now to your question, of how to reproduce the deadlock: There are several types of deadlocks. The simplest one is the deadlock on two resources:

connection A takes lock on resource 1
connection B takes lock on resource 2
connection A tries to take conflicting lock on resource 2 and has to wait
connection B tries to take conflicting lock on resource 1 and has to wait

Now both connections wait for each other causing a deadlock. This type of deadlock can be fairly easily reproduced by stepping through the code one statement at a time on two connections. Other deadlocks are not always as easy to reproduce because e.g. they might only occur on memory pressure situations or similar.

The easiest way to get more information about deadlocks is, to set up a deadlock-graph trace. The deadlock graph contains a lot of information that can be helpful understanding the captured deadlock. As your system (hopefully) has deadlock only relatively infrequent, you might have to run the trace for a while. But if you are capturing only that one event, it won't use a lot of resources. Once you captured the deadlock graph look at it as XML. The graphical representation given by the profiler does reveal only a limited amount of information, but it is a good starting point.

SQL Server – Raiserror in For Delete Trigger to Rollback Deletes

In order for that to work as you've coded it above, you need a rollback after your RAISERROR or, as the comments correctly state, wrap in a TRY/CATCH.

By itself, it's not going to be treated the same as an actual statement or batch terminating error. For example, this will work the way you expect:

create table T1 ( i1 int  );
create table t2 (i2 int primary key);
go

create trigger T1_ForDelete on T1
for delete
as
    insert into t2 (i2)
    SELECT i1 
    FROM DELETED
go

insert into T1 (i1) values (1);
insert into t2 (i2) values (1);

delete from T1;
go

SELECT * from t1

In the above code the trigger will error, and cause the delete (outer) transaction to rollback. By itself, RAISERROR is only really transferring control and/or sending messages up the stack (so to speak)

BOL describes this behavior:

When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

Outside the scope of any TRY block.

With a severity of 10 or lower in a TRY block.

With a severity of 20 or higher that terminates the database connection.

So RAISERROR outside the scope of a TRY block simply returns the error to the caller and is not treated as a statement-terminating error regardless of the severity you define.

Best Answer

Related Solutions

Sql-server – Given two known statements, how can I replicate deadlock

SQL Server – Raiserror in For Delete Trigger to Rollback Deletes

Related Question