2

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?

EDIT: add example data

parent_table:
-------------
id
1
2

child_table:
------------
parent_id | col_1 | ...
1         | "A"   | ...
2         | "A"   | ...
...

2 threads:
1st thread is `DELETE FROM parent_table where id = 1`
2nd thread is `DELETE FROM parent_table where id = 2`

child_table has:

  • composite primary key of (parent_id, col_1)
  • fk parent_id references parent_table with ON DELETE CASCADE
Improve this question

1 Answer 1

Reset to default
0

Your index key A is composite key: (parent_id, col1) so for one parent id you have multiple rows, in my picture suppose you want to delete rows with parent_id = 1, you have at least 4 rows, one process started with (1,5) and the second from (1,2).

enter image description here

Improve this answer
8
  • wouldn't process-2 's RS-U be over (1,2), (1,3), (1,4), (1,5) ?
    – brownsugarpearl
    Commented Feb 28 at 19:45
  • But i guess it would be hard to implement a range lock over a composite key such as this without a second level of indirection, probably like 1 -> [(1, 2), (1,3), (1,4), (1,5)], 2 -> ...?
    – brownsugarpearl
    Commented Feb 28 at 19:52
  • @brownsugarpearl I think this situation is only possible with parallel plan as there is no reason to scan the index in different order in different sessions, so the range is not all inclusive for the entire index but only for a subset that belongs to some single thread.
    – sepupic
    Commented Feb 29 at 8:33
  • hi @sepupic thanks for your explanation. I've added more context in the question, hopefully that could add insight. I'm not understanding "so the range is not all inclusive for the entire index but only for a subset that belongs to some single thread"
    – brownsugarpearl
    Commented Mar 1 at 0:43
  • hi @brownsugarpearl, what I said is different from your "2 threads: 1st thread is DELETE FROM parent_table where id = 1 2nd thread is DELETE FROM parent_table where id = 2", I meand there is 2 sessions, not 2 threads, and every session that executes in parallel for example executes on 4 threads, ok? The overall work is divided between 4 threads and every thread aquires locks only ot its portion of data, that's why you don't have 1 range but 4 ranges per session
    – sepupic
    Commented Mar 1 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.