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?