SQL Server Deadlock by IX pagelock

Question

I have a deadlock and I don't see how coult it be solved. I have already fixed some other queries coming from EF side that caused the same deadlock (at same row in the sp) but this one cannot be modified, it's a very basic query and I think there must be an easier way rewriting the SP instead or modifying indexes to avoid page locks.

Three tables:

Workitems(Id, DMC,...)
Charges(Id, ...)
ChargeItems(Id, Charge_Id, Workitem_Id, ...)

Two processes:

An EF query that is called many times. (~5-10/minute)
A stored procedure which is archiving data by moving archives into an other database and deleting them from source.

The deadlock:

It is thrown at the very last step when the SP tries to remove empty Charges, where ChargeItems has no records. To this point it has already deleted all ChargeItems and only empty charges and workitems has to be deleted.

The query run by EF is searching for a Workitem by its DMC while the SP tries to remove Charges.

SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[DMC] AS [DMC], 
    [Limit1].[FirstSeen] AS [FirstSeen], 
    [Limit1].[DrawingNo] AS [DrawingNo], 
    [Limit1].[MachineId] AS [MachineId], 
    [Limit1].[WorkItemState_Id] AS [WorkItemState_Id], 
    [Limit1].[ItemType_Id] AS [ItemType_Id], 
    [Limit1].[Repaired] AS [Repaired], 
    [Limit1].[MachineCycle] AS [MachineCycle], 
    [Limit1].[FirstSeenCheck] AS [FirstSeenCheck], 
    [Limit1].[LastSeen] AS [LastSeen], 
    [Limit1].[Archive] AS [Archive], 
    [Limit1].[CastingDateString] AS [CastingDateString], 
    [Limit1].[Deleted] AS [Deleted], 
    [Limit1].[DMC2] AS [DMC2], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[WorkPlace_Id] AS [WorkPlace_Id], 
    [Limit1].[CastingFormIdent_Id] AS [CastingFormIdent_Id], 
    [Limit1].[FormIdentItemType_Id] AS [FormIdentItemType_Id]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[DMC] AS [DMC], 
        [Extent1].[FirstSeen] AS [FirstSeen], 
        [Extent1].[DrawingNo] AS [DrawingNo], 
        [Extent1].[MachineId] AS [MachineId], 
        [Extent1].[WorkItemState_Id] AS [WorkItemState_Id], 
        [Extent1].[ItemType_Id] AS [ItemType_Id], 
        [Extent1].[Repaired] AS [Repaired], 
        [Extent1].[MachineCycle] AS [MachineCycle], 
        [Extent1].[FirstSeenCheck] AS [FirstSeenCheck], 
        [Extent1].[LastSeen] AS [LastSeen], 
        [Extent1].[Archive] AS [Archive], 
        [Extent1].[CastingDateString] AS [CastingDateString], 
        [Extent1].[Deleted] AS [Deleted], 
        [Extent1].[DMC2] AS [DMC2], 
        [Extent1].[WorkPlace_Id] AS [WorkPlace_Id], 
        [Extent1].[CastingFormIdent_Id] AS [CastingFormIdent_Id], 
        [Extent1].[FormIdentItemType_Id] AS [FormIdentItemType_Id], 
        [Extent2].[Id] AS [Id1]
        FROM WorkItems AS [Extent1]
        LEFT OUTER JOIN [dbo].[ChargeItems] AS [Extent2] ON [Extent1].[Id] = [Extent2].[WorkItem_Id]
        WHERE ([Extent1].[DMC] = '') OR (([Extent1].[DMC] IS NULL))
    )  AS [Limit1]

Execution plan of the select:

Part of the SP:

;with chargesToDelete(id, ciid) as (
                    select c.id, ci.Id from @chargeids c
                    left join dbo.chargeitems ci on ci.Charge_Id = c.id
                    where ci.id is null
                )

                delete from dbo.charges
                    where Id in (select id from chargesToDelete)

Deadlock graph xml:

    
 
  
 
 
  
   
    
delete from LP_R.dbo.workitems where id in (select id from @workitemIds);    
    
exec Archive_Finish @Day, @Dryrun, @MaxWorkitems;    
    
EXEC    @return_value = [dbo].[Archive]
        @Day = 450,
        @Dryrun = 0    
   
   

DECLARE @return_value int

