由于密钥锁涉及3个进程而导致的死锁

Question

我正在试图确定这个死锁是如何发生的，以及我需要做什么来防止它再次发生。

我已经附上了来自SSMS的死锁图图像，SSMS图像提供的分辨率不是很好，我很抱歉。

​

这里要做的是在一个周期中锁定3个进程，所有都在等待主键上的锁，以便表SecurityObject被释放。

此表的主键是群集的，是一个包含四列的组合键。

每个进程正在运行的语句如下所示。它是一个delete命令，它从表中删除与单个列匹配的所有记录。该列是一个GUID标识符，它是复合群集主键中的四列之一。

DELETE FROM SecurityObject WHERE col1 = @val1

其中col1是主键中的四列之一。

我很难理解这种情况是怎么发生的？如何出现主密钥锁的死锁场景？

下面是死锁xml图：

<deadlock>
    <victim-list>
        <victimProcess id="processaeabf84108"/>
    </victim-list>
    <process-list>
        <process id="processaeabf84108" taskpriority="0" logused="0" waitresource="KEY: 14:72057594041925632 (00f78314b62e)" waittime="1754" ownerId="6629325" transactionname="user_transaction" lasttranstarted="2017-08-04T15:16:55.747" XDES="0xaea526f498" lockMode="X" schedulerid="2" kpid="16620" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-08-04T15:16:55.747" lastbatchcompleted="2017-08-04T15:16:55.747" lastattention="1900-01-01T00:00:00.747" clientapp=".Net SqlClient Data Provider" hostname="RDXP0165C9JAWIE" hostpid="19084" loginname="REDMOND\RDXP0165C9JAWIE$" isolationlevel="read committed (2)" xactid="6629325" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="SecurityAuthorization.DB.dbo.spDeleteAllSecurityObjects" line="5" stmtstart="342" stmtend="474" sqlhandle="0x03000e00b56a9938f8fcba00c3a7000001000000000000000000000000000000000000000000000000000000">  DELETE FROM [SecurityObject] WHERE [EnvironmentId] = @EnvironmentI    </frame>
            </executionStack>
            <inputbuf>  Proc [Database Id = 14 Object Id = 949578421]   </inputbuf>
        </process>
        <process id="processaea64a9468" taskpriority="0" logused="0" waitresource="KEY: 14:72057594041925632 (e0caa7da41f0)" waittime="3981" ownerId="6629329" transactionname="user_transaction" lasttranstarted="2017-08-04T15:16:55.750" XDES="0xaea9602408" lockMode="X" schedulerid="1" kpid="14152" status="suspended" spid="76" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-08-04T15:16:55.750" lastbatchcompleted="2017-08-04T15:16:55.750" lastattention="1900-01-01T00:00:00.750" clientapp=".Net SqlClient Data Provider" hostname="RDXP0165C9JAWIE" hostpid="19084" loginname="REDMOND\RDXP0165C9JAWIE$" isolationlevel="read committed (2)" xactid="6629329" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="SecurityAuthorization.DB.dbo.spDeleteAllSecurityObjects" line="5" stmtstart="342" stmtend="474" sqlhandle="0x03000e00b56a9938f8fcba00c3a7000001000000000000000000000000000000000000000000000000000000">  DELETE FROM [SecurityObject] WHERE [EnvironmentId] = @EnvironmentI    </frame>
            </executionStack>
            <inputbuf>  Proc [Database Id = 14 Object Id = 949578421]   </inputbuf>
        </process>
        <process id="processaea686fc28" taskpriority="0" logused="884" waitresource="KEY: 14:72057594041925632 (e0caa7da41f0)" waittime="2105" ownerId="6638253" transactionname="user_transaction" lasttranstarted="2017-08-04T15:16:57.627" XDES="0xaea9460e58" lockMode="X" schedulerid="2" kpid="6528" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-08-04T15:16:57.627" lastbatchcompleted="2017-08-04T15:16:57.627" lastattention="1900-01-01T00:00:00.627" clientapp=".Net SqlClient Data Provider" hostname="RDXP0165C9JAWIE" hostpid="19084" loginname="REDMOND\RDXP0165C9JAWIE$" isolationlevel="read committed (2)" xactid="6638253" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="SecurityAuthorization.DB.dbo.spDeleteAllSecurityObjects" line="5" stmtstart="342" stmtend="474" sqlhandle="0x03000e00b56a9938f8fcba00c3a7000001000000000000000000000000000000000000000000000000000000">  DELETE FROM [SecurityObject] WHERE [EnvironmentId] = @EnvironmentI    </frame>
            </executionStack>
            <inputbuf>  Proc [Database Id = 14 Object Id = 949578421]   </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594041925632" dbid="14" objectname="SecurityAuthorization.DB.dbo.SecurityObject" indexname="PK__Security__185B78FE57F79F91" id="lockaead1a0680" mode="X" associatedObjectId="72057594041925632">
            <owner-list>
                <owner id="processaea686fc28" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="processaeabf84108" mode="X" requestType="wait"/>
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594041925632" dbid="14" objectname="SecurityAuthorization.DB.dbo.SecurityObject" indexname="PK__Security__185B78FE57F79F91" id="lockae6d468f80" mode="X" associatedObjectId="72057594041925632">
            <owner-list>
                <owner id="processaeabf84108" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="processaea64a9468" mode="X" requestType="wait"/>
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594041925632" dbid="14" objectname="SecurityAuthorization.DB.dbo.SecurityObject" indexname="PK__Security__185B78FE57F79F91" id="lockae6d468f80" mode="X" associatedObjectId="72057594041925632">
            <owner-list>
                <owner id="processaea64a9468" mode="X" requestType="wait"/>
            </owner-list>
            <waiter-list>
                <waiter id="processaea686fc28" mode="X" requestType="wait"/>
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>  

以下是存储过程的执行计划：

​

​

Answer 1

经过进一步测试，我成功地将死锁场景的根本原因隔离为并发调用DeleteAll (表中删除记录子集)和插入(插入符合DeleteAll标准的记录)。

导致死锁场景的事件的确切顺序仍不清楚，但通过将隔离级别设置为解决了这个问题。

这是一个可以接受的副作用，它将损害性能(对于我的场景，我们不关心这个操作性能，因为这些操作没有等待，它是一个火灾和忘记进程)。