Deadlock with insert and delete

Question

Deadlock graph:

<deadlock>
  <victim-list>
    <victimProcess id="process21881f1bc28" />
  </victim-list>
  <process-list>
    <process id="process21881f1bc28" taskpriority="0" logused="0" waitresource="KEY: 7:72057594049003520 (44e4a8141ab4)" waittime="375" ownerId="313642186" transactionname="user_transaction" lasttranstarted="2018-12-09T10:00:39.007" XDES="0x217706d6408" lockMode="RangeS-U" schedulerid="2" kpid="6776" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-09T10:00:39.013" lastbatchcompleted="2018-12-09T10:00:39.007" lastattention="1900-01-01T00:00:00.007" clientapp=".Net SqlClient Data Provider" hostname="x" hostpid="4724" loginname="x" isolationlevel="read uncommitted (1)" xactid="313642186" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="44308" stmtend="72024" sqlhandle="0x02000000fb261007e17c6dabc0917779a6823e14abd04fef0000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@param0 int,@param1 int,@param2 int,@param3 real,@param4 int,@param5 int,@param6 int,@param7 nvarchar(21),@param8 int,@param9 nvarchar(7),@param10 datetime,@param11 datetime,@param12 nvarchar(14),@param13 int,@param14 int,@param15 int,@param16 real,@param17 int,@param18 int,@param19 int,@param20 nvarchar(21),@param21 int,@param22 nvarchar(7),@param23 datetime,@param24 datetime,@param25 nvarchar(14),@param26 int,@param27 int,@param28 int,@param29 real,@param30 int,@param31 int,@param32 int,@param33 nvarchar(21),@param34 int,@param35 nvarchar(12),@param36 datetime,@param37 datetime,@param38 nvarchar(14),@param39 int,@param40 int,@param41 int,@param42 real,@param43 int,@param44 int,@param45 int,@param46 nvarchar(21),@param47 int,@param48 nvarchar(12),@param49 datetime,@param50 datetime,@param51 nvarchar(14),@param52 int,@param53 int,@param54 int,@param55 real,@param56 int,@param57 int,@param58 int,@param59 nvarchar(21),@param60 int,@param61 nvarchar(7),@param62 datetime,@param63 datetime,@param64 nvarchar(14),  /*INSERT INTO TradeBuffer_US_PC .... VALUES ...*/
 </inputbuf>
    </process>
    <process id="process2187b184108" taskpriority="0" logused="4655716" waitresource="KEY: 7:72057594049003520 (233cbd3694c1)" waittime="82" ownerId="313639357" transactionname="DELETE" lasttranstarted="2018-12-09T10:00:27.050" XDES="0x2188843d8a8" lockMode="X" schedulerid="1" kpid="6340" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-09T10:00:00.250" lastbatchcompleted="2018-12-09T10:00:00.250" lastattention="1900-01-01T00:00:00.250" clientapp="SQLAgent - TSQL JobStep (Job 0x19A4A8C8E6580E42BAB84B11DDD66C97 : Step 1)" hostname="HWC-HWP-1098250" hostpid="4044" loginname="NT SERVICE\SQLSERVERAGENT" isolationlevel="read committed (2)" xactid="313639357" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="27" stmtstart="7180" stmtend="7278" sqlhandle="0x0200000013d8441152a66453df9df52295e0815f8e2c4a0b0000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
SET QUOTED_IDENTIFIER ON
DECLARE @LastID INT
SELECT TOP 1 @LastID = ID FROM [TradeBuffer_US_PC] ORDER BY ID DESC

IF @LastID IS NOT NULL
BEGIN
    BEGIN TRY
        ...
        DELETE FROM TradeBuffer_US_PC WHERE ID < @LastID
      </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594049003520" dbid="7" objectname="TradeBuffer_US_PC" indexname="UX_TradeBuffer_US_PC_PlayerID_ItemID_Amount_TotalPrice_ExpireTime_GuildName" id="lock217fdb50980" mode="X" associatedObjectId="72057594049003520">
      <owner-list>
        <owner id="process2187b184108" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process21881f1bc28" mode="RangeS-U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594049003520" dbid="7" objectname="TradeBuffer_US_PC" indexname="UX_TradeBuffer_US_PC_PlayerID_ItemID_Amount_TotalPrice_ExpireTime_GuildName" id="lock2178726f880" mode="RangeS-U" associatedObjectId="72057594049003520">
      <owner-list>
        <owner id="process21881f1bc28" mode="RangeS-U" />
      </owner-list>
      <waiter-list>
        <waiter id="process2187b184108" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

ID is a primary identity key.

The first process is doing a multi insert (INSERT INTO ... VALUES(...), (...), (...)) and the second process is doing a delete on the same table.

Second process is merging the buffer table to the real table and purging the buffer table.

The buffer table has a unique key defined with IGNORE_DUP_KEY on. So the multi insert might contain duplicated rows that's already in the buffer table.

From the graph it seems like delete process is the owner of exclusive lock and the insert process is waiting to get the update lock to do insert.

But the second keylock block says the insert process owns the update lock (which I assume its one of the new row inserted) and delete process is trying to acquire exclusive lock.

This looks strange to me because the delete process already owns the exclusive lock why would it request for it again and fail?

The delete process is running inside SQL Agent job and is NOT wrapped in begin trans block

Why is deadlock happening in this case and is there anything I could do to avoid it?

Answer 1

The insert process (process21881f1bc28) has a partial range lock on keys in the nonclustered index of the table TradeBuffer_US_PC, but is waiting on the rest of the keys in the range.

The delete process (process2187b184108) has an exclusive lock on one of the keys in that same range, and is waiting on a lock to delete one of the other keys in that range.

Related to that, check out this extremely detailed blog post:

Locking in Microsoft SQL Server (Part 20) – Range lock (RangeS-U) deadlock due to IGNORE_DUP_KEY index option

The author there demonstrates that inserts into tables with IGNORE_DUP_KEY nonclustered indexes can lead to SERIALIZABLE locking behavior on the nonclustered index keys. This is the most restrictive locking behavior SQL Server offers, and is thus least conducive to concurrency.

Unfortunately, this means you have some work on your hands to alleviate this deadlock situation. Here are some general ideas that might help, without having seen the code involved:

• Your lowest effort option is to delete rows in smaller batches than you are currently. This would at least limit the number of deadlocks you encounter.

• A likely larger change would be to explicitly use the SERIALIZBLE isolation level in the delete process, and then do a SELECT on the entire range of keys that you expect to delete at the very beginning. This should acquire the range of locks you need to delete.

• Another option would be to take advantage of ordering. Change both the insert and delete processes so that they are operating on the data in the same order (key order ascending, for instance). This will hopefully allow locks to be taken in the same order, and thus they will be less likely to deadlock (and will instead just wait on the incompatible locks to be released).

PS: I noticed this in the process node of the insert process:

isolationlevel="read uncommitted (1)"

I'm not sure why you're using READ UNCOMMITTED for this data modification query, but it's likely not doing what you hope it does.