SQL deadlock on concurrent delete

I have an API endpoint to delete children of a parent, unless a child is specified. The SQL is:

DELETE FROM
   Child
WHERE
   ParentID = @ParentID AND
   ChildID NOT IN (@ChildIDToKeep);

(This query is sanitised, so ignore the fact that I’m not doing ChildID != @ChildIDToKeep)

The executed SQL is also wrapped in a transaction.

My web application attempts to send hundreds of requests to this endpoint concurrently, so this SQL transaction gets executed concurrently many times. For some reason this is causing a deadlock.

Why am I getting a deadlock, and what can I do to resolve it?

Here is the deadlock XML:

<deadlock>
 <victim-list>
  <victimProcess id="process25c3045b848" />
 </victim-list>
 <process-list>
  <process id="process25c3045b848" taskpriority="0" logused="6336" waitresource="OBJECT: 14:788354023:0 " waittime="3039" ownerId="39730548" transactionname="user_transaction" lasttranstarted="2021-08-13T13:28:05.690" XDES="0x25c36f9f900" lockMode="IX" schedulerid="1" kpid="6588" status="suspended" spid="70" sbid="4" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-08-13T13:28:05.957" lastbatchcompleted="2021-08-13T13:28:05.957" lastattention="1900-01-01T00:00:00.957" clientapp="Core .Net SqlClient Data Provider" hostname="DER-UK-LAP-101" hostpid="14344" loginname="USERNAME" isolationlevel="serializable (4)" xactid="39730548" currentdb="14" currentdbname="Database" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="2" stmtstart="102" stmtend="500" sqlhandle="0x0200000053d2042c7d4e4199da6b8531c53adb3464bc8b610000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@ParentID int,@ChildIDToKeep int)
                    DELETE FROM
                        Child
                    WHERE
                        ParentID = @ParentID AND
                        ChildID NOT IN (@ChildIDToKeep);   </inputbuf>
  </process>
  <process id="process25c2a462ca8" taskpriority="0" logused="6348" waitresource="OBJECT: 14:788354023:0 " waittime="2901" ownerId="39730549" transactionname="user_transaction" lasttranstarted="2021-08-13T13:28:05.690" XDES="0x25c39560e80" lockMode="IX" schedulerid="2" kpid="2252" status="suspended" spid="63" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-08-13T13:28:06.097" lastbatchcompleted="2021-08-13T13:28:06.097" lastattention="1900-01-01T00:00:00.097" clientapp="Core .Net SqlClient Data Provider" hostname="DER-UK-LAP-101" hostpid="14344" loginname="USERNAME" isolationlevel="serializable (4)" xactid="39730549" currentdb="14" currentdbname="Database" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="2" stmtstart="102" stmtend="500" sqlhandle="0x0200000053d2042c7d4e4199da6b8531c53adb3464bc8b610000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@ParentID int,@ChildIDToKeep int)
                    DELETE FROM
                        Child
                    WHERE
                        ParentID = @ParentID AND
                        ChildID NOT IN (@ChildIDToKeep);   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="788354023" subresource="FULL" dbid="14" objectname="Database.dbo.Child" id="lock255443f2580" mode="S" associatedObjectId="788354023">
   <owner-list>
    <owner id="process25c2a462ca8" mode="S" />
    <owner id="process25c2a462ca8" mode="IX" requestType="convert" />
   </owner-list>
   <waiter-list>
    <waiter id="process25c3045b848" mode="IX" requestType="convert" />
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="788354023" subresource="FULL" dbid="14" objectname="Database.dbo.Child" id="lock255443f2580" mode="S" associatedObjectId="788354023">
   <owner-list>
    <owner id="process25c3045b848" mode="S" />
    <owner id="process25c3045b848" mode="IX" requestType="convert" />
   </owner-list>
   <waiter-list>
    <waiter id="process25c2a462ca8" mode="IX" requestType="convert" />
   </waiter-list>
  </objectlock>
 </resource-list>
</deadlock>

Here is the deadlock graph:

Answer

You’ve asked for the deadlock by running the transaction in SERIALIZABLE isolation level:

isolationlevel="serializable (4)"

SERIALIZABLE transactions hold shared (S) locks until the end of the transaction, so when you “Select Parent’s Children.” you acquire and hold a S lock. And it’s a table lock since you don’t have proper indexing on the table.

SERIALIZABLE enforces its guarantees with deadlocks, see rant here: using new TransactionScope() considered harmful.

So when two sessions take and hold an S lock on the table, neither can acquire the IX lock required to start the DELETE, nor would they be able to acquire the X lock required to complete the DELETE. SERIALIZABLE is just doing its job here, as each session has read data that the other is trying to update, so if allowed to complete, the state of the database would not be the same as if the transactions ran sequentially, which is the definition of SERIALIZABLE. So it’s expected one of the transactions must be terminated, and that you would catch and retry if you still needed that work done.

So I would drop to READ_COMMITTED, and put an index on ParentId. You might want to put an UPDLOCK hint on your query to “Select Parent” so two sessions can’t run against the same parent concurrently.

Advertisement

Answer