代码之家 › 专栏 › 技术社区 › Perrier

SQL Server因IX页锁而死锁

deadlock entity-framework sql-server

Perrier · 技术社区 · 6 年前

我陷入僵局,不知道怎么解决。我已经修复了来自EF端的其他一些导致相同死锁的查询(在sp的同一行),但是这个查询不能修改,它是一个非常基本的查询,我认为应该有一个更简单的方法来重写sp或者修改索引以避免页锁。

三张桌子:

费用(Id,…)

两个过程:

多次调用的EF查询。(~5-10/分钟)
一种存储过程,通过将档案移到另一个数据库中并从源中删除来归档数据。

当SP尝试删除空费用时,在最后一步抛出,此时ChargeItems没有记录。到目前为止,它已经删除了所有费用项目,只需删除空的费用和工作项。

EF运行的查询正在通过其DMC搜索工作项,而SP尝试删除费用。

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]

选择的执行计划:

;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)

死锁图xml:

    <deadlock>
 <victim-list>
  <victimProcess id="process6472ad498" />
 </victim-list>
 <process-list>
  <process id="process6472ad498" taskpriority="5" logused="152924" waitresource="PAGE: 5:1:531207 " waittime="794" ownerId="10001638" transactionname="DELETE" lasttranstarted="2018-08-29T11:50:14.510" XDES="0x6ff07f078" lockMode="IX" schedulerid="7" kpid="7620" status="suspended" spid="89" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2018-08-29T11:22:53.457" lastbatchcompleted="2018-08-29T11:22:53.457" lastattention="1900-01-01T00:00:00.457" clientapp="Microsoft SQL Server Management Studio - Query" hostname="PCSERVER151" hostpid="6480" loginname="PRC\administrator" isolationlevel="read uncommitted (1)" xactid="10001638" currentdb="5" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
   <executionStack>
    <frame procname="LP_R.dbo.Archive_Finish" line="190" stmtstart="12876" stmtend="13044" sqlhandle="0x03000500063ecd76e962c80014a9000001000000000000000000000000000000000000000000000000000000">
delete from LP_R.dbo.workitems where id in (select id from @workitemIds);    </frame>
    <frame procname="LP_R.dbo.Archive" line="64" stmtstart="5142" stmtend="5244" sqlhandle="0x030005007886b57874f2b30014a9000001000000000000000000000000000000000000000000000000000000">
exec Archive_Finish @Day, @Dryrun, @MaxWorkitems;    </frame>
    <frame procname="adhoc" line="4" stmtstart="62" stmtend="200" sqlhandle="0x0100050010f3f82c309a63770600000000000000000000000000000000000000000000000000000000000000">
EXEC    @return_value = [dbo].[Archive]
        @Day = 450,
        @Dryrun = 0    </frame>
   </executionStack>
   <inputbuf>

DECLARE @return_value int

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

SELECT  'Return Value' = @return_value

   </inputbuf>
  </process>
  <process id="process66f184558" taskpriority="0" logused="0" waitresource="PAGE: 5:1:114492 " waittime="913" ownerId="10002051" transactionname="SELECT" lasttranstarted="2018-08-29T11:50:15.210" XDES="0x6b379ad00" lockMode="S" schedulerid="5" kpid="3860" status="suspended" spid="67" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-08-29T11:50:15.210" lastbatchcompleted="2018-08-29T11:50:15.210" lastattention="1900-01-01T00:00:00.210" clientapp="EntityFramework" hostname="PCSERVER151" hostpid="3520" loginname="sa" isolationlevel="read committed (2)" xactid="10002051" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="56" sqlhandle="0x02000000412fd7099fe0d3410b538a2193192ac8c5143cf20000000000000000000000000000000000000000">
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    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@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   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="531207" dbid="5" subresource="FULL" objectname="LP_R.dbo.WorkItems" id="lock6d7b2d800" mode="S" associatedObjectId="72057594043891712">
   <owner-list>
    <owner id="process66f184558" mode="S" />
   </owner-list>
   <waiter-list>
    <waiter id="process6472ad498" mode="IX" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="114492" dbid="5" subresource="FULL" objectname="LP_R.dbo.WorkItems" id="lock5cd1a2b00" mode="IX" associatedObjectId="72057594043891712">
   <owner-list>
    <owner id="process6472ad498" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process66f184558" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

2 回复 | 直到 6 年前

Ivan Starostin 6 年前

(作为旁注)

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
)

chargeitems 实际上已经到了这个阶段。它们很可能是在这个delete语句之前被删除的。所以归档过程事务可能比显示的要长得多。

rows 并在页面上放置一个intent-X锁,同时读取过程正在扫描 pages FK 如果有锁的话,我们可以多放几把。

提示

TOP 1 where 谓词和连接在 workitem_id (上面可能没有索引)。修好它有助于摆脱 scan (如有)阅读时。也许你可以试着选择 top1 workitem 只有在那之后 top1 chargeitem

你可以试着申请 READPAST 关于读取语句的提示(不会等待锁定的页)或将delete语句的粒度提高到 PAGLOCK TABLOCK 对于delete进程,如果它很少执行,并且对于这个系统来说还可以。

升级版

实际上,我错过了要点:你指的是从 charges 而死锁是开着的 WorkItem 如执行计划所示在对特定行执行此删除操作时,将真正扫描:

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

您可以应用我的帖子中的提示来选择语句和/或删除语句(所有这些都在归档过程中)。

David Browne - Microsoft 6 年前

首先,由于此死锁中存在S锁,请考虑将数据库切换为读取提交的快照,以便SELECT查询将使用行版本控制而不是S锁来读取数据库。这将一举解决所有S/X死锁和其他阻塞,但您需要进行测试。

第二,为了解决这个死锁,在存储过程中使用一个事务并尽早获得一个大锁。例如,使用TABLOCKX提示强制它获取独占表锁,而不是IX锁。死锁只有在两个会话时才会发生第一后来尝试获取不兼容的锁。IX和S锁是这个死锁的开始,因此您可以通过确保存储过程不获取微不足道的IX来阻止死锁,并等待它获取一个使它能够成功完成的锁。