Recently I came across an interesting SQL Server Foreign Key Deadlock scenario I wasn’t aware off. I didn’t have idea that foreign key constraint can also result in deadlocks.  The detailed steps to replicate the deadlocks are mentioned below.

Test Tables

The Scenario

Step 1: Open a new query window and execute below query under an explicit Transaction say T1.

The transaction T1 inserts 10 rows in ParentTable and takes appropriate locks on the ParentTable. To check what all locks it acquires, execute the below query.

The transaction T1 takes IX locks at Object and Page level and X locks at key level as shown below.

1_SQL_Server_Foreign_Key_Deadlocks

Step 2: Open a new query window and execute below query under another explicit Transaction say T2.

The transaction T2 inserts 10 rows into ParentTable and then inserts multiple rows into ChildTable. To check what all it acquires execute the below query.

The transaction T2 takes IX lock at Page level on ParentTable and ChildTable and X locks on relevant rows. It also waits to acquire S lock on ParentTable, Why? This is because as it inserts rows into ChildTable it scans ParentTable to verify referential integrity. However, it has to wait to get S lock on rows inserted by transaction T1 in ParentTable in step1.

2_SQL_Server_Foreign_Key_Deadlocks

Step 3: Execute the below query in transaction T1. Make sure that you select and execute below query only.

The above query inserts records in ChildTable.  One of the transactions will be chosen as a deadlock victim and will terminate with below error

Msg 1205, Level 13, State 51, Line 2

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

Rerun the transaction.

When transaction T1 inserts rows into ChildTable similar to transaction T2 it also scans the rows in ParentTable to verify referential integrity. In doing so, it waits on rows inserted into ParentTable by Transaction T2. This results in a deadlock situation.

I leave it as an exercise for you all to figure out work around for this kind of deadlocks, until I spill the beans in my next blog.

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook