When you define tables with foreign key constraints, SQL Server must check that the foreign key constraints will not be violated during DML operations. If the foreign key columns are not indexed, there are two scenarios that will cause table locks to be placed on the foreign keyed table:
- During any delete operation
- During any update to a primary key column
While an update to a primary key column is an uncommon scenario (and is probably an indicator of bad design), a delete is a common occurrence. In this post I will go into further detail on why this happens.
The Example
In this example, we will use data from the AdventureWorks2012 database. Let’s look at the Sales.SalesPerson and Sales.SalesOrderHeader tables on the PersonID and BusinessEntityID (a PersonID is a subtype of a BusinessUnitEntityID) columns. This relationship is defined in the foreign key constraint:
FK_SalesOrderHeader_SalesPerson_SalesPersonID
This foreign key constraint ensures a record cannot exist in Sales.SalesOrderHeader if a corresponding record does not exist in Sales.SalesPerson. If a record is deleted from Sales.SalesPerson, SQL Server must ensure that there are no records for that SalesPerson in the SalesOrderHeader table (otherwise referential integrity will be compromised). Prior to SQL Server deleting the record, it must perform a check via looking up the record in SalesOrderHeader. Let’s take the following DELETE statement for example:
DELETE Sales.SalesPerson WHERE BusinessEntityID = 275
The execution plan for this is as follows:
As you can see, there is an Index Seek on the SalesOrderHeader table. The index it is seeking on is for SalesPersonID:
In other words, SQL Server is checking if any rows exist in SalesOrderHeader for the SalesPerson about to be deleted. If any rows exist, the delete operation will fail.
In the above example, the foreign keyed column (SalesOrderHeader.SalesPersonID) was indexed appropriately. I’ve seen many scenarios where this wasn’t the case. Many DBA’s only add indexes as needed or based on the access patterns they intend to occur in their database.
For the sake of example, I will drop the index and retry the delete operation.
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_SalesPersonID
You can now see an index scan occurring on PK_SalesOrderHeader. Part of the issue with this is the index scan. A second side effect is locks being placed.
In order to show the effect of this, we will lock a single row in the SalesOrderHeader table by performing an update. At the same time, we will attempt to delete an unrelated row in the SalesPerson table. The result of this will be the delete on SalesPerson hanging.
BEGIN TRANSACTION
UPDATE Sales.SalesOrderHeader SET ModifiedDate = getdate() WHERE SalesOrderID = 43667
This delete will hang until the UPDATE statement commits or rollsback:
DELETE Sales.SalesPerson WHERE BusinessEntityID = 275
Make note, the BusinessEntityID for the record deleted is 277, not 275. Despite these records being unrelated, one statement will block the other.
Viewing the Lock Via DMVs
By running the query the following query, we can get a better picture of why this is occurring:
SELECT dm_tran_locks.request_session_id,
CASE
WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
ELSE OBJECT_NAME(partitions.OBJECT_ID)
END AS ObjectName,
partitions.index_id,
indexes.name AS index_name,
dm_tran_locks.resource_type,
dm_tran_locks.resource_description,
dm_tran_locks.resource_associated_entity_id,
dm_tran_locks.request_mode,
dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
AND resource_database_id = DB_ID()
-- AND request_session_id in(75,77)
ORDER BY resource_associated_entity_id, request_session_id
Below is the result of executing this query during the hang:
The session that is hung is session 75. You can see that it is in a request_status of WAIT. It is waiting to place a shared (S) lock on a key in the index. This is happening because every key in the index is being scanned as a result of the clustered index scan on SalesOrderHeader (we saw this in the execution plan I previously showed). The key in the index has an exclusive (X) lock on it being held by session 77, as a result this is causing session 75 to hang.
It is counterintuitive that deleting from the SalesPerson table by the primary key value would generate lock contention on SalesOrderHeader, but from reviewing the execution plan and the locks held as a result, it is clear what is happening.
Conclusion
It is generally a good practice to index foreign keyed columns even if you don’t intend to access those tables by those columns directly. I’ve seen this scenario cause deadlocks on many occasions, so if you have deadlocks occurring it is good to check the execution plan and see if you have any scans related to this.
Watch us diagnose a query timeout with SQLGrease: