Sql-Server

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

  • March 16, 2022

我在儲存過程中遇到死鎖問題。

此儲存過程是自定義的類全文搜尋系統的一部分。它在記錄更新後呼叫，並插入/更新“單詞”列表以及這些單詞在記錄中的位置。

首先，這裡是儲存過程使用的表。為了簡單起見，我將列定義簡化為一個列表，並跳過了外鍵，但我已經包含了所有索引定義。

CREATE TABLE FTS.Word (
   -- ID, 
   -- StringValue, DateValue, TimeValue, NumericValue, Unit,
   -- Metaphone, FK_BaseWord, BaseWordConfidence, IsStopWord

   CONSTRAINT PK_FTS_Word PRIMARY KEY (ID),
   INDEX IX_FTS_Word_FullValue (StringValue, DateValue, TimeValue, NumericValue, Unit),
   INDEX IX_FTS_Word_DateValue (DateValue),
   INDEX IX_FTS_Word_TimeValue (TimeValue),
   INDEX IX_FTS_Word_NumbValue (NumericValue, Unit),
   INDEX IX_FTS_Word_Metaphone (Metaphone),
   INDEX IX_FTS_Word_BaseWord  (FK_BaseWord)
)

CREATE TABLE FTS.WordUsage_RecordRegularColumn (
   -- ID,
   -- FK_InRecord, FK_InColumn, SubTableRecordID,
   -- FK_Word, FK_WordType, WordTypeConfidence,
   -- StartIndex, EndIndex, FullFieldMatch

   CONSTRAINT PK_FTS_WordUsageRC PRIMARY KEY (ID),
   INDEX IX_FTS_WordUsageRC_Location (FK_InRecord, FK_InColumn, SubTableRecordID),
   INDEX IX_FTS_WordUsageRC_Word     (FK_Word, FK_WordType)
)

CREATE TABLE FTS.WordUsage_RecordLookupColumn (
   -- ID,
   -- FK_InRecord, FK_InColumn, SubTableRecordID,
   -- FK_LookupItem

   CONSTRAINT PK_FTS_WordUsageLC PRIMARY KEY (ID),
   INDEX IX_FTS_WordUsageLC_Location (FK_InRecord, FK_InColumn, SubTableRecordID)
)

這裡實際上涉及兩個儲存過程。我將這一位提取到它自己的儲存過程中以消除程式碼重複，因為需要在兩種情況下執行相同的操作。（另一個與這個問題並不真正相關，因此不包括在內。）

因此，這InsertWords, 僅處理向表中插入新的“單詞” Word（並為某些現有的更新一列）。我相信RecordDataChanged當這是唯一從proc呼叫的東西（稍後顯示）時，我已經讓這不會陷入僵局。

CREATE PROCEDURE FTS.InsertWords
   @words AS FTS.InsertWord READONLY       -- table valued parameter
