Sql-Server

為什麼upsert會導致死鎖？

  • February 17, 2022

我正在執行以下 upsert 語句，我需要在短時間內執行多次。我根據 Aaron Bertrand 在 SQLPerformance.com 上的Please stop using this UPSERT anti-pattern post 中的範例編寫了此語句。我的大部分 UPSERTS 都是 INSERTS，所以我使用了他文章中的那個例子。

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防止這些死鎖嗎？

為防止死鎖，您將需要此表上的正確索引。

我建議使用以下聚集索引之一，如果可以使其獨一無二，那就更好了。

(Date, AggregationConfigurationId)
-- alternatively
(AggregationConfigurationId, Date)

您需要索引的原因有兩個：否則INSERTandNOT EXISTS將鎖定太多，更重要的是，強制查找是順序的

想像一下伺服器必須做什麼。它需要首先找出是否存在任何行：

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

如果沒有（或較差的）索引，它將對錶進行全掃描以找到該行。並且鑑於您已要求UPDLOCK, SERIALIZABLE，它需要RangeS-U在它認為可能匹配的任何行上放置一個 -lock，並將其保留到交易結束。如果這意味著整個表，那麼您會在U整個表上獲得 -lock，IX以及由於可能的鎖升級（這似乎發生在分區上）而導致的鎖。

鎖通常可以防止由於讀取數據而導致的U死鎖，這些數據可能會在以後被修改。U但是，如果U-locks 本身以錯誤的順序發生，那麼您擁有鎖的事實可能對您沒有幫助。這通常發生在查詢並行或發生無序掃描時。

然而，如果您有正確的索引，伺服器可以辨識出很少（或唯一）行將匹配，因此對其進行串列、有序的查找。鎖U現在完全按照預期工作，通過以正確的順序鎖定所有內容來防止死鎖。

引用自：https://dba.stackexchange.com/questions/305233

相關問答

Sql-Server

如何對涉及前 1 個子查詢的 Upsert 使用適當的提示

  • January 10, 2014
檢視問答
Sql-Server

在 SQL Server / Azure SQL 中，有沒有辦法查看查詢產生或想要產生的鎖？

  • May 3, 2022
檢視問答
Sql-Server

如何避免在具有 SERIALIZABLE 隔離級別、INSERT、UPDATE 和 DELETE 的儲存過程中出現死鎖

  • March 16, 2022
檢視問答
Sql-Server

普通 IX 鎖死鎖

  • March 16, 2022
檢視問答
Sql-Server

如何在 SQL Server 中鎖定表以防止其他事務 INSERT 並避免死鎖？

  • August 4, 2021
檢視問答
Sql-Server

並發批量更新觸發鍵鎖

  • May 21, 2021
檢視問答