HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

How to Resolve deadlock on concurrent MERGE on two tables

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tablesmergedeadlocktworesolvehowconcurrent

Problem

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

  • Does Insertion operation always holds lock at BTree level as seen the graph?



  • 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?



  • Since I am using the Merge

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#183409, answer score: 12

Revisions (0)

No revisions yet.