如何解決兩個表上的並發 MERGE 死鎖

最近，當我嘗試在兩個表上同時插入數據時，偶爾會遇到死鎖。

以下是表結構

Create Table TableA
(
 Id Bigint not null primary key ,
 FieldA1 nvarchar(50)
)
go

Create Table TableB
(
 Id Bigint not null primary key,
 TableAId Bigint not null constraint FK_TableA Foreign Key (TableAId) References TableB(Id),
 FieldB1 nvarchar(50)
)
go

CREATE type TableBParam as table
(
   Id Bigint,
   TableAId Bigint not null,
   FieldB1 nvarchar(50)
)

--Deadlock was observed on the save query
go
CREATE PROC SaveTableB
(  
@val [dbo].[TableBParam] READONLY  
)  
AS 
BEGIN 
SET NOCOUNT ON;  

MERGE [dbo].[TableB] AS T  
USING (SELECT * FROM @val) AS S  
 ON ( T.Id = S.Id)  
WHEN MATCHED THEN  
   update set FieldB1 = S.FieldB1
WHEN NOT MATCHED THEN 
   insert(TableAId, FieldB1) Values(S.TableAId, S.FieldB1);

END
go

在單個事務中，我試圖通過呼叫各自的儲存過程（SaveTableA 和 SaveTableB）來完成對錶 A 和表 B 的數據插入。SaveTableA 與 SaveTableB 相同。

.

數據庫已啟用讀取送出的快照隔離。想知道插入時如何發生死鎖？從上圖中，我推斷來自一個執行緒的 SaveTableB 是受害者，而不同執行緒上相同過程的其他實例正在鎖定 TableA 的主鍵。

從這個參考我了解到這裡的鎖是基於參考https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx和死鎖圖的索引的 Btree更多。雖然鎖定的資源位於聚集索引 BTree 節點本身上，

從上面的推論我很想了解以下

1. 如圖所示，插入操作是否始終在 BTree 級別保持鎖定？
2. 當死鎖涉及的語句是來自兩個不同事務的 SaveTableB 過程時，試圖理解為什麼 TableA 的聚集索引上有鎖？
3. 由於我在這裡使用 Merge 語句，是假設插入的 btree 鎖定一直保持到事務結束還是直到 Merge 語句結束？

很好奇，如果在提到的這種情況下可能會出現死鎖，請嘗試了解如何處理它，以便我準備好解決它。感謝您幫助我了解這一點。

預設情況下，並發MERGE語句將死鎖或產生 PK 違規，因為執行的“掃描”階段MERGE沒有限制性鎖定。您需要添加鎖定提示才能使其正常工作。請參閱為什麼 TSQL MERGE 因主鍵衝突而失敗？不是原子的嗎？David Browne 提供MERGE鎖定細節。

或者像這樣修復它：

MERGE [dbo].[TableB] with (serializable) AS T  
USING (SELECT * FROM @val) AS S  
 ON ( T.Id = S.Id)  
WHEN MATCHED THEN  
   update set FieldB1 = S.FieldB1
WHEN NOT MATCHED THEN 
   insert(TableAId, FieldB1) Values(S.TableAId, S.FieldB1);

如果執行兩個順序相反的查詢可能會導致死鎖。您的問題是典型的死鎖範例。

會發生什麼。

• 事務 1 將鎖定 TableA (SaveTableA)
• 事務 2 將鎖定 TableB (SaveTableB)
• 事務 1 等待 TableB 上的鎖被釋放以進行更新/插入
• 事務 2 等待 TableA 上的鎖被釋放以執行更新/插入

因此，要解決您的問題，請不要同時執行它們或更新邏輯以執行其他操作。

如果您在其中一個過程中添加一個 Select 語句以在執行初始選擇/掃描或使用 SERIALIZABLE 之前強制執行 PAGE 和 UPDATE LOCK。所以在 SaveTableB 中添加如下內容：

MERGE [dbo].[TableB] AS T  WITH (PAGLOCK,UPDLOCK)

要麼

MERGE [dbo].[TableB] AS T  WITH (SERIALIZABLE)

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