snippetsqlModerate
How to Resolve deadlock on concurrent MERGE on two tables
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
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
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
goOn 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
Or just fix it like this:
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.