SQL Server Foreign Key Deadlocks

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

Create table ParentTable
    (Pid int identity, Col1 char(5) 
       CONSTRAINT PK_ParentTable PRIMARY KEY (Pid));
GO
 
Create table ChildTable 
    (Cid int identity Primary Key, 
    Col1 Char(5),Pid int references ParentTable(Pid));
GO

The Scenario

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

-- Transaction T1
BEGIN TRAN
Insert into ParentTable Values('Dummy');
GO 10

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.

select resource_type,
DB_NAME(resource_database_id) as DatabaseName,
resource_description,request_mode,
request_type,
request_status
from
-- change the request_session_id to that of yours
sys.dm_tran_locks where request_session_id=59 
order by resource_type desc

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

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

-- Transaction T2
BEGIN TRAN
Insert into ParentTable Values('Dummy')
GO 10
While 1=1
Insert into ChildTable values('D',1)
Insert into ChildTable values('D',2)
Insert into ChildTable values('D',3)
Insert into ChildTable values('D',4)
Insert into ChildTable values('D',5)

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.

select resource_type,
    DB_NAME(resource_database_id) as DatabaseName,
    object_name(p.object_id) As ObjectName,
    resource_description,request_mode,
    request_type,
    request_status
    from
    -- change the request_session_id to that of yours
    sys.dm_tran_locks tl 
    join sys.partitions p on p.partition_id=tl.resource_associated_entity_id
    where tl.request_session_id=55
    order by request_status desc

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.

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

While 1=1
Insert into ChildTable values ('D',11)
Insert into ChildTable values ('D',12)
Insert into ChildTable values ('D',13)
Insert into ChildTable values ('D',14)
Insert into ChildTable values ('D',15)

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.

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