0

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.

.DeadlockGraph

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.

  • With a question like this you will only get an general answer. To get a more specific answer add your code and the xml from the deadlock event. Read: dba.stackexchange.com/help/how-to-ask – Daniel Björk Aug 14 '17 at 8:09
  • The general answer would be read this: technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx And take appropriate action. – Daniel Björk Aug 14 '17 at 8:13
  • Without seeing your code, we're shooting in the dark. At the very least, please provide table definitions and the code that is doing the inserts. – Colin 't Hart Aug 14 '17 at 9:07
  • I understand that, is it possible to get deadlocks in this kind of scenario mentioned, trying to understand on how to approach it. Thanks for helping me learn this. Thanks for response from @Colin'tHart – user824910 Aug 14 '17 at 11:07
  • 1
    You're not just inserting, as you say in your question. It's a merge: an insert or an update. – Colin 't Hart Aug 14 '17 at 11:44
7

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 https://blogs.msdn.microsoft.com/dbrowne/2013/02/25/why-is-tsql-merge-failing-with-a-primary-key-violation-isnt-it-atomic/ 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);
0

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

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)
  • Wondering to learn why there are locks on insert statement. While I am sure that I am trying to create new record in TableA and TableB in respective transactions. – user824910 Aug 14 '17 at 11:29
  • 1
    @user824910 its a table lock. You cant change anything in the table until the lock is released – Daniel Björk Aug 14 '17 at 11:44
  • In that case, I am trying to understand if there is a way to profile how long this resource was on hold so that thinking if I can work on improving the transaction elapsed time. Thanks for your quick response. – user824910 Aug 14 '17 at 11:53
  • @user824910 Well its lock from the MERGE statement starts until it ends. So about same time as the whole procedure runs. You can profile it with SQL profiler or just add actuall execution plan and SET STATISTICS TIME ON. – Daniel Björk Aug 14 '17 at 11:55
  • on further analyzing the graph, two statements were issued to execute SaveTableB (while one of them is victim) procedure on concurrent threads, hope you could help me understand why there is a lock on PK of TableA? – user824910 Aug 14 '17 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.