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 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.