AS
BEGIN
   SET NOCOUNT ON

   /* **************************************************************************************** */
   -- insert the base word if it doesn't exist yet
   -- and/or anything that doesn't have a base word itself
   /* **************************************************************************************** */

   INSERT INTO FTS.Word
       (StringValue, DateValue, TimeValue, NumericValue, Unit, Metaphone, IsStopWord)
   SELECT DISTINCT
       inp.StringValue, inp.DateValue, inp.TimeValue, inp.NumericValue, inp.Unit, inp.Metaphone,
       CASE WHEN inp.StringValue IS NOT NULL AND EXISTS (SELECT 1 FROM FTS.StopWords WHERE Word = inp.StringValue)
           THEN 1
           ELSE 0
       END
   FROM @words inp
   LEFT JOIN FTS.Word exist WITH (UPDLOCK, INDEX(IX_FTS_Word_FullValue)) 
       ON (
           (exist.StringValue  IS NOT NULL AND exist.StringValue  = inp.StringValue ) OR
           (exist.DateValue    IS NOT NULL AND exist.DateValue    = inp.DateValue   ) OR
           (exist.TimeValue    IS NOT NULL AND exist.TimeValue    = inp.TimeValue   ) OR
           (exist.NumericValue IS NOT NULL AND exist.NumericValue = inp.NumericValue)
       ) AND (inp.Unit IS NULL OR exist.Unit = inp.Unit)
   WHERE exist.ID IS NULL          -- where not exists
       AND inp.BaseWord IS NULL

   /* **************************************************************************************** */
   -- insert the main word if it doesn't exist yet
   /* **************************************************************************************** */

   INSERT INTO FTS.Word
       (StringValue, DateValue, TimeValue, NumericValue, Unit, Metaphone, FK_BaseWord, BaseWordConfidence, IsStopWord)
   SELECT DISTINCT
       inp.StringValue, inp.DateValue, inp.TimeValue, inp.NumericValue, inp.Unit, inp.Metaphone,
       base.ID, inp.BaseWordConfidence,
       CASE WHEN inp.StringValue IS NOT NULL AND EXISTS (SELECT 1 FROM FTS.StopWords WHERE Word = inp.StringValue)
           THEN 1
           ELSE 0
       END
   FROM @words inp
   JOIN FTS.Word base ON inp.BaseWord = base.StringValue   -- only things w/ a string value have base words
   LEFT JOIN FTS.Word exist WITH (UPDLOCK, INDEX(IX_FTS_Word_FullValue)) 
       ON (
           (exist.StringValue  IS NOT NULL AND exist.StringValue  = inp.StringValue ) OR
           (exist.DateValue    IS NOT NULL AND exist.DateValue    = inp.DateValue   ) OR
           (exist.TimeValue    IS NOT NULL AND exist.TimeValue    = inp.TimeValue   ) OR
           (exist.NumericValue IS NOT NULL AND exist.NumericValue = inp.NumericValue)
       ) AND (inp.Unit IS NULL OR exist.Unit = inp.Unit)
   WHERE exist.ID IS NULL          -- where not exists
       AND inp.BaseWord IS NOT NULL

   /* **************************************************************************************** */
   -- update all
   /* **************************************************************************************** */

   UPDATE upd
   SET upd.BaseWordConfidence = inp.BaseWordConfidence
   FROM FTS.Word upd WITH (UPDLOCK, INDEX(IX_FTS_Word_FullValue))
   INNER JOIN @words AS inp ON  (
       (upd.StringValue  IS NOT NULL AND upd.StringValue  = inp.StringValue ) OR
       (upd.DateValue    IS NOT NULL AND upd.DateValue    = inp.DateValue   ) OR
       (upd.TimeValue    IS NOT NULL AND upd.TimeValue    = inp.TimeValue   ) OR
       (upd.NumericValue IS NOT NULL AND upd.NumericValue = inp.NumericValue)
   ) AND (inp.Unit IS NULL OR upd.Unit = inp.Unit)
   WHERE upd.BaseWordConfidence IS NOT NULL
       AND upd.BaseWordConfidence < inp.BaseWordConfidence
END

這就是問題似乎發生的地方。這個儲存的過程呼叫InsertWords，然後將數據插入到兩個“單詞使用”表中。我沒有嘗試處理更新現有項目並僅刪除已刪除的項目等，而是刪除給定記錄欄位中的所有單詞使用，然後插入它目前應該使用的任何單詞。

CREATE PROCEDURE FTS.RecordDataChanged
   @recordId AS bigint,
   @words    AS FTS.RecordDataWord READONLY    -- table valued parameter
