寻求EF Core中SQL死锁问题的调试指导

问题背景 #

使用EF Core时遇到SQL死锁，怀疑问题出在_context.InventoryTracks.Remove(id)这行代码——这是代码中唯一对InventoryTracks表执行删除操作的位置，会生成对应的DELETE语句。死锁涉及Key Lock，可能和之前带Include的查询有关，但无法理解为何两个执行相同DELETE操作的进程中，会有一个成为死锁受害者。

执行该Remove操作前，已对关联表执行RemoveRange和SaveChanges，但数据库已配置级联删除，若跳过该步骤，删除InventoryTracks记录时会自动清理关联数据。

死锁日志 #

<deadlock>
 <victim-list>
  <victimProcess id="process15c4352d468" />
 </victim-list>
 <process-list>
  <process id="process15c4352d468" taskpriority="0" logused="604" waitresource="KEY: 5:72057654391668736 (b4d7f128fa4c)" waittime="1111" ownerId="2312262978" transactionname="user_transaction" lasttranstarted="2024-09-25T10:07:12.913" XDES="0x154f76b8428" lockMode="RangeS-U" schedulerid="4" kpid="4460" status="suspended" spid="96" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-09-25T10:07:12.933" lastbatchcompleted="2024-09-25T10:07:12.923" lastattention="1900-01-01T00:00:00.923" clientapp="MyAPI" hostname="ProdServer" hostpid="14488" loginname="AD\user" isolationlevel="read committed (2)" xactid="2312262978" currentdb="5" currentdbname="MyDB" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="2" stmtstart="86" stmtend="258" sqlhandle="0x0200000060b625062105144680a22bc60c73056ff9771a4c0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p2 int,@p3 varbinary(8))SET NOCOUNT ON;
DELETE FROM [dbo].[InventoryTrack]
WHERE [InventoryTrackId] = @p2 AND [RowVersion] = @p3;
SELECT @@ROWCOUNT;

   </inputbuf>
  </process>
  <process id="process15aef12e108" taskpriority="0" logused="960" waitresource="KEY: 5:72057654391668736 (cf6d78acbc8a)" waittime="1121" ownerId="2312262977" transactionname="user_transaction" lasttranstarted="2024-09-25T10:07:12.903" XDES="0x1552a7c0428" lockMode="RangeS-U" schedulerid="7" kpid="9872" status="suspended" spid="125" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-09-25T10:07:12.923" lastbatchcompleted="2024-09-25T10:07:12.913" lastattention="1900-01-01T00:00:00.913" clientapp="MyAPI" hostname="ProdServer" hostpid="14488" loginname="AD\user" isolationlevel="read committed (2)" xactid="2312262977" currentdb="5" currentdbname="MyDB" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="2" stmtstart="86" stmtend="258" sqlhandle="0x0200000060b625062105144680a22bc60c73056ff9771a4c0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p2 int,@p3 varbinary(8))SET NOCOUNT ON;
DELETE FROM [dbo].[InventoryTrack]
WHERE [InventoryTrackId] = @p2 AND [RowVersion] = @p3;
SELECT @@ROWCOUNT;

   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057654391668736" dbid="5" objectname="MyDB.dbo.PickListItem" indexname="PK_PickListItem" id="lock150c6d0f000" mode="RangeS-U" associatedObjectId="72057654391668736">
   <owner-list>
    <owner id="process15aef12e108" mode="RangeS-U" />
   </owner-list>
   <waiter-list>
    <waiter id="process15c4352d468" mode="RangeS-U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057654391668736" dbid="5" objectname="MyDB.dbo.PickListItem" indexname="PK_PickListItem" id="lock15db6cb0200" mode="X" associatedObjectId="72057654391668736">
   <owner-list>
    <owner id="process15c4352d468" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process15aef12e108" mode="RangeS-U" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

相关EF代码 #

//code above, omitted for brevity

incompleteTotesForPick = _context.PickListTotes
    .Where(plt => plt.PickStatusId != (int)PickStatus.Completed)
    .Include(pli => pli.PickListItem.InventoryTrack)
    .Any(
        ip =>
            ip.PickListItem.InventoryTrack.InventoryTrackId ==
            pickListTote.PickListItem.InventoryTrackId);

var inventoryTrack = pickListTote.PickListItem.InventoryTrack;

var pickListItemTotes = _context.PickListTotes
    .Where(pl => pl.PickListItem.PickListItemId == pickListTote.PickListItemId);

totalPickQty = pickListItemTotes.Sum(q => q.PickedQty);

_context.PickListTotes.RemoveRange(pickListItemTotes);  
_context.SaveChanges();

if (!incompleteTotesForPick)
{
    _context.InventoryTracks.Remove(InventoryTrack);    //this must generate the delete and be where the deadlock occurs 
    _context.SaveChanges();
}

//more code, omitted for brevity

调试建议 #

• 锁定死锁核心资源：从日志看，死锁实际发生在PickListItem表的主键索引PK_PickListItem上，而非InventoryTracks表。两个进程分别持有该表某条记录的RangeS-U锁，同时请求对方持有的记录锁，形成循环等待。
• 规范级联删除的锁顺序：虽然手动删除了PickListTotes，但删除InventoryTracks时的级联操作仍会触发对PickListItem的锁操作。要确保所有进程处理相关记录时，始终按相同顺序（比如主键ID升序）获取锁，避免循环等待。
• 合并事务边界：当前代码多次调用SaveChanges()拆分了事务，可能导致锁持有时间变长或锁顺序不可控。尝试将相关操作合并到一个事务中，明确锁的生命周期。
• 移除手动删除关联表的操作：既然数据库已配置级联删除，直接删除InventoryTracks即可由数据库自动清理关联数据，手动删除再触发级联会增加额外锁竞争。
• 优化查询的Include操作：最初的Any查询使用Include加载了不必要的关联数据，可能持有额外锁。修改为直接通过关联ID判断，避免加载实体：

  incompleteTotesForPick = _context.PickListTotes
      .Where(plt => plt.PickStatusId != (int)PickStatus.Completed)
      .Any(ip => ip.PickListItem.InventoryTrackId == pickListTote.PickListItem.InventoryTrackId);
  

• 跟踪锁的获取过程：用SQL Server Profiler或Extended Events跟踪锁的获取和释放，明确两个进程的锁顺序差异，定位死锁触发点。

内容的提问来源于stack exchange，提问作者BBauer42