A Foreign key (FK) is used to establish a referential constraint between two tables, sometimes referred to as parent-child tables or master/detail tables. It indicates that a set of records in the child/detail table should belong to a record in the parent/master table. Normally, it’s a good idea to create an index on foreign key columns. This will help improve the performance of joins (based on the FK column) between parent-child tables. However, there are scenarios in which an un-indexed foreign key column can lead to a deadlock.
Before going into details, let’s have a brief look at some basic locks used in SQL Server. For the purpose of this discussion, we will concentrate on 3 main lock modes:
- Shared (S) – This is used by SQL Server when reading any data (e.g. SELECT). This ensures that other sessions/transactions can read the same data at the same time but they can’t modify it.
- Exclusive (X) – This is used by SQL Server at the time of modifying any data (i.e. any DML operation like UPDATE/DELETE/INSERT). This ensures that no other transaction can read/write that data at the same time.
- Update (U) – This is something that can be thought of as being in between ‘Shared’ and ‘Exclusive’. SQL Server takes this lock to indicate that it is going to update that data. This lock just indicates the desire to update. ‘Exclusive’ lock is obtained at the time of the actual update.
In this article, we will discuss a scenario in which an un-indexed foreign key column can lead to a deadlock. Let’s consider a parent-child table with a foreign key relationship:
CREATE TABLE tbParent
(ParentId INT NOT NULL CONSTRAINT PK_ParentId PRIMARY KEY,
Val1 INT NOT NULL,
Val2 VARCHAR(10) NOT NULL)
CREATE TABLE tbChild
(ChildId INT NOT NULL CONSTRAINT PK_ChildId PRIMARY KEY,
ParentId INT NOT NULL CONSTRAINT FK_ParentId REFERENCES tbParent(ParentId),
CVal1 INT NOT NULL,
CVal2 VARCHAR(10) NOT NULL)
In this example, both the tables have clustered indexes, but the child table does not have a non-clustered index on the FK column (ParentId). We will use a query to display various locks obtained by SQL Server when we try to delete records from these tables:
SELECT
CASE
WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(l.Resource_associated_entity_id)
WHEN resource_type IN('PAGE','KEY','RID') THEN OBJECT_NAME(p.object_id)
ELSE '<not applicable>'
END AS OwnerObjectName
,request_session_id,resource_type,resource_description,request_mode
,request_type,request_status,request_reference_count,resource_associated_entity_id
FROM sys.dm_tran_locks l
LEFT OUTER JOIN sys.partitions p
ON l.resource_associated_entity_id = p.hobt_id
WHERE request_session_id IN(53,54) –- replace 53/54 with actual session Ids
ORDER BY request_session_id,resource_type
Now, let’s run two delete queries on the child and parent tables in different sessions:
DELETE FROM dbo.tbChild
WHERE ParentId = 20
DELETE FROM dbo.tbParent
WHERE ParentId = 20
In this scenario, a deadlock can occur. The absence of an index on the ParentId column in the tbChild table forces the query optimizer to do a table scan to find all the rows that have ParentId=10. This can lead to blocking and a deadlock situation.
To avoid this deadlock, it is recommended to create a non-clustered index on the ParentId column in the tbChild table:
CREATE NONCLUSTERED INDEX [IX_Parent] ON [dbo].[tbChild]([ParentId] ASC)
With the non-clustered index in place, the queries will use index seeks instead of scans, avoiding blocking and potential deadlocks.
In conclusion, creating an index for a foreign key column is not only beneficial for performance but also helps avoid potential deadlocks when deleting or updating records from parent-child tables.