Blocking on Foreign Keys in SQL Server

We have seen that this is one area that people tend to neglect when it comes to blocking locks.  Let’s illustrate this using an example:

Let’s consider 2 tables TABLEA and TABLEB and that there is a foreign key relationship between the two as defined below:

Create table TableA (Col1 int, Col2 varchar(10),

constraint PK_TABLEA primary key (Col1))
GO
Create table TableB (Col1 int, Col2 int, Col3 varchar(10),

constraint PK_TABLEB primary key (Col1, Col2))
GO
Alter table TableB Add constraint FK_TableB_To_TableA foreign key (Col1) references TableA (Col1)
GO
Begin Tran
Insert into TableA (Col1, Col2) values (1, ‘Test’)
Insert into TableA (Col1, Col2) values (2, ‘Test2’)
Insert into TableA (Col1, Col2) values (3, ‘Test3’)
Insert into TableA (Col1, Col2) values (4, ‘Test4’)
Insert into TableB (Col1, Col2, Col3) values (1, 2, ‘BTest’)
Commit tran

Now, suppose that there are two different sessions and the activity is listed in the following table:

Session 1	Session2
Begin Tran Update TableA    Set Col2 = ‘Test-A’  Where Col1 = 2	Set Lock_Timeout 5000 –- 5 seconds Begin Tran Insert into TableB(col1, col2, col3)  Values (2, 3, ‘TestB’)

Session 2 will hang and will time-out returning the 1222 (lock timeout error) error after 5 seconds.   What occurs behind the scenes is that internally, SQL Server fires off a select statement  on TableA when the insert is done on TableB because it has to validate the foreign key and because that record is locked in Session 1, it results into the classic writers blocking readers scenario but an implicit case.  Also, remember that for Session 2, you need to trap that error and rollback/commit that transaction in order to release the locks else the locks will remain – you can check that very easily using sp_lock or other locking troubleshooting scripts.

One way to work around this issue is to define the primary key on the parent table (TableA) as a non-clustered index.  Once you do that, the issue won’t occur since the look-up will occur against the non-clustered index and that won’t be in a locked state since the PK column is not being modified.

This situation becomes even worse when the foreign key columns are not indexed because internal look-ups to validate the foreign key can then lead to full scans.  Actually, un-indexed foreign keys affect inserts, updates as well as deletes.  It is always a very good idea to index your foreign keys with the exception of look-up tables (less number of records) since over there doing a scan would be much faster than maintaining the index.