Sql-Server

與有條件的 INSERT 和 SELECT 相比，帶有 OUTPUT 的 MERGE 是更好的做法嗎？

  • December 22, 2016

我們經常會遇到“如果不存在，就插入”的情況。Dan Guzman 的部落格對如何使這個程序執行緒安全進行了出色的調查。

我有一個基本表，它只是將字元串從SEQUENCE. 在儲存過程中，我需要獲取值的整數鍵（如果存在），或者INSERT獲取結果值。列上有唯一性約束，dbo.NameLookup.ItemName因此數據完整性沒有風險，但我不想遇到異常。

這不是IDENTITY我無法得到的，在某些情況下SCOPE_IDENTITY價值可能是。NULL

在我的情況下，我只需要處理INSERT桌子上的安全問題，所以我試圖決定這樣使用是否更好MERGE：

SET NOCOUNT, XACT_ABORT ON;

DECLARE @vValueId INT 
DECLARE @inserted AS TABLE (Id INT NOT NULL)

MERGE 
   dbo.NameLookup WITH (HOLDLOCK) AS f 
USING 
   (SELECT @vName AS val WHERE @vName IS NOT NULL AND LEN(@vName) > 0) AS new_item
       ON f.ItemName= new_item.val
WHEN MATCHED THEN
   UPDATE SET @vValueId = f.Id
WHEN NOT MATCHED BY TARGET THEN
   INSERT
     (ItemName)
   VALUES
     (@vName)
OUTPUT inserted.Id AS Id INTO @inserted;
SELECT @vValueId = s.Id FROM @inserted AS s

我可以在不使用MERGE條件的情況下執行此操作，INSERT然後使用SELECT 我認為第二種方法對讀者來說更清楚，但我不相信這是“更好”的做法

SET NOCOUNT, XACT_ABORT ON;

INSERT INTO 
   dbo.NameLookup (ItemName)
SELECT
   @vName
WHERE
   NOT EXISTS (SELECT * FROM dbo.NameLookup AS t WHERE @vName IS NOT NULL AND LEN(@vName) > 0 AND t.ItemName = @vName)

DECLARE @vValueId int;
SELECT @vValueId = i.Id FROM dbo.NameLookup AS i WHERE i.ItemName = @vName

或者也許還有另一種我沒有考慮過的更好的方法

我確實搜尋並參考了其他問題。這個：https ://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice是我能找到的最合適的，但似乎不太適用於我的案例。IF NOT EXISTS() THEN我認為不可接受的方法的其他問題。

因為您使用的是序列，所以您可以使用相同的NEXT VALUE FOR函式——您已經在主鍵欄位的預設約束中擁有該函式——提前Id生成一個新值。Id首先生成值意味著您不必擔心沒有SCOPE_IDENTITY，這意味著您不需要該OUTPUT子句或執行附加SELECT操作來獲取新值；在你做之前你將擁有價值，INSERT你甚至不需要搞砸SET IDENTITY INSERT ON / OFF:-)

所以這需要照顧整體情況的一部分。另一部分是同時處理兩個程序的並發問題，沒有找到完全相同的字元串的現有行，並繼續處理INSERT. 問題在於避免可能發生的唯一約束違規。

處理這些類型的並發問題的一種方法是強制此特定操作為單執行緒。做到這一點的方法是使用應用程序鎖（跨會話工作）。雖然有效，但對於這種碰撞頻率可能相當低的情況，它們可能會有點笨拙。

處理衝突的另一種方法是接受它們有時會發生並處理它們而不是試圖避免它們。使用該TRY...CATCH構造，您可以有效地擷取特定錯誤（在這種情況下：“唯一約束違規”，Msg 2601）並重新執行SELECT以獲取該Id值，因為我們知道它現在存在是由於CATCH與該特定的塊中錯誤。其他錯誤可以以典型RAISERROR/RETURN或THROW方式處理。

測試設置：序列、表格和唯一索引

USE [tempdb];

CREATE SEQUENCE dbo.MagicNumber
 AS INT
 START WITH 1
 INCREMENT BY 1;

CREATE TABLE dbo.NameLookup
(
 [Id] INT NOT NULL
        CONSTRAINT [PK_NameLookup] PRIMARY KEY CLUSTERED
       CONSTRAINT [DF_NameLookup_Id] DEFAULT (NEXT VALUE FOR dbo.MagicNumber),
 [ItemName] NVARCHAR(50) NOT NULL         
);

CREATE UNIQUE NONCLUSTERED INDEX [UIX_NameLookup_ItemName]
 ON dbo.NameLookup ([ItemName]);
