死锁图：

<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="HWC-HWP-1098250" hostpid="4724" loginname="ESOTrade" 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是主要标识密钥 .

第一个进程是进行多插入，第二个进程是在同一个表上进行删除 .

第二个过程是将缓冲区表与实际表合并并清除缓冲区表 .

从图中看来，删除进程是独占锁的所有者，插入进程正在等待更新锁进行插入 .

但第二个键锁块表示插入进程拥有更新锁（我假设其中一个插入了新行）并且删除进程正在尝试获取独占锁 .

这看起来很奇怪，因为删除进程已经拥有独占锁，为什么它会再次请求它并失败？

删除过程在SQL代理作业中运行，不包含在 begin trans 块中

为什么在这种情况下会发生死锁，我能做些什么来避免它？

sql sql-server database-deadlocks