如何避免这两个 SQL 语句之间出现死锁？

Question

我有两个存储过程在单独的线程中运行，在 SQL Server 2005 上运行。一个过程将新行插入到一组表中，另一个过程从同一组表中删除旧数据。这些过程在表 DLevel 和 Model 上陷入死锁。这是架构：

_{（来源：barramsoft.com）}

表DFile：主键 = DFileID
表DLevel：主键 = DLevelID，外键：DFileID
表模型：主键 = ModelID，外键：DFileID
表ELement：主键= ElementID，外键1：DFileID，外键2：DLevelID

我已经隔离了导致死锁的两个 SQL 语句（每个存储过程各一个）。我已经看到这两个过程报告的死锁。我在这两种情况下都使用 top (1000)，并且这两个语句都在循环中执行，直到它们完成且没有任何行可供删除/插入。

SQL 语句 1：

while (...)
begin
    delete top (1000) from DLevel where DFileID = @fileID1
    ...
end

SQL 语句 2：

while (...)
begin
    insert into Element (ElementID, DFileID, LevelNum, ...)
       select top (1000) el.ElementID, el.DFileID, el.LevelNum, ...
       from   ElementLoader el with (nolock)
              left outer join Element e with (nolock)
                   on e.ElementID = el.ElementID
       where  el.DFileID = @fileID2
       and    e.ElementID is null
       order  by el.ElementID
    ...
end

注意：@fileID1 和 @fileID2 的值始终保证有所不同。 DLevel 表平均大约有。单个 DFileID 包含 60 行，因此一次即可完成所有行的删除。

如何避免这两个 SQL 语句之间出现死锁？

编辑1： 重写以更好地澄清问题；添加图像；简化了 SQL 并删除了与 DLevel 表的连接，这不会导致死锁。

编辑2： 添加了死锁图的XML。
Model 表上现在发生死锁。 Model 的删除语句和模式与 DLevel 表类似。

<deadlock victim="process2bae38">
    <process-list>
        <process id="process2bae38" taskpriority="0" logused="4760" waitresource="PAGE: 11:1:1946" waittime="46" ownerId="4127445" transactionname="DELETE" lasttranstarted="2010-06-24T16:19:00.107" XDES="0xffffffff90552ae0" lockMode="S" schedulerid="1" kpid="14252" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-06-24T16:19:00.107" lastbatchcompleted="2010-06-24T16:19:00.107" clientapp=".Net SqlClient Data Provider" hostname="LT0103" hostpid="1668" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="4127445" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="CadExplorer5.dbo.pCleanUpOldFiles" line="364" stmtstart="23718" stmtend="23834" sqlhandle="0x03000b00fb1c2229b1a7f7009f9d00000100000000000000">
delete top (@batchSize) from Model where DFileID = @fileID     </frame>
            </executionStack>
            <inputbuf>
Proc [Database Id = 11 Object Id = 690101499]    </inputbuf>
        </process>
        <process id="process2c95b8" taskpriority="0" logused="283388" waitresource="KEY: 11:72057594039304192 (8100bdf15e8b)" waittime="78" ownerId="4127412" transactionname="INSERT" lasttranstarted="2010-06-24T16:19:00.103" XDES="0xffffffff81d5ef18" lockMode="S" schedulerid="2" kpid="8460" status="suspended" spid="63" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-06-24T16:18:59.413" lastbatchcompleted="2010-06-24T16:18:59.413" clientapp=".Net SqlClient Data Provider" hostname="LT0103" hostpid="1668" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="4127412" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="CadExplorer5.dbo.pLoadElements" line="288" stmtstart="28796" stmtend="33194" sqlhandle="0x03000b00a689fe2b2c5107019f9d00000100000000000000">
insert into Element (
                        ElementID, DFileID, ModelID, ElementTypeID, CADElementID,
                        ParentID,
                        LevelNum,
                        Depth, NumChildren,
                        Color, Weight, Style, Xlo, Ylo, Zlo, Xhi, Yhi, Zhi,
                        Diagonal, XCenter,
                        BitFlags, ElementModTime
                        )
                  select top (@batchSize)
                        el.ElementID, el.DFileID, el.ModelID, el.ElementTypeID, el.CADElementID,
                        parent.ElementID as ParentID,
                        (case when el.LoaderType = 1 and et.IsGraphical = 1 then el.LevelAttrib else null end) as LevelNum,
                        --l.LevelNum,
                        el.Depth, el.NumChildren,
                        el.Color, el.Weight, el.Style, el.Xlo, el.Ylo, el.Zlo, el.Xhi, el.Yhi, el.Zhi,
                        el.Diagonal, el.XCenter,     </frame>
            </executionStack>
            <inputbuf>
Proc [Database Id = 11 Object Id = 738101670]    </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <pagelock fileid="1" pageid="1946" dbid="11" objectname="CadExplorer5.dbo.Element" id="lockffffffff86ffd080" mode="IX" associatedObjectId="72057594039107584">
            <owner-list>
                <owner id="process2c95b8" mode="IX"/>
            </owner-list>
            <waiter-list>
                <waiter id="process2bae38" mode="S" requestType="wait"/>
            </waiter-list>
        </pagelock>
        <keylock hobtid="72057594039304192" dbid="11" objectname="CadExplorer5.dbo.Model" indexname="PK_Model" id="lockffffffff8d66db80" mode="X" associatedObjectId="72057594039304192">
            <owner-list>
                <owner id="process2bae38" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="process2c95b8" mode="S" requestType="wait"/>
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

Answer 1

一种可能的情况：一个连接在 Element 中插入一行，该行引用 DLevel 中的一行，并且 DLevel 中的该行正被另一个连接删除。您的 nolock 提示不适用于外键。

Answer 2

您可以尝试从 Element 表中删除 DLevel.DFileID 的外键。由于您在 DLevel.DlevelID 上有一个主键，并且您将其引用为 Element 中的外键，因此实际上并不需要 DFileID 外键。

Answer 3

我怀疑删除和插入同时进行一定存在某种关键违规......？