GO

測試設置：儲存過程

CREATE PROCEDURE dbo.GetOrInsertName
(
 @SomeName NVARCHAR(50),
 @ID INT OUTPUT,
 @TestRaceCondition BIT = 0
)
AS
SET NOCOUNT ON;

BEGIN TRY
 SELECT @ID = nl.[Id]
 FROM   dbo.NameLookup nl
 WHERE  nl.[ItemName] = @SomeName
 AND    @TestRaceCondition = 0;

 IF (@ID IS NULL)
 BEGIN
   SET @ID = NEXT VALUE FOR dbo.MagicNumber;

   INSERT INTO dbo.NameLookup ([Id], [ItemName])
   VALUES (@ID, @SomeName);
 END;
END TRY
BEGIN CATCH
 IF (ERROR_NUMBER() = 2601) -- "Cannot insert duplicate key row in object"
 BEGIN
   SELECT @ID = nl.[Id]
   FROM   dbo.NameLookup nl
   WHERE  nl.[ItemName] = @SomeName;
 END;
 ELSE
 BEGIN
   ;THROW; -- SQL Server 2012 or newer
   /*
   DECLARE @ErrorNumber INT = ERROR_NUMBER(),
           @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

   RAISERROR(N'Msg %d: %s', 16, 1, @ErrorNumber, @ErrorMessage);
   RETURN;
   */
 END;

END CATCH;
GO

考試

DECLARE @ItemID INT;
EXEC dbo.GetOrInsertName
 @SomeName = N'test1',
 @ID = @ItemID OUTPUT;
SELECT @ItemID AS [ItemID];
GO

DECLARE @ItemID INT;
EXEC dbo.GetOrInsertName
 @SomeName = N'test1',
 @ID = @ItemID OUTPUT,
 @TestRaceCondition = 1;
SELECT @ItemID AS [ItemID];
GO

來自OP的問題

為什麼這比MERGE? TRY如果不使用該WHERE NOT EXISTS子句，我不會獲得相同的功能嗎？

MERGE有各種“問題”（​​@SqlZim 的答案中連結了幾個參考資料，因此無需在此處複製該資訊）。而且，這種方法沒有額外的鎖定（爭用較少），因此在並發方面應該更好。在這種方法中，您將永遠不會遇到唯一約束違規，所有這些都沒有任何HOLDLOCK等。它幾乎可以保證工作。

這種方法背後的原因是：

  1. 如果你有足夠多的執行這個過程以至於你需要擔心衝突，那麼你不想：

  2. 採取不必要的措施

  3. 鎖定任何資源的時間超過必要的時間

  4. 由於衝突只會發生在新條目上（新條目同時送出），因此首先落入CATCH區塊的頻率將非常低。優化執行時間為 99% 的程式碼而不是執行時間為 1% 的程式碼更有意義（除非優化兩者都沒有成本，但這裡不是這種情況）。

來自@SqlZim 的回答的評論（強調添加）

我個人更喜歡嘗試定制解決方案，以避免在可能的情況下這樣做。在這種情況下，我不覺得使用 from 的鎖serializable是一種笨拙的方法，我相信它可以很好地處理高並發。

如果將第一句話修改為“和_當謹慎時”，我會同意。僅僅因為某事在技術上是可行的並不意味著該情況（即預期的案例）會從中受益。

我用這種方法看到的問題是它鎖定的比建議的要多。重新閱讀有關“可序列化”的引用文件很重要，特別是以下內容（強調添加）：

  • 在目前事務完成之前，其他事務不能插入鍵值落在目前事務中的任何語句讀取的鍵範圍內的新行。

現在，這裡是範常式式碼中的註釋：

SELECT [Id]
FROM   dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */

那裡的操作詞是“範圍”。被鎖定的不僅僅是 中的值@vName，更準確地說，是從 開始的範圍這個新值應該去的位置（即在新值適合的任一側的現有鍵值之間），但不是值本身。這意味著，其他程序將被阻止插入新值，具體取決於目前正在查找的值。如果查找是在範圍的頂部進行的，那麼插入任何可能佔據相同位置的東西都將被阻止。例如，如果值“a”、“b”和“d”存在，那麼如果一個程序正在對“f”執行 SELECT，那麼將無法插入值“g”甚至“e”（因為其中任何一個都會在“d”之後立即出現）。但是，插入“c”值是可能的，因為它不會放在“保留”範圍內。

以下範例應說明此行為：

（在查詢選項卡（即會話）#1）

INSERT INTO dbo.NameLookup ([ItemName]) VALUES (N'test5');