AS
BEGIN
   SET NOCOUNT ON

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN TRANSACTION

       /* **************************************************************************************** */
       -- call FTS.InsertWords
       /* **************************************************************************************** */

       DECLARE @insWords FTS.InsertWord

       INSERT INTO @insWords
       SELECT StringValue, DateValue, TimeValue, NumericValue, Unit, Metaphone, BaseWord, BaseWordConfidence
       FROM @words
       WHERE LookupTableId IS NULL

       EXEC FTS.InsertWords @insWords


       /* **************************************************************************************** */
       -- Now do the word usage table
       -- just delete all and insert all
       /* **************************************************************************************** */

       DELETE del
       FROM FTS.WordUsage_RecordRegularColumn del --WITH (UPDLOCK)     -- locks?
       JOIN @words inp ON
           @recordId            = del.FK_InRecord      AND
           inp.IndexedColumn    = del.FK_InColumn      AND
           inp.InSubtableRecord = del.SubTableRecordID
       WHERE inp.LookupTableId IS NULL

       DELETE del
       FROM FTS.WordUsage_RecordLookupColumn del --WITH (UPDLOCK)     -- locks?
       JOIN @words inp ON
           @recordId            = del.FK_InRecord      AND
           inp.IndexedColumn    = del.FK_InColumn      AND
           inp.InSubtableRecord = del.SubTableRecordID
       WHERE inp.LookupTableId IS NOT NULL


       /* **************************************************************************************** */
       -- insert to "RecordRegularColumn" word usage table
       /* **************************************************************************************** */

       INSERT INTO FTS.WordUsage_RecordRegularColumn --WITH (TABLOCK)
           (FK_InRecord, FK_InColumn, SubTableRecordID,
            FK_Word, FK_WordType, WordTypeConfidence,
            StartIndex, EndIndex, FullFieldMatch)
       SELECT
           @recordId, inp.IndexedColumn, inp.InSubtableRecord,
           word.ID, inp.WordType, inp.WordTypeConfidence,
           inp.StartIndex, inp.EndIndex, inp.IsFullMatch
       FROM @words inp
       JOIN FTS.Word word ON (
               (word.StringValue  IS NOT NULL AND word.StringValue  = inp.StringValue ) OR
               (word.DateValue    IS NOT NULL AND word.DateValue    = inp.DateValue   ) OR
               (word.TimeValue    IS NOT NULL AND word.TimeValue    = inp.TimeValue   ) OR
               (word.NumericValue IS NOT NULL AND word.NumericValue = inp.NumericValue)
           ) AND (inp.Unit IS NULL OR word.Unit = inp.Unit)
       WHERE inp.LookupTableId IS NULL AND
           (inp.IsFullMatch = 1 OR (inp.StartIndex IS NOT NULL AND inp.EndIndex IS NOT NULL))  -- one/other required, which base words don't (always) have


       /* **************************************************************************************** */
       -- insert to "RecordLookupColumn" word usage table
       /* **************************************************************************************** */

       INSERT INTO FTS.WordUsage_RecordLookupColumn --WITH (TABLOCK)
           (FK_InRecord, FK_InColumn, SubTableRecordID, FK_LookupItem)
       SELECT
           @recordId, inp.IndexedColumn, inp.InSubtableRecord,
           ltwu.ID
       FROM @words inp
       JOIN FTS.IndexedColumns             col  ON inp.IndexedColumn = col.ID
       JOIN FTS.LookupTableItem            lti  ON inp.LookupTableId = lti.ItemID AND lti.FK_LookupTable = col.FK_LookupTable
       JOIN FTS.WordUsage_LookupTableItems ltwu ON lti.ID = ltwu.FK_LookupTableItem
       WHERE inp.LookupTableId IS NOT NULL

   COMMIT TRANSACTION
END

我以前有一個似乎沒有死鎖的工作版本（但我不確定我測試並發性的徹底程度）。但是我不得不改變我的表結構，從那時起，我一直在努力理解我需要做些什麼來避免死鎖。使用SERIALIZABLE事務隔離級別和UPDLOCKs 似乎可以工作，所以我把它帶到了新版本。

這是我在更改表結構之前所擁有的大致內容：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

   -- this statement twice, just like the current version,
   -- once where inp.BaseWord IS NULL and once where inp.BaseWord IS NOT NULL
   INSERT INTO FTS.Word
   SELECT ...
   FROM @words
   LEFT JOIN FTS.Word WITH (UPDLOCK, HOLDLOCK)

   UPDATE -- BaseWordConfidence
   FROM FTS.Word upd WITH (UPDLOCK, HOLDLOCK)
   INNER JOIN @words
   
   DELETE del
   FROM FTS.WordUsage del WITH (UPDLOCK, HOLDLOCK)
   JOIN @words
   
   INSERT INTO FTS.WordUsage
   SELECT ... 
   FROM @words
   JOIN FTS.Word  -- no lock hint

COMMIT TRANSACTION

最後的想法：

我可以呼叫InsertWords一個事務，並在另一個事務中處理DELETEand INSERT，但刪除和插入必須一起在一個事務中。InsertWords但是 - 我用註釋掉的呼叫對此進行了測試， DELETEandINSERT語句本身仍然導致死鎖。所以我知道必須有更多的東西。

好的，在這些過程中有很多東西要解開，所以我會盡力以易於理解的方式進行佈局。

