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 < 1000)
select newid() 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 newid() as ID, t.ID as ParentId, 'Test' + 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 < 1000)
select newid() 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 newid() as ID, t.ID as ParentId, 'Test' + 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!
Best Answer
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
SNAPSHOTisolation level orREAD COMMITTEDwith theREAD_COMMITTED_SNAPSHOTdatabase 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.