3

We recently discovered we have issues in our application with deadlocks where we the developers didn't think they could occur.

In my quest to further understand this I set out to create as basic of a test scenario as I could imagine. It resulted in two tables, one parent and one child.

In this simple scenario I launch two instances of Management Studio and execute the SAME query at (almost, as quick as I can switch window) the same time in both instances. Pretty soon one of them ends with a deadlock.

I've read about different approaches and have tried enabling SNAPSHOT isolation level but that did not resolve much of anything and the deadlock remains.

I question how inserts can result in deadlocks and hope to gain some insight in why and hopefully even provide a way around it to resolve the issue.

First here is the simple table layout:

-- (Optional code to drop the tables and sequences) 
drop table ChildTable
go
drop table ParentTable
go

drop sequence Seq_ParentSequence 
drop sequence Seq_ChildSequence

create sequence Seq_ParentSequence as bigint start with 1 increment by 1 cache
create sequence Seq_ChildSequence as bigint start with 1 increment by 1 cache



-- Table creation
create table ParentTable (
    ID bigint not null,
    Name nvarchar(100),

    CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED 
    (
        ID ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
)



create table ChildTable (
    ID bigint not null,
    ParentID bigint not null,
    Name nvarchar(100),
        CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED 
    (
        ID ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

)


alter table childtable add Constraint FK_ChildTable_ParentTable foreign key (ParentId) references ParentTable (ID) 

CREATE NONCLUSTERED INDEX [IDX_ChildTable_ParentID] ON [dbo].[ChildTable]
(
    [ID] ASC,
    [ParentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Next is the query I use in both instances of management studio to insert the test data. 

BEGIN

    BEGIN TRANSACTION
        ;with cte as (select 1 as IdNumber union all select IdNumber+1 from cte where IdNumber < 1000)
        select next value for Seq_ParentSequence as ID, 'Test' + cast(IdNumber as nvarchar(100)) as Name  into #tmpParent from cte option (MAXRECURSION 1000) 

        ;with cte as (select 1 as IdNumber union all select IdNumber+1 from cte where IdNumber < 100)
        select next value for Seq_ChildSequence  as ID, t.ID as ParentId, 'Test' + cast(IdNumber as nvarchar(100)) + '/' + t.Name as Name  into #tmpChild  from cte c, #tmpParent t

        insert into ParentTable (ID, Name) 
        select * from #tmpParent

        -- This will cause a dead lock if two users execute this same query at the same time ...
        insert into ChildTable(ID, ParentID, Name)
        select * from #tmpChild


        drop table #tmpParent
        drop table #tmpChild

    COMMIT;

END

One of the clients get the following error:

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

And here is the trace from SQL Profiler regarding the deadlock. (This one happened to be when I used snapshot isolation, but having it there or not did not make a difference in my tests)

<deadlock-list>
 <deadlock victim="process268532188">
  <process-list>
   <process id="process268532188" taskpriority="0" logused="354112" waitresource="OBJECT: 27:437576597:0 " waittime="3051" ownerId="37934645" transactionname="user_transaction" lasttranstarted="2017-06-02T11:10:09.640" XDES="0x147ca2040" lockMode="IX" schedulerid="1" kpid="1748" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-02T11:10:09.627" lastbatchcompleted="2017-06-02T11:09:13.650" lastattention="1900-01-01T00:00:00.650" clientapp="Microsoft SQL Server Management Studio - Query" hostname="AF-6L64K32" hostpid="8056" loginname="sa" isolationlevel="snapshot (5)" xactid="37934645" currentdb="27" lockTimeout="4294967295" clientoption1="671090720" clientoption2="390200">
    <executionStack>
     <frame procname="adhoc" line="14" stmtstart="1398" stmtend="1546" sqlhandle="0x020000004b3b5d03b307699ab43002ca065170d084c4ba3b0000000000000000000000000000000000000000">
insert into ChildTable(ID, ParentID, Name)
        select * from #tmpChild     </frame>
    </executionStack>
    <inputbuf>
BEGIN
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT  
    BEGIN TRANSACTION
        ;with cte as (select 1 as IdNumber union all select IdNumber+1 from cte where IdNumber &lt; 1000)
        select newid() as ID, &apos;Test&apos; + cast(IdNumber as nvarchar(100)) as Name  into #tmpParent from cte option (MAXRECURSION 1000) 

        ;with cte as (select 1 as IdNumber union all select IdNumber+1 from cte where IdNumber &lt; 100)
        select newid() as ID, t.ID as ParentId, &apos;Test&apos; + cast(IdNumber as nvarchar(100)) as Name  into #tmpChild  from cte c, #tmpParent t

        insert into ParentTable (ID, Name) 
        select * from #tmpParent

        -- This will cause a dead lock if two users execute this same query at the same time ...
        insert into ChildTable(ID, ParentID, Name)
        select * from #tmpChild

        drop table #tmpParent
        drop table #tmpChild

    COMMIT;

END
</inputbuf>
   </process>
   <process id="process2065cb868" taskpriority="0" logused="26540956" waitresource="KEY: 27:72057594039304192 (815b539c4fac)" waittime="2387" ownerId="37934179" transactionname="user_transaction" lasttranstarted="2017-06-02T11:10:09.430" XDES="0x1f3989740" lockMode="S" schedulerid="1" kpid="3732" status="suspended" spid="69" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-02T11:10:09.433" lastbatchcompleted="2017-06-02T11:09:56.257" lastattention="1900-01-01T00:00:00.257" clientapp="Microsoft SQL Server Management Studio - Query" hostname="AF-6L64K32" hostpid="29028" loginname="sa" isolationlevel="snapshot (5)" xactid="37934179" currentdb="27" lockTimeout="4294967295" clientoption1="671090720" clientoption2="390200">
    <executionStack>
     <frame procname="adhoc" line="16" stmtstart="1540" stmtend="1688" sqlhandle="0x02000000a9b4ae00517f6148e4fd39beff736b1ea63241b70000000000000000000000000000000000000000">
insert into ChildTable(ID, ParentID, Name)
        select * from #tmpChild     </frame>
    </executionStack>
    <inputbuf>
--ALTER DATABASE TestPlayground  
--SET ALLOW_SNAPSHOT_ISOLATION ON 
BEGIN
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT  
    BEGIN TRANSACTION
        ;with cte as (select 1 as IdNumber union all select IdNumber+1 from cte where IdNumber &lt; 1000)
        select newid() as ID, &apos;Test&apos; + cast(IdNumber as nvarchar(100)) as Name  into #tmpParent from cte option (MAXRECURSION 1000) 

        ;with cte as (select 1 as IdNumber union all select IdNumber+1 from cte where IdNumber &lt; 100)
        select newid() as ID, t.ID as ParentId, &apos;Test&apos; + cast(IdNumber as nvarchar(100)) as Name  into #tmpChild  from cte c, #tmpParent t

        insert into ParentTable (ID, Name) 
        select * from #tmpParent

        -- This will cause a dead lock if two users execute this same query at the same time ...
        insert into ChildTable(ID, ParentID, Name)
        select * from #tmpChild

        drop table #tmpParent
        drop table #tmpChild

    COMMIT;

END    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="437576597" subresource="FULL" dbid="27" objectname="TestPlayground.dbo.ChildTable" id="lock247a04200" mode="X" associatedObjectId="437576597">
    <owner-list>
     <owner id="process2065cb868" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process268532188" mode="IX" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <keylock hobtid="72057594039304192" dbid="27" objectname="TestPlayground.dbo.ParentTable" indexname="PK_ParentTable" id="lock237eeb580" mode="X" associatedObjectId="72057594039304192">
    <owner-list>
     <owner id="process268532188" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2065cb868" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Hopefully I am missing something basic but I can't figure it out. I've tried different indexes, both clustered and non-clustered and read just about any article I have found relating to deadlocks. For instance https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/ gave alot of insight but I could not come up with a solution by reading that.

So again any insight and help on the matter would be much appreciated! Thanks in advance!

Improve this question

1 Answer 1

Reset to default
5

Although row versioning isolation will help avoid readers blocking/deadlocking writers and visa-versa, it won't avoid deadlocks here. Locking is required with data modifications against traditional tables even in the SNAPSHOT isolation level or READ COMMITTED with the READ_COMMITTED_SNAPSHOT database option on.

Looking at the execution plan on my test system, I see a clustered index scan against the parent table during the child table insert (in order to validate the foreign key) due to the large number of rows inserted. Because exclusive locks are held on newly inserted rows until the end of each transaction, concurrent scans of the parent table during the child table insert will block each other and lead to a deadlock.

One way to avoid the deadlock is to add an 'OPTION (LOOP JOIN)' query hint to the child table insert. This will help avoid touching newly inserted rows locked by other sessions, but may reduce performance.

Another way to avoid the deadlock with your mass inserts queries is to acquire a transaction scoped application lock. This will avoid deadlocks by serializing the mass insert queries at the cost of concurrency with other mass insert queries.

Improve this answer
1
  • Thank you for your suggestion! I added "option (loop join)" and it works like a charm. Initial benchmarks doesn't indicate any negative performance which is great! We have also been checking out application locks which may be useful in procedures and may adopt that strategy as well where needed.
    – Xtrophic
    Jun 2, 2017 at 13:00

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.