Preventing deadlock on insert with foreign key (MSSQL)

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

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.

StackExchange Database Administrators Q#175260, answer score: 6