EXEC    @return_value = [dbo].[Archive]
        @Day = 450,
        @Dryrun = 0

SELECT  'Return Value' = @return_value

   
  
  
   
    
SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[DMC] AS [DMC], 
    [Limit1].[FirstSeen] AS [FirstSeen], 
    [Limit1].[DrawingNo] AS [DrawingNo], 
    [Limit1].[MachineId] AS [MachineId], 
    [Limit1].[WorkItemState_Id] AS [WorkItemState_Id], 
    [Limit1].[ItemType_Id] AS [ItemType_Id], 
    [Limit1].[Repaired] AS [Repaired], 
    [Limit1].[MachineCycle] AS [MachineCycle], 
    [Limit1].[FirstSeenCheck] AS [FirstSeenCheck], 
    [Limit1].[LastSeen] AS [LastSeen], 
    [Limit1].[Archive] AS [Archive], 
    [Limit1].[CastingDateString] AS [CastingDateString], 
    [Limit1].[Deleted] AS [Deleted], 
    [Limit1].[DMC2] AS [DMC2], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[WorkPlace_Id] AS [WorkPlace_Id], 
    [Limit1].[CastingFormIdent_Id] AS [CastingFormIdent_Id], 
    [Limit1].[FormIdentItemType_Id] AS [FormIdentItemType_Id]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[DMC] AS [DMC], 
        [Extent1].[FirstSeen] AS [FirstSeen], 
        [Extent1    
    
unknown    
   
   
(@p__linq__0 nvarchar(4000))SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[DMC] AS [DMC], 
    [Limit1].[FirstSeen] AS [FirstSeen], 
    [Limit1].[DrawingNo] AS [DrawingNo], 
    [Limit1].[MachineId] AS [MachineId], 
    [Limit1].[WorkItemState_Id] AS [WorkItemState_Id], 
    [Limit1].[ItemType_Id] AS [ItemType_Id], 
    [Limit1].[Repaired] AS [Repaired], 
    [Limit1].[MachineCycle] AS [MachineCycle], 
    [Limit1].[FirstSeenCheck] AS [FirstSeenCheck], 
    [Limit1].[LastSeen] AS [LastSeen], 
    [Limit1].[Archive] AS [Archive], 
    [Limit1].[CastingDateString] AS [CastingDateString], 
    [Limit1].[Deleted] AS [Deleted], 
    [Limit1].[DMC2] AS [DMC2], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[WorkPlace_Id] AS [WorkPlace_Id], 
    [Limit1].[CastingFormIdent_Id] AS [CastingFormIdent_Id], 
    [Limit1].[FormIdentItemType_Id] AS [FormIdentItemType_Id]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[DMC] AS [DMC], 
        [Extent1].[FirstSeen] AS [F

IVNSTN · Accepted Answer

(as a side-note)

delete c 
from dbo.charges c
inner join @chargeids t
  on t.id = c.id
where not exists(
  select 1 from dbo.chargeitems ci 
  where ci.Charge_id = c.id
)

I suppose there are no chargeitems already at this stage actually. And they were probably deleted just before this delete statement. So archiving process transaction could be much longer then it's shown.

So, your delete process is deleting specific rows and places an intent-X lock on pages, whilst reading process is scanning pages, probably, in different order. FKs could place some more locks if there are any.

TIPS

Have a look at select statement's actual execution plan. It is requesting TOP 1 with no order, uses far not precise where predicate and joins chargeitems on workitem_id (which perhaps has no index on it). Fixing it could help getting rid of scan (if any) while reading. May be you could try to select top1 workitem and only after that select top1 chargeitem for it.

You could try to apply READPAST hint on reading statement (will not be waiting for locked pages) or raise granularity of delete statements to PAGLOCK for example. Try TABLOCK for delete process if it's rarely executed, and if it's fine for this system.

UPD

Actually I missed the major point: you pointed at delete from charges whereas deadlock is on WorkItem (as deadlock graph is clearly showing). But this does not cancel the rest of my assumptions. As the execution plan shows WorkItem is truly scanned whilst this delete is performed on specific rows:

delete from LP_R.dbo.workitems where id in (select id from @workitemIds);

You can apply tips from my post to select statement and/or delete statement (all of them within archiving proc).

SQL Server Deadlock by IX pagelock

Answers (2)

TIPS

UPD

Related Questions