BEGIN TRAN;

SELECT [Id]
FROM   dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */
WHERE  ItemName = N'test8';

--ROLLBACK;

（在查詢選項卡（即會話）#2 中）

EXEC dbo.NameLookup_getset_byName @vName = N'test4';
-- works just fine

EXEC dbo.NameLookup_getset_byName @vName = N'test9';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

EXEC dbo.NameLookup_getset_byName @vName = N'test7';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

EXEC dbo.NameLookup_getset_byName @vName = N's';
-- works just fine

EXEC dbo.NameLookup_getset_byName @vName = N'u';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

同樣，如果值“C”存在，並且值“A”被選中（並因此被鎖定），那麼您可以插入值“D”，但不能插入值“B”：

（在查詢選項卡（即會話）#1）

INSERT INTO dbo.NameLookup ([ItemName]) VALUES (N'testC');

BEGIN TRAN

SELECT [Id]
FROM   dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */
WHERE  ItemName = N'testA';

--ROLLBACK;

（在查詢選項卡（即會話）#2 中）

EXEC dbo.NameLookup_getset_byName @vName = N'testD';
-- works just fine

EXEC dbo.NameLookup_getset_byName @vName = N'testB';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

公平地說，在我建議的方法中，當出現異常時，事務日誌中將有 4 個條目在這種“可序列化事務”方法中不會發生。但是，正如我上面所說，如果異常發生的時間為 1%（甚至 5%），那麼與最初的 SELECT 暫時阻塞 INSERT 操作的更可能的情況相比，這影響要小得多。

這種“可序列化事務 + OUTPUT 子句”方法的另一個（儘管很小）問題是該OUTPUT子句（在其目前用法中）將數據作為結果集發回。OUTPUT結果集需要比簡單參數更多的成本（可能在雙方：在 SQL Server 中管理內部游標，在應用層中管理 DataReader 對象） 。鑑於我們只處理單個標量值，並且假設執行頻率很高，結果集的額外成本可能會增加。

雖然該OUTPUT子句可以以返回OUTPUT參數的方式使用，但這需要額外的步驟來創建臨時表或表變數，然後從該臨時表/表變數中選擇值到OUTPUT參數中。

進一步澄清：對@SqlZim 的回應（更新的答案）對我對@SqlZim 的回應（在原始答案中）對我關於並發和性能的聲明的回應；-)

對不起，如果這部分有點長，但在這一點上，我們只是了解這兩種方法的細微差別。

serializable我相信資訊的呈現方式可能會導致人們在原始問題中提出的場景中使用時可能會遇到的鎖定量的錯誤假設。

是的，我承認我有偏見，但公平地說：

  1. 一個人不可能沒有偏見，至少在某種程度上，我確實盡量將其保持在最低限度，
  2. 給出的例子很簡單，但這是為了說明目的，在不過度複雜化的情況下傳達行為。暗示頻率過高並不是有意的，儘管我確實理解我也沒有明確說明其他情況，並且可以將其解讀為暗示比實際存在的問題更大。我將嘗試在下面澄清這一點。
  3. 我還包括一個鎖定兩個現有鍵之間範圍的範例（第二組“查詢選項卡 1”和“查詢選項卡 2”塊）。
  4. 我確實發現（並自願）了我的方法的“隱藏成本”，即每次INSERT由於違反唯一約束而失敗時的四個額外的 Tran Log 條目。我沒有看到任何其他答案/文章中提到的內容。

關於@gbn 的“JFDI”方法，Michael J. Swart 的“Ugly Pragmatism For The Win”文章，以及 Aaron Bertrand 對 Michael 文章的評論（關於他的測試顯示哪些場景降低了性能），以及您對“Michael J 的適應”的評論. 斯圖爾特對@gbn 的 Try Catch JFDI 程序的改編”指出：

如果您更頻繁地插入新值而不是選擇現有值，這可能比@srutzky 的版本更高效。否則我會更喜歡@srutzky 的版本而不是這個版本。

關於與“JFDI”方法相關的 gbn / Michael / Aaron 討論，將我的建議等同於 gbn 的“JFDI”方法是不正確的。由於“獲取或插入”操作的性質，明確需要執行SELECT以獲取ID現有記錄的值。此 SELECT 充當IF EXISTS檢查，這使得這種方法更等同於 Aaron 測試的“CheckTryCatch”變體。Michael 重新編寫的程式碼（以及您對 Michael 的改編的最終改編）還包括WHERE NOT EXISTS首先進行相同的檢查。因此，我的建議（連同邁克爾的最終程式碼和您對他的最終程式碼的改編）實際上不會CATCH經常遇到問題。只能是兩個會話的情況，ItemName``INSERT...SELECT在完全相同的時刻，使得兩個會話在完全相同的時刻收到一個“真” WHERE NOT EXISTS，因此都試圖INSERT在完全相同的時刻做。當沒有其他程序在同一時刻嘗試這樣做時，這種非常具體的情況比選擇現有的ItemName或插入新的要少得多。ItemName

考慮到以上所有因素：為什麼我更喜歡我的方法？

首先，讓我們看看在“可序列化”方法中發生了什麼鎖定。如上所述，被鎖定的“範圍”取決於新鍵值適合的任一側的現有鍵值。如果該方向上沒有現有的鍵值，則範圍的開始或結束也可以分別是索引的開始或結束。假設我們有以下索引和鍵（^表示索引的開頭，表示索引$的結尾）：

Range #:    |--- 1 ---|--- 2 ---|--- 3 ---|--- 4 ---|
Key Value:  ^         C         F         J         $

如果會話 55 嘗試插入以下鍵值：

  • A，則範圍 #1（從^到C）被鎖定：會話 56 不能插入 的值B，即使是唯一且有效的（還）。但是會話 56 可以插入D、G和的值M。
  • D，則範圍#2（從C到F）被鎖定：會話 56 無法插入E（尚未）的值。但是會話 56 可以插入A、G和的值M。
  • M，則範圍#4（從J到$）被鎖定：會話 56 無法插入X（尚未）的值。但是會話 56 可以插入A、D和的值G。

隨著更多鍵值的添加，鍵值之間的範圍變得更窄，從而降低了同時插入多個值在同一範圍內爭鬥的機率/頻率。誠然，這不是一個大問題，幸運的是，它似乎是一個實際上隨著時間的推移而減少的問題。

上面描述了我的方法的問題：它僅在兩個會話嘗試同時插入相同的鍵值時發生。在這方面，它歸結為發生機率更高的事情：同時嘗試兩個不同但接近的鍵值，還是同時嘗試相同的鍵值？我想答案在於執行插入的應用程序的結構，但一般來說，我認為更有可能插入恰好共享相同範圍的兩個不同值。但真正知道的唯一方法是在 OPs 系統上測試兩者。

接下來，讓我們考慮兩種情況以及每種方法如何處理它們：

  1. 所有請求都是針對唯一鍵值的：

在這種情況下，CATCH我的建議中的塊永遠不會輸入，因此沒有“問題”（​​即 4 個 tran 日誌條目和執行此操作所需的時間）。但是，在“可序列化”方法中，即使所有插入都是唯一的，也總會有一些可能阻塞同一範圍內的其他插入（儘管不會持續很長時間）。 2. 同一時間對同一個鍵值的高頻率請求：

在這種情況下——對於不存在的鍵值的傳入請求而言，唯一性非常低——CATCH我建議中的塊將定期輸入。這樣做的效果是，每個失敗的插入都需要自動回滾並將 4 個條目寫入事務日誌，每次都會對性能造成輕微影響。但是整體操作不應該失敗（至少不是因為這個）。

（以前版本的“更新”方法存在一個問題，使其遭受死鎖。updlock添加了一個提示來解決這個問題，它不再出現死鎖。）但是，在“可序列化”的方法中（即使是更新、優化的版本），操作會死鎖。為什麼？因為該serializable行為僅阻止INSERT已讀取並因此鎖定的範圍內的操作；它不會阻止SELECT在該範圍內的操作。

在這種情況下，這種serializable方法似乎沒有額外的成本，並且性能可能比我建議的要好一些。

與許多/大多數關於性能的討論一樣，由於有很多因素會影響結果，真正了解某事將如何執行的唯一方法是在它將執行的目標環境中進行嘗試。到那時，這將不是意見問題:)。

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

相關問答

Sql-Server

有沒有辦法刪除插入的數據，反之亦然？

  • April 12, 2022
檢視問答
Sql-Server

即使使用非聚集索引和全文索引，SE LECT 查詢也很慢

  • February 21, 2022
檢視問答
Sql-Server

如何使用 TSQL 動態更改數據庫

  • September 22, 2021
檢視問答
Sql-Server

從 SSIS 中的 SP 更新表

  • July 6, 2021
檢視問答
Sql-Server

選擇兩個方向城市之間的距離

  • April 13, 2021
檢視問答
Sql-Server

我可以在 WHEN MATCHED 條件下簡化這個 MERGE 語句嗎？

  • November 5, 2020
檢視問答