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

我正在表 A 上編寫數據庫觸發器。在所述觸發器中，我通過執行以下操作將新記錄插入表 B：

1. 查找表 B 的 PK ID
2. 增加 PK ID 並將新行插入表 B

（注意：表 B 不使用身份。也就是說，PK ID 不是自動遞增的。這不是我可以更改的。原因是數據庫架構是供應商控制的。他們不使用身份在任何他們的表（自動遞增）。供應商在插入記錄時跟踪下一個 ID。這就是他們設計應用程序的方式。我希望與他們的設計實踐保持一致，因為偏離路線並向表添加標識可能會破壞在更新檔或升級週期內。）

我想在交易過程中確保兩件事：

1. 避免表 B 上的死鎖
2. 防止其他插入查詢/事務插入表 B（PK ID 應保持一致）

請記住，在我的案例中，PK ID 不是整數而是 varchar；它是一個字母數字值，前綴為 3 個字母，後跟多個數字。

這是我的程式碼：

CREATE TRIGGER [dbo].[AfterINSERT_TableA_Trigger]
  ON  [dbo].[TableA]
  AFTER INSERT
AS 
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   -- [Table A code goes here]

   -- Table B code

   -- Instruct SQL Server to rollback entire transaction and abort the batch when a run-time error occurs
   SET XACT_ABORT ON;

   BEGIN TRANSACTION TableB
   
   -- Ex: SUB1234567890
   DECLARE @subID VARCHAR(15)

   DECLARE @newSubID VARCHAR(15)

   -- Lookup last PK ID inserted into Table B
   SELECT
       @subID = submissionID
   FROM
       TableB WITH (TABLOCKX, HOLDLOCK)
   ORDER BY
       submissionID DESC

   -- Increment PK ID, separating prefix, increment by 1, and concatenate
  SELECT @newSubID = LEFT(@subID, 3) + CAST((RIGHT(@subID, LEN(@subID) - 3) + 1) AS VARCHAR(15))

  -- Insert new record into TableB
  INSERT TableB WITH (TABLOCKX, HOLDLOCK)
          (SubmissionID, Description, Status)
  VALUES
          (@newSubId, 'Test', 0)

  COMMIT TRANSACTION TableB

   -- Turn off rollback flag
   SET XACT_ABORT OFF;
END

我讀到 HOLDLOCK 相當於 SERIALIZABLE （https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15），所以我’我不確定我使用哪一個會有所不同。

當我的事務執行時，程式碼是否看起來不錯，從而滿足避免死鎖和阻塞表 B 上的其他插入事務的標準？SELECT 和 INSERT 語句是否都需要 WITH 鎖？我應該在事務級別使用 Read Committed 還是類似的東西來代替或補充？

需要明確的是，我不希望表上的其他插入事務必然失敗。我只是希望他們等到我的交易完成，同時保持 PK ID 列一致。

您的觸發器中有許多主要缺陷：

• 它沒有考慮inserted表中有多個（或零個）行。
• XACT_ABORT無論如何總是ON處於觸發器中，我看不出有理由嘗試將其關閉。
• BEGIN TRAN \ COMMIT也是浪費時間，因為觸發器在同一個事務中執行。
• 你需要HOLDLOCK, UPDLOCK保持對錶的鎖定直到事務結束，TABLOCK是沒有必要的。
• 我不知道編號系統背後的確切邏輯，您可能需要調整它，但ROW_NUMBER在這裡是必要的，因為您有多行。

CREATE TRIGGER [dbo].[AfterINSERT_TableA_Trigger]
  ON  [dbo].[TableA]
  AFTER INSERT
AS 

   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   IF (NOT EXISTS (SELECT 1 FROM inserted))
       RETURN;  -- early bail-out

   INSERT TableB
       (SubmissionID, Description, Status)
   SELECT
       CONCAT(
           LEFT(b.submissionID, 3),
           CAST(RIGHT(b.submissionID, LEN(b.submissionID) - 3) AS int) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)
       ),
       'Test',
       0
   FROM inserted i
   CROSS APPLY (
       SELECT submissionID
       FROM TableB WITH (HOLDLOCK, UPDLOCK)
       ORDER BY submissionID DESC
   ) b;

GO

您應該為此使用序列NEXT VALUE FOR，並使用或sys.sp_sequence_get_range獲取下一個值，以便為多行插入保留一系列值。

序列只提供一個數值，但您可以自己輕鬆地為字元程式碼添加前綴。

另一種選擇是帶有包含前綴的計算列的標識列，但您已經說過您不希望走那條路。

序列和標識列的優點是它們由系統維護而不參與目前的使用者事務。使用替代程式碼來避免阻塞和死鎖是極其困難的。

如果您真的想避免此類問題的所有內置解決方案，另一種強大的替代方法是手動序列，正如我在我的一篇關於序列表的舊文章中描述的那樣。

聽起來供應商正在對他們的其他表使用這種方法，但如果他們正確實施了它，我會感到非常驚訝。我個人從未見過它在供應商程式碼中正確完成。也許你是第一個。

無論如何，如果你真的想遵循他們的模式，你應該更仔細地研究一下。也就是說，您可能應該就您的要求與他們聯繫，而不是自己進行更改，具體取決於您與他們的許可協議。

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