合併語句本身死鎖

我有以下過程（SQL Server 2008 R2）：

create procedure usp_SaveCompanyUserData
   @companyId bigint,
   @userId bigint,
   @dataTable tt_CoUserdata readonly
as
begin

   set nocount, xact_abort on;

   merge CompanyUser with (holdlock) as r
   using (
       select 
           @companyId as CompanyId, 
           @userId as UserId, 
           MyKey, 
           MyValue
       from @dataTable) as newData
   on r.CompanyId = newData.CompanyId
       and r.UserId = newData.UserId
       and r.MyKey = newData.MyKey
   when not matched then
       insert (CompanyId, UserId, MyKey, MyValue) values
       (@companyId, @userId, newData.MyKey, newData.MyValue);

end;

CompanyId、UserId、MyKey 構成目標表的複合鍵。CompanyId 是父表的外鍵。此外，還有一個非聚集索引CompanyId asc, UserId asc。

它是從許多不同的執行緒呼叫的，並且我一直在呼叫同一語句的不同程序之間遇到死鎖。我的理解是“with (holdlock)”對於防止插入/更新競爭條件錯誤是必要的。

我假設兩個不同的執行緒在驗證約束時以不同的順序鎖定行（或頁面），因此是死鎖。

這是一個正確的假設嗎？

解決這種情況的最佳方法是什麼（即沒有死鎖，對多執行緒性能的影響最小）？

（如果您在新選項卡中查看圖像，它是可讀的。對不起，小尺寸。）

• @datatable 中最多有 28 行。
• 我已經追溯了程式碼，我看不到我們在這裡開始交易的任何地方。
• 外鍵設置為僅在刪除時級聯，並且沒有從父表中刪除。

好的，在查看了幾次之後，我認為您的基本假設是正確的。這裡可能發生的是：

1. MERGE 的 MATCH 部分檢查索引是否匹配，並在執行時對這些行/頁進行讀鎖定。
2. 當它有一行沒有匹配時，它會首先嘗試插入新的索引行，所以它會請求一個行/頁寫鎖……

但是，如果另一個使用者也在同一行/頁面上執行了第 1 步，那麼第一個使用者將被阻止更新，並且…

如果第二個使用者也需要在同一頁面上插入，那麼他們就陷入了僵局。

AFAIK，只有一種（簡單）方法可以 100% 確保您不會在此過程中陷入死鎖，那就是向 MERGE 添加 TABLOCKX 提示，但這可能會對性能產生非常糟糕的影響。

添加TABLOCK提示可能足以解決問題，而不會對您的性能產生很大影響。

最後，您還可以嘗試添加 PAGLOCK、XLOCK 或同時添加 PAGLOCK 和 XLOCK。同樣，這可能會起作用，性能可能不會太糟糕。你得試一試才能看到。

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