根據您的查詢編寫方式，它們可能包含許多將產生表掃描並持有長鎖的非 SARGABLE 謂詞。使它們容易相互阻塞和死鎖。您希望使這些交易盡可能短，以免它們妨礙任何其他傳入交易。最好有一個鍵將兩個錶鍊接在一起，而不是多個OR條件。但是，如果您必須使用多個OR條件，請考慮這個…

  1. 您的程序正在接受表類型參數。這些類似於表變數，因為它們通常會產生糟糕的估計並且表現不佳。考慮在過程開始時將內容轉儲到#Temp 表，然後將其連接到其他表。
  2. 對於這樣的謂詞(exist.StringValue IS NOT NULL AND exist.StringValue = inp.StringValue )，請改用exist.StringValue = inp.StringValue. 如果任一值為 NULL，則不會返回 True。IS NOT NULL在將其與另一列進行比較之前，無需顯式檢查該列。
  3. 消除您對OR運算符的使用。您可以使用幾個較小的查詢並將它們聯合在一起，或者為每個條件背靠背幾個較小的更新。像您一樣使用OR運算符，SQL Server 不會使用您的索引，這將使您的事務保持打開更長時間。我在我的部落格上討論OR了UPDATES 這裡的使用。更好的是，是否有理由相信所有列都不應該包含來自源的數據？
  4. 確定您是否更有可能擁有INSERTSor UPDATES，然後按照Aaron Bertrand的這篇文章了解處理 的最佳方式，選擇最符合您需要的模式。UPSERTS

總之，對於每個OR可能是INSERTand/or的條件UPDATE，考慮將其轉換為一個簡單的UPSERT語句。專注於小額和空頭交易，然後轉到下一個OR條件，依此類推。這應該允許他們快速移動，並避免阻塞和死鎖。

編輯

下面是一個例子。我會考慮為您正在尋找的每個匹配模式編寫其中一個語句，並查看它的執行方式。通過這種方式，您將每個OR條件分解為自己的事務，該事務應該執行得很快，並且阻塞最少。但同樣，如果您有一個可以映射到傳入表和現有表之間的 Key 列，這將更加容易。

UPSERT

BEGIN TRAN

INSERT INTO FTS.Word (
   StringValue, DateValue, TimeValue, NumericValue, Unit,
   Metaphone, FK_BaseWord, BaseWordConfidence, IsStopWord
)
SELECT inp.StringValue, inp.DateValue, inp.TimeValue, inp.NumericValue, inp.Unit, inp.Metaphone,
   NULL,NULL,
   CASE WHEN EXISTS (
       SELECT 1
       FROM FTS.StopWords
       WHERE Word = inp.StringValue
   )
   THEN 1
   ELSE 0
   END
FROM @words AS inp
WHERE inp.BaseWord IS NULL
   AND NOT EXISTS
   (
       SELECT 1 FROM dbo.Word WITH (UPDLOCK, SERIALIZABLE)
       WHERE Word.Unit = inp.Unit
           AND Word.StringValue = inp.StringValue
   )

UNION

SELECT inp.StringValue, inp.DateValue, inp.TimeValue, inp.NumericValue, inp.Unit, inp.Metaphone,
   base.ID, inp.BaseWordConfidence,
   CASE WHEN EXISTS (
       SELECT 1
       FROM FTS.StopWords
       WHERE Word = inp.StringValue
   )
FROM @words inp
JOIN FTS.Word base
   ON inp.BaseWord = base.StringValue   -- only things w/ a string value have base words
WHERE inp.BaseWord IS NULL
   AND NOT EXISTS
   (
       SELECT 1 FROM dbo.Word WITH (UPDLOCK, SERIALIZABLE)
       WHERE Word.Unit = inp.Unit
           AND Word.StringValue = inp.StringValue
   )

UPDATE upd
SET upd.BaseWordConfidence = inp.BaseWordConfidence
FROM FTS.Word upd
INNER JOIN @words AS inp
   ON upd.StringValue = inp.StringValue
       AND upd.Unit = inp.Unit
WHERE upd.BaseWordConfidence < inp.BaseWordConfidence

COMMIT TRAN

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

相關問答

Sql-Server

並發批量更新觸發鍵鎖

  • May 21, 2021
檢視問答
Sql-Server

死鎖圖 - 倖存語句 inputbuf 不顯示被鎖定的對象

  • October 14, 2019
檢視問答
Sql-Server

刪除查詢會阻塞其他並發事務

  • July 4, 2019
檢視問答
Sql-Server

在並發呼叫上使用 CTE 和 OUTPUT 塊進行更新

  • April 17, 2018
檢視問答
Sql-Server

嘗試執行遠端過程時，我需要哪些配置來修復“無法開始分佈式事務”錯誤？

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

SQL 死鎖 - 同時呼叫的相同儲存過程

  • March 16, 2022
檢視問答