为什么重新插入会导致死锁？

Question

我正在执行下面的upsert语句，需要在短时间内多次运行该语句。我是根据亚伦·伯特兰在SQLPerformance.com上发表的请停止使用此UPSERT反模式.文章中的例子写这篇文章的。我的大部分UPSERTS都是插入的，所以我在他的文章中使用了这个例子。

BEGIN TRANSACTION;

INSERT AggregationMeasurement (ValueFloat, Date, AggregationConfigurationId)
SELECT @ValueFloat, @Date, @AggregationConfigurationId
WHERE NOT EXISTS
(
    SELECT 1 FROM dbo.AggregationMeasurement WITH (UPDLOCK, SERIALIZABLE)
    WHERE Date = @Date AND AggregationConfigurationId = @AggregationConfigurationId
);

IF @@ROWCOUNT = 0
BEGIN
UPDATE AggregationMeasurement SET 
    ValueFloat = @ValueFloat
WHERE Date = @Date AND AggregationConfigurationId = @AggregationConfigurationId;
END

COMMIT TRANSACTION;

但是，每次这样做都会导致多个死锁，如下所示：

<deadlock>
    <victim-list>
        <victimProcess id="process15af9222ca8" />
    </victim-list>
    <process-list>
        <process id="process15af9222ca8" taskpriority="0" logused="0" waitresource="OBJECT: 5:738101670:1 " waittime="3826" ownerId="346683" transactionname="user_transaction" lasttranstarted="2021-12-21T05:03:14.247" XDES="0x15b02ce8428" lockMode="X" schedulerid="1" kpid="92924" status="suspended" spid="127" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-21T05:03:14.070" lastbatchcompleted="2021-12-21T05:03:14.030" lastattention="1900-01-01T00:00:00.030" clientapp="AzureDataMovement" hostname="ab925934100003P" hostpid="3152" loginname="YVeZ1R096I" isolationlevel="read committed (2)" xactid="346683" currentdb="5" currentdbname="b2c01-sqldb-adaptin-p-001" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="b2b213bd-1c82-4160-8b95-053eff29a124.dbo.sp_UpsertAggregateMeasurement" queryhash="0x72dd67a03d17633c" queryplanhash="0xa6cb7ceab670b343" line="9" stmtstart="348" stmtend="1026" sqlhandle="0x03000500e40bdd577809dd0091ad000001000000000000000000000000000000000000000000000000000000">
                    INSERT AggregationMeasurement (ValueFloat, Date, AggregationConfigurationId)
                    SELECT @ValueFloat, @Date, @AggregationConfigurationId
                    WHERE NOT EXISTS
                    (
                    SELECT 1 FROM dbo.AggregationMeasurement WITH (UPDLOCK, SERIALIZABLE)
                    WHERE Date = @Date AND AggregationConfigurationId = @AggregationConfigurationId
                </frame>
                <frame procname="b2b213bd-1c82-4160-8b95-053eff29a124.dbo.sp_AggregateMeasurement" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="136" stmtstart="10194" stmtend="10476" sqlhandle="0x0300050001c1a15c8f175c01a3ad000001000000000000000000000000000000000000000000000000000000">
                    EXEC sp_UpsertAggregateMeasurement @ValueFloat = @ValueFloat_tmp, @Date = @Date_tmp, @AggregationConfigurationId = @AggregationConfigurationI
                </frame>
            </executionStack>
            <inputbuf>
                Proc [Database Id = 5 Object Id = 1554104577]
            </inputbuf>
        </process>
        <process id="process15ab49c9848" taskpriority="0" logused="0" waitresource="OBJECT: 5:738101670:0 " waittime="3830" ownerId="346672" transactionname="user_transaction" lasttranstarted="2021-12-21T05:03:14.070" XDES="0x15afb548428" lockMode="X" schedulerid="2" kpid="49096" status="suspended" spid="121" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-21T05:03:13.927" lastbatchcompleted="2021-12-21T05:03:13.857" lastattention="1900-01-01T00:00:00.857" clientapp="AzureDataMovement" hostname="adf46566100003T" hostpid="5608" loginname="YVeZ1R096I" isolationlevel="read committed (2)" xactid="346672" currentdb="5" currentdbname="b2c01-sqldb-adaptin-p-001" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="b2b213bd-1c82-4160-8b95-053eff29a124.dbo.sp_UpsertAggregateMeasurement" queryhash="0x72dd67a03d17633c" queryplanhash="0xa6cb7ceab670b343" line="9" stmtstart="348" stmtend="1026" sqlhandle="0x03000500e40bdd577809dd0091ad000001000000000000000000000000000000000000000000000000000000">
                    INSERT AggregationMeasurement (ValueFloat, Date, AggregationConfigurationId)
                    SELECT @ValueFloat, @Date, @AggregationConfigurationId
                    WHERE NOT EXISTS
                    (
                    SELECT 1 FROM dbo.AggregationMeasurement WITH (UPDLOCK, SERIALIZABLE)
                    WHERE Date = @Date AND AggregationConfigurationId = @AggregationConfigurationId
                </frame>
                <frame procname="b2b213bd-1c82-4160-8b95-053eff29a124.dbo.sp_AggregateMeasurement" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="136" stmtstart="10194" stmtend="10476" sqlhandle="0x0300050001c1a15c8f175c01a3ad000001000000000000000000000000000000000000000000000000000000">
                    EXEC sp_UpsertAggregateMeasurement @ValueFloat = @ValueFloat_tmp, @Date = @Date_tmp, @AggregationConfigurationId = @AggregationConfigurationI
                </frame>
            </executionStack>
            <inputbuf>
                Proc [Database Id = 5 Object Id = 1554104577]
            </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <objectlock lockPartition="1" objid="738101670" subresource="FULL" dbid="5" objectname="b2b213bd-1c82-4160-8b95-053eff29a124.dbo.AggregationMeasurement" id="lock15af4c94f00" mode="IX" associatedObjectId="738101670">
            <owner-list>
                <owner id="process15ab49c9848" mode="IX" />
            </owner-list>
            <waiter-list>
                <waiter id="process15af9222ca8" mode="X" requestType="wait" />
            </waiter-list>
        </objectlock>
        <objectlock lockPartition="0" objid="738101670" subresource="FULL" dbid="5" objectname="b2b213bd-1c82-4160-8b95-053eff29a124.dbo.AggregationMeasurement" id="lock15af4cc4780" mode="X" associatedObjectId="738101670">
            <owner-list>
                <owner id="process15af9222ca8" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process15ab49c9848" mode="X" requestType="wait" />
            </waiter-list>
        </objectlock>
    </resource-list>
</deadlock>

我不明白为什么会这样。UPDLOCK和SERIALIZABLE不应该防止这些死锁吗？

Answer 1

您要做的第一件事是确保您有一个满足WHERE子句需求的索引。在这种情况下，它需要Date和AggregationConfigurationId上的键列。这样做将确保锁保持尽可能短的时间。

之后，如果您的UPSERT主要是更新而不是插入，则需要将UPSERT语句修改为下面的语句。否则，您使用的语句看起来很好。

BEGIN TRANSACTION;

UPDATE AggregationMeasurement WITH (UPDLOCK, SERIALIZABLE)
SET ValueFloat = @ValueFloat
WHERE Date = @Date
    AND AggregationConfigurationId = @AggregationConfigurationId;

IF @@ROWCOUNT = 0
BEGIN
    INSERT AggregationMeasurement
        (ValueFloat, Date, AggregationConfigurationId)
    VALUES (@ValueFloat, @Date, @AggregationConfigurationId)
END

COMMIT TRANSACTION;