MERGE 是否可以防止死鎖和伺服器阻塞？

我已經開始考慮在MERGE我的應用程序中使用它來處理UPSERT事務。似乎很多 SQL Server 專家都在推薦這種方法。但是，在探索此方法時，我發現了一些MERGE可能導致的常見問題，例如 Aaron Bertrand在使用 SQL Server 的 MERGE 語句時要小心。

我的系統是全州範圍的，我在後端使用 ColdFusion。它是多執行緒的，我們期望對我們的數據庫有大量的請求。這些INSERT/UPDATE事務中的每一個都將處理一個單行事務。這意味著使用者當時正在插入或更新一行。當我呼叫儲存過程時，我在 ColdFusion 中使用帶有回滾的事務。這是否足夠或者在 SQL 中使用它會更好？

這是我MERGE在儲存過程中使用的語句的範例：

CREATE PROCEDURE [dbo].[SaveMaster] 
  @RecordID INT = NULL, -- Auto increment ID
  @Status BIT = NULL,
  @Name VARCHAR(50) = NULL, 
  @Code CHAR(2) = NULL, --Primary Key
  @ActionDt DATETIME = NULL,
  @ActionID UNIQUEIDENTIFIER = NULL    
AS
  MERGE dbo.Master WITH (HOLDLOCK) AS Target
  USING (SELECT @RecordID,@Status,@Name,@Code,@ActionDt,@ActionID) 
  AS Source (RecordID,Status,Name,Code,ActionDt,ActionID)
     ON Target.RecID = Source.RecordID
  WHEN MATCHED THEN
     UPDATE
   SET Target.Status = Source.Status,
       Target.Name = Source.Name,
       Target.Code = Source.Code,
       Target.ActionDt = Source.ActionDt,
       Target.ActionID = Source.ActionID
  WHEN NOT MATCHED THEN
   INSERT(
       Status,Name,Code,ActionDt,ActionID
   )VALUES(
       Source.Status,
       Source.Name,
       Source.Code,
       Source.ActionDt,
       Source.ActionID
   )
OUTPUT inserted.RecID,$action as Action;

你會看到我用它WITH(HOLDLOCK)來防止主鍵違規和死鎖。在我閱讀了我文章中所附的文章後，似乎即使靜止不動HOLDLOCK也會導致死鎖。我不確定這是否也適用於我上面的程式碼。

我使用一個@RecordID參數來檢查該行是否存在，並在此基礎上MERGE執行插入或更新。此列是一個自動遞增的 id，我來回傳遞它以確保我始終更新表中的正確行。這種方法用於我係統中的多個事務。

我想知道這是否足夠好，或者我應該考慮使用Primary Key(Code is PK) 代替？或者可能同時包含RecordID和Code列？這對性能有幫助嗎？

我正在探索選項和最重要的嘗試來防止死鎖和伺服器阻塞，這似乎是 SQL 世界中這些事務的一個大問題。如果有人對我的程式碼有意見或建議，請告訴我。

其他注意事項

我收到了一些回饋說insert和update應該分開，MERGE出於各種原因我應該盡量避免。這是我發現的另一種方法。我想知道這兩個中的任何一個是否是一個好的解決方案，以及主鍵違規或死鎖是否存在任何潛在問題：

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
  update table set ...
  where key = @key
end
else
begin
  insert into table (key, ...)
  values (@key, ...)
end
commit tran
Or

2)

begin tran
  update table with (serializable) set ...
  where key = @key

  if @@rowcount = 0
  begin
     insert into table (key, ...) values (@key,..)
  end
commit tran

我知道我的 SQL Server 2008 需要升級，但遺憾的是我無法控制。

在上面的範例中，您將看到我使用 WITH(HOLDLOCK) 來防止主鍵違規和死鎖。

這是必要的並且（通常）足以防止並發 MERGE 的 PK 違規。死鎖取決於事務中的其他鎖，但如果您有一個簡單的聚集索引鍵鎖用於您的 MERGE，那麼您應該沒問題。

我使用@RecordID 參數來檢查記錄是否存在，並基於該MERGE 將執行插入或更新。這個欄位是我來回傳遞的自動遞增的 id，以確保我總是更新表中的正確行。

這很奇怪，而且可能很糟糕。您應該使用主鍵來“確保始終更新正確的行”。我不明白為什麼你有那個專欄*，*更不用說在你的 MERGE 中使用它了。只用PK。

社區維基回答：

有趣的是，MERGE據報導，與單獨INSERT的UPDATE, 和DELETE語句相比，它的性能更差，更難調整性能。

絕對將單獨的語句保留在儲存過程中，並考慮將它們包裝在事務中（儘管這可能會引入更長的鎖）。避免 ColdFusion 的事務處理；而是信任 SQL Server。

問題中的IF EXISTS方法似乎較差。WHERE開戶檢查的作用與遺囑中的條款相同UPDATE。第二種形式更整潔。您需要TRY/CATCH正確的錯誤和事務處理（快速範例和更全面的方法）。

你總是有可能讓自己陷入僵局。MERGE不會解決這個問題。你不應該在這裡遇到任何 PK 違規，但這就是TRY/CATCH目的。如果兩個事務以某種方式設法在完全相同的納秒內插入完全相同的密鑰，則其中一個將獲勝，您可以使用TRY/CATCH來決定如何處理另一個事務-它們可以重試（在這種情況下它將更新)，或者您可以強制它失敗並確保使用者知道存在衝突並可以採取相應措施。請參閱Daniel Farina為失敗的事務實施 SQL Server 事務重試邏輯。

人們認為阻塞是一件壞事，但這正是它的目的——防止兩個人試圖同時對相同的數據進行操作。如果您擔心避免阻塞（例如編寫器阻塞讀取器，反之亦然），您應該查看行版本控制隔離級別，例如已送出讀的快照隔離 (RCSI) 和快照隔離 (SI)。

另請參閱 Michael Swart 的Be Careful with the Merge Statement和SQL Server UPSERT Patterns and Antipatterns。

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