How to Resolve deadlock on concurrent MERGE on two tables

Question

Recently I have been encountering deadlocks occasionally when I try to insert data concurrently on two tables.

Following is the table structure

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

On a single transaction I am trying to complete data insertion on tableA and Table B by calling respective stored procedure (SaveTableA and SaveTableB). SaveTableA is same as SaveTableB.

.

Database has read committed snapshot isolation enabled. wondering how deadlock occurs on insertion? From the above graph I infer that the SaveTableB from one thread is victim while other instance of same procedure on different thread was holding lock on primary key of TableA.

From this reference I understand that the lock here is on the Btree of the Index based on the reference https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx and the deadlock graph above. While the locked resource is on the clustered index BTree node itself,

From the above inference I am curious to understand following

1. Does Insertion operation always holds lock at BTree level as seen the graph?
2. Trying to understand why there is a lock on the clustered index of TableA when the statements involved in the deadlock are SaveTableB procedure from two different transactions?
3. Since I am using the Merge statement here, is it the assumption that the btree lock on insert is hold on till the transaction ends or till the Merge statement ends?

Curious to understand that if it is possible to get deadlocks in this kind of scenario mentioned, trying to understand on how to approach it so that I will prepare myself to solve it. Thanks for helping me learn about this.

Answer 1

Concurrent MERGE statements will deadlock or produce PK violations by default, as the "scan" phase of the MERGE is performed without a restrictive lock. You need to add a lock hint for this to work. See Why is TSQL MERGE failing with a Primary Key Violation? Isn’t it atomic? by David Browne for MERGE locking details.

Or just fix it like this:

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);

Answer 2

If running two querys that is in the opposite order can cause deadlock. Your problem is a classic deadlock example.

What will happen.

• Transaction 1 will lock TableA (SaveTableA)
• Transaction 2 will lock TableB (SaveTableB)
• Transaction 1 waits for Lock on TableB to be release to do the update/insert
• Transaction 2 waits for Lock on TableA to be released to do the update/insert

So to resolve your problem, dont run them at same time or update the logic to do something else.

If you in one of the procs add a Select statement to force a PAGE and UPDATE LOCK before doing the initial select/scan or use SERIALIZABLE. So in SaveTableB add something like this:

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

Or

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