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 ,
Create Table TableB
Id Bigint not null primary key,
TableAId Bigint not null constraint FK_TableA Foreign Key (TableAId) References TableB(Id),
CREATE type TableBParam as table
TableAId Bigint not null,
--Deadlock was observed on the save query
CREATE PROC SaveTableB
@val [dbo].[TableBParam] READONLY
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);
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 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.