Skip to main content
8 of 9
edited tags
Aleksey Vitsko
  • 6k
  • 5
  • 37
  • 68

SQL Server 2016 Deadlock

I want to validate my understanding of the sqlserver deadlock report:

  • context:
parent_table(id); 
child_table(parent_id, col1, col2, ...);

constraint primary_key(id) on parent_table;
constraint primary_key(parent_id, col1) "pk_child_table" on child_table; 
constraint foreign_key(parent_id) references parent_tabl on delete cascade
  • sqlserver 2016
  • isolation level: READ COMMITTED
  • given deadlock report:
<deadlock>
    <victim-list>
        <victimProcess id="process1"/>
    </victim-list>
    <process-list>
        <process id="process1" taskpriority="0" logused="884" waitresource="KEY: 6:72057594911457280 (32da7da6dc73)"
                 waittime="4320" ownerId="73322515021" transactionname="implicit_transaction"
                 lasttranstarted="2024-02-20T07:31:23.417" XDES="0x1d38e169900" lockMode="RangeS-U" schedulerid="40"
                 kpid="6240" status="suspended" spid="336" sbid="0" ecid="0" priority="0" trancount="2"
                 lastbatchstarted="2024-02-20T07:31:23.447" lastbatchcompleted="2024-02-20T07:31:23.447"
                 lastattention="1900-01-01T00:00:00.447" clientapp="Microsoft JDBC Driver for SQL Server"
                 hostname="node1.host.com" hostpid="0" loginname="dbuser" isolationlevel="read committed (2)"
                 xactid="73322515021" currentdb="6" currentdbname="dbname" lockTimeout="4294967295"
                 clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="24" stmtend="116"
                       sqlhandle="0x02000000c98f681b852f2b5ac263e52bb2d8019412d0c7470000000000000000000000000000000000000000">
                    unknown
                </frame>
                <frame procname="unknown" line="1"
                       sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>(@P0 bigint)delete from parent_table where id=@P0</inputbuf>
        </process>
        <process id="process2" taskpriority="0" logused="884" waitresource="KEY: 6:72057594911457280 (b6ebcc0466d0)"
                 waittime="4290" ownerId="73322515432" transactionname="implicit_transaction"
                 lasttranstarted="2024-02-20T07:31:23.423" XDES="0x1db05a07900" lockMode="RangeS-U" schedulerid="7"
                 kpid="12444" status="suspended" spid="209" sbid="0" ecid="0" priority="0" trancount="2"
                 lastbatchstarted="2024-02-20T07:31:23.447" lastbatchcompleted="2024-02-20T07:31:23.447"
                 lastattention="1900-01-01T00:00:00.447" clientapp="Microsoft JDBC Driver for SQL Server"
                 hostname="node2.host.com" hostpid="0" loginname="dbuser" isolationlevel="read committed (2)"
                 xactid="73322515432" currentdb="6" currentdbname="dbname" lockTimeout="4294967295"
                 clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="24" stmtend="116"
                       sqlhandle="0x02000000c98f681b852f2b5ac263e52bb2d8019412d0c7470000000000000000000000000000000000000000">
                    unknown
                </frame>
                <frame procname="unknown" line="1"
                       sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>(@P0 bigint)delete from parent_table where id=@P0</inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594911457280" dbid="6" objectname="dbname.dbo.child_table" indexname="pk_child_table"
                 id="lock1b7c8d5cf00" mode="RangeS-U" associatedObjectId="72057594911457280">
            <owner-list>
                <owner id="process2" mode="RangeS-U"/>
            </owner-list>
            <waiter-list>
                <waiter id="process1" mode="RangeS-U" requestType="wait"/>
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594911457280" dbid="6" objectname="dbname.dbo.child_table" indexname="pk_child_table"
                 id="lock1ba79447380" mode="X" associatedObjectId="72057594911457280">
            <owner-list>
                <owner id="process1" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="process2" mode="RangeS-U" requestType="wait"/>
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>
  • I'm struggling to understand how one process can obtain a range lock for update (RangeS-U) that includes index key A, while another process owns an exclusive lock (X) on the same index key A? Shouldn't holding e.g. a X lock on index key A prevent a RangeS-U on key A being obtained, and vice-versa?
  • Is my understand correct that: waitresource hash ((32da7da6dc73) vs (b6ebcc0466d0)) is different for the 2 processes, so they're trying to lock on different rows?
  • Given 2 processes concurrently deleting the same parent_table record that has child_table records that are to be cascade deleted, and that there is a primary key on the child_table (meaning there is index on it)... Under what scenarios would this deadlock ever happen?