sql - 分析sql死锁xml

Question

我需要帮助来分析以下死锁 xml

<deadlock>
    <victim-list>
        <victimProcess id="processa9f6f73c28" />
    </victim-list>
    <process-list>
        <process id="processa9f6f73c28" taskpriority="0" logused="0" waitresource="KEY: 5:72057594060013568 (bd1a413b4dd8)" waittime="1759" ownerId="19463226" transactionname="user_transaction" lasttranstarted="2018-05-21T14:43:38.640" XDES="0xa9dec70458" lockMode="X" schedulerid="2" kpid="8068" status="suspended" spid="122" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-21T14:43:38.640" lastbatchcompleted="2018-05-21T14:43:38.637" lastattention="1900-01-01T00:00:00.637" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF430FC8" hostpid="12344" loginname="officearchitect" isolationlevel="read committed (2)" xactid="19463226" currentdb="5" currentdbname="OfficeArchitect_Performance_Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="d2558974-73ab-4869-acd2-9cce4009286e.model.RelationshipPair_DeleteByRelationshipIds" queryhash="0x9a6597d902cb7ffa" queryplanhash="0x4f762f1ec930146f" line="7" stmtstart="302" stmtend="566" sqlhandle="0x03000500f540c416e4e82300e7a8000001000000000000000000000000000000000000000000000000000000">DELETE
        RP
    FROM
        [model].RelationshipPair RP
    INNER JOIN 
        @RelationshipIdTable RIT
    ON
        RP.RelationshipId = RIT.EntityI</frame>
                <frame procname="d2558974-73ab-4869-acd2-9cce4009286e.model.ModelItem_Relationship_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="20" stmtstart="910" stmtend="1066" sqlhandle="0x030005000d989e702ae82300e7a8000001000000000000000000000000000000000000000000000000000000">EXEC [model].[RelationshipPair_DeleteByRelationshipIds]
        @RelationshipIdTabl</frame>
            </executionStack>
            <inputbuf>Proc [Database Id = 5 Object Id = 1889441805]</inputbuf>
        </process>
        <process id="processa9f9857088" taskpriority="0" logused="624" waitresource="KEY: 5:72057594060013568 (3f1e49aa6519)" waittime="2779" ownerId="19414353" transactionname="user_transaction" lasttranstarted="2018-05-21T14:43:28.600" XDES="0xaa0a244458" lockMode="RangeS-S" schedulerid="2" kpid="51500" status="suspended" spid="164" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-21T14:43:28.603" lastbatchcompleted="2018-05-21T14:43:28.593" lastattention="2018-05-21T14:38:44.820" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF430FC8" hostpid="12344" loginname="officearchitect" isolationlevel="read committed (2)" xactid="19414353" currentdb="5" currentdbname="OfficeArchitect_Performance_Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="d2558974-73ab-4869-acd2-9cce4009286e.model.ModelItem_Generic_Create" queryhash="0x21c1a974c29371a5" queryplanhash="0x60900e552e5614c5" line="17" stmtstart="898" stmtend="1402" sqlhandle="0x030005005dcdd742fde62300e7a8000001000000000000000000000000000000000000000000000000000000">INSERT INTO [model].[ModelItem]
    (
        [MetamodelItemId],
        [ModelItemCategoryId]
    )
    OUTPUT [inserted].[ModelItemId], [inserted].[MetamodelItemId]
    INTO @ModelItemIdsByMetamodelId
    SELECT EntityId, @ModelItemCategoryId
    FROM @MetamodelItemIdTabl</frame>
                <frame procname="d2558974-73ab-4869-acd2-9cce4009286e.model.ModelItem_Relationship_Create" queryhash="0x41bf1ae3ccbfaccc" queryplanhash="0x76a3cb6aa572b737" line="134" stmtstart="9960" stmtend="10500" sqlhandle="0x030005009b4fb66e1be82300e7a8000001000000000000000000000000000000000000000000000000000000">INSERT INTO @tempStorage 
        EXECUTE [model].[ModelItem_Generic_Create]  
            @MetamodelItemIdTable = @metamodelIds,
            @ModelId = @ModelId,
            @ModelItemCategoryId = @ModelItemCategoryId,
            @DateLastModified = @DateLastModified,
            @LastModifiedBy = @LastModifiedB</frame>
            </executionStack>
            <inputbuf>Proc [Database Id = 5 Object Id = 1857441691]</inputbuf>
        </process>
        <process id="processa9fb862108" taskpriority="0" logused="43256" waitresource="KEY: 5:72057594060013568 (bd1a413b4dd8)" waittime="40" ownerId="19385479" transactionname="user_transaction" lasttranstarted="2018-05-21T14:43:27.370" XDES="0xa9da75c458" lockMode="RangeS-S" schedulerid="1" kpid="51692" status="suspended" spid="193" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-21T14:43:40.320" lastbatchcompleted="2018-05-21T14:43:40.327" lastattention="1900-01-01T00:00:00.327" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF430FC8" hostpid="12344" loginname="officearchitect" isolationlevel="read committed (2)" xactid="19385479" currentdb="5" currentdbname="OfficeArchitect_Performance_Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="d2558974-73ab-4869-acd2-9cce4009286e.model.ModelItem_Generic_Delete" queryhash="0xd6e2f8f770b21179" queryplanhash="0x18df7aa720a890f6" line="80" stmtstart="4110" stmtend="4360" sqlhandle="0x0300050096f1cb4302e72300e7a8000001000000000000000000000000000000000000000000000000000000">DELETE
            MI
        FROM
            [model].ModelItem MI
        INNER JOIN
            @ModelItemIdTable MIT
        ON
            MIT.EntityId = MI.ModelItemI</frame>
                <frame procname="d2558974-73ab-4869-acd2-9cce4009286e.model.ModelItem_Object_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="25" stmtstart="1088" stmtend="1302" sqlhandle="0x0300050061e52c65bce72300e7a8000001000000000000000000000000000000000000000000000000000000">EXEC [model].[ModelItem_Generic_Delete] 
        @ObjectIdTable,
        @MarkAsDeleted,
        @DeletedBy, 
        @DeletedO</frame>
            </executionStack>
            <inputbuf>Proc [Database Id = 5 Object Id = 1697441121]</inputbuf>
        </process>
        <process id="processa9e0ddc108" taskpriority="0" logused="2657548" waitresource="KEY: 5:72057594060013568 (3f1e49aa6519)" waittime="2779" ownerId="19456397" transactionname="user_transaction" lasttranstarted="2018-05-21T14:43:30.350" XDES="0xa9dc49c458" lockMode="RangeS-S" schedulerid="2" kpid="55424" status="suspended" spid="85" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-21T14:43:30.537" lastbatchcompleted="2018-05-21T14:43:30.530" lastattention="1900-01-01T00:00:00.530" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF430FC8" hostpid="12344" loginname="officearchitect" isolationlevel="read committed (2)" xactid="19456397" currentdb="5" currentdbname="OfficeArchitect_Performance_Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="d2558974-73ab-4869-acd2-9cce4009286e.model.ModelItem_Generic_Delete" queryhash="0xd6e2f8f770b21179" queryplanhash="0x18df7aa720a890f6" line="80" stmtstart="4110" stmtend="4360" sqlhandle="0x0300050096f1cb4302e72300e7a8000001000000000000000000000000000000000000000000000000000000">DELETE
            MI
        FROM
            [model].ModelItem MI
        INNER JOIN
            @ModelItemIdTable MIT
        ON
            MIT.EntityId = MI.ModelItemI</frame>
                <frame procname="d2558974-73ab-4869-acd2-9cce4009286e.model.ModelItem_Object_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="25" stmtstart="1088" stmtend="1302" sqlhandle="0x0300050061e52c65bce72300e7a8000001000000000000000000000000000000000000000000000000000000">EXEC [model].[ModelItem_Generic_Delete] 
        @ObjectIdTable,
        @MarkAsDeleted,
        @DeletedBy, 
        @DeletedO</frame>
            </executionStack>
            <inputbuf>Proc [Database Id = 5 Object Id = 1697441121]</inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594060013568" dbid="5" objectname="d2558974-73ab-4869-acd2-9cce4009286e.model.RelationshipPair" indexname="PK_RelationshipPair_RelationshipPairId" id="lockaa19259180" mode="RangeS-U" associatedObjectId="72057594060013568">
            <owner-list>
                <owner id="processa9f9857088" mode="RangeS-S" />
            </owner-list>
            <waiter-list>
                <waiter id="processa9f6f73c28" mode="X" requestType="convert" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594060013568" dbid="5" objectname="d2558974-73ab-4869-acd2-9cce4009286e.model.RelationshipPair" indexname="PK_RelationshipPair_RelationshipPairId" id="lockaa18438980" mode="RangeX-X" associatedObjectId="72057594060013568">
            <owner-list>
                <owner id="processa9e0ddc108" mode="RangeS-S" requestType="wait" />
            </owner-list>
            <waiter-list>
                <waiter id="processa9f9857088" mode="RangeS-S" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594060013568" dbid="5" objectname="d2558974-73ab-4869-acd2-9cce4009286e.model.RelationshipPair" indexname="PK_RelationshipPair_RelationshipPairId" id="lockaa19259180" mode="RangeS-U" associatedObjectId="72057594060013568">
            <owner-list>
                <owner id="processa9f6f73c28" mode="U" />
                <owner id="processa9f6f73c28" mode="X" requestType="convert" />
            </owner-list>
            <waiter-list>
                <waiter id="processa9fb862108" mode="RangeS-S" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594060013568" dbid="5" objectname="d2558974-73ab-4869-acd2-9cce4009286e.model.RelationshipPair" indexname="PK_RelationshipPair_RelationshipPairId" id="lockaa18438980" mode="RangeX-X" associatedObjectId="72057594060013568">
            <owner-list>
                <owner id="processa9fb862108" mode="RangeX-X" />
            </owner-list>
            <waiter-list>
                <waiter id="processa9e0ddc108" mode="RangeS-S" requestType="wait" />
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

现在，据我所知(这对我来说有点新)，DELETE RP 语句是“受害者”，这是由于 INSERT INTO [ model].[ModelItem] 语句。

锁定索引 PK_RelationshipPair_RelationshipPairId 时出现问题。

我不完全理解的是 RangeA-B 锁。据我了解，索引上锁定了一系列值。但不太清楚为什么。

我知道，如果没有实际的 sql 代码，很难准确地了解正在发生的情况，但我需要一些关于如何诊断此问题的帮助。

我尝试通过在两个事务中运行 DELETE 和 INSERT INTO 语句来复制死锁(并且不完成或回滚插入)，但没有死锁到目前为止。

编辑

C#层事务范围设置如下

var transactionOptions = return new TransactionOptions
{
    IsolationLevel = IsolationLevel.ReadCommitted,
    Timeout = TransactionManager.MaximumTimeout
};

using (var transaction = new TransactionScope(TransactionScopeOption.Required, transactionOptions, TransactionScopeAsyncFlowOption.Enabled))
{
    await action(transaction);
    transaction.Complete();
}

Answer 1

您似乎正在使用 TransactionScope 类，该类强制在可序列化隔离级别上常见范围锁，尽管您使用读提交隔离级别。这些范围锁lockMode="RangeS-S"会提示阻塞和死锁。`

尽管您在事务选项上指定“已提交读取”，TransactionScope 仍会强制仅存在于可序列化隔离上的范围锁，如所述 here .