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

Question

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>

Answer 1

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.