Reputation: 3667
I am looking for some guidance on how to best debug this deadlock. I'm using EF Core and believe the line causing the issue is this: _context.InventoryTracks.Remove(id). This is the only place in code a remove is performed on that table. That would generate the DELETE statement shown below. There is a keylock, which may come from a prior query with an Include (or something else), but I'm not understanding why there are 2 processes here doing the same exact thing (the delete) and one becomes the victim. Prior to the .Remove, there is a .RemoveRange and .SaveChanges on a related table. Interestingly enough, if that isn't done at all, deleting the InventoryTracks record would automatically clean up what is being removed in the .RemoveRange call as cascade delete is set on the DB. Any suggestions on how to better debug this are much appreciated. Thanks.
<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>
Here is the EF code from the project which was created a while ago by people who are no longer around and there is no accompanying documentation. I removed code before and after the area of consideration.
//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
Upvotes: 0
Views: 57