My first article on using foreign key constraints in databases included the claim that using them slows down an application. This article addresses the claim that a Foreign Key Constraint use locks and can cause Deadlocks and thus slow down applications.

Claim

Foreign key constraints can lead to lock contention or timeouts in a busy system. When a record in a referenced table is updated or deleted, the RDBMS must wait for any locks on the corresponding rows in the referencing table to be released to validate the integrity.

Foreign Key Constraint and Locks

A Foreign key constraint must use locks on the reference table when it is checked; surely no one will disagree on this point. But how do locks affect the performance of an RDBMS system?

What kind of Locks must be used?

The core statement that a Foreign Key Constraint slows down a database system by blocking requires a deeper understanding of the locking chain when a foreign key constraint is involved. To explain the locking behavior, I use a simple Foreign Key Constraint between the [demo].[customers] table and [dbo].[nations]. Each customer must be assigned to a nation that is predefined by the [dbo].[nations] table.

To evaluate the locking chain, a new record is entered in the [demo].[customers] table. The [c_nationkey] attribute uses the [dbo].[nations] table to enforce the foreign key relationship.

/* Start a user transaction */
BEGIN TRANSACTION;
GO
	/* Insert a new record int the demo.customers table */
	INSERT INTO demo.customers
	SELECT	*
	FROM	dbo.customers
	WHERE	c_custkey = 1;

	/* We check the lock resource for the lock chaing */
	SELECT %%lockres%% AS locked_resource, * FROM demo.customers;

	/*
		What resources are locked by the transaction?
		Note: The function is an object from the demo database!	
	*/
	SELECT * FROM dbo.get_locking_status(@@SPID);

What is striking is that the table [dbo].[nations] – referenced by the Foreign Key Constraint – is not blocked again after the record has been inserted. Neither a shared lock nor an exclusive lock is on the reference table. At first glance, this strategy seems surprising, because SQL Server must protect the value of the foreign key from changes as long as the transaction is still open!

At this point you will notice how smart SQL Server (like other RDBMS systems) has implemented Foreign Key Constraints! When experienced database developers say that a Foreign Key Constraint hold locks for too long and thus the RDBMS no longer scales under high transactional workloads, this is not true!

Handling of concurrency

If SQL Server only maintains the lock on the reference table for a short period of time, the question arises as to how the database system will find a secure method to maintain the integrity rules of a Foreign Key Constraint . Since the lock on the Foreign Key Constraint is released immediately after the check, a change may have already been made in the reference table by the time a new record is next inserted. Ensuring integrity is implemented cleverly!

How long will the locks be held?

When checking a foreign key constraint, a modern RDBMS system releases the locks on the reference table as soon as the check is complete. This makes the reference table available to other processes. A maintained lock would not be a problem as long as the reference table only needs to be accessed for reading. To enable write processes to access the reference table, locks are released as quickly as possible! A typical scenario for concurrent read/write operations would be an order table that is used as a reference table for the order items!

What impact will these locks have on other processes?

During the brief moment of the SH-Lock, other processes can continue to read the Foreign Key Table. Writing processes have to wait during the action. However, since the reading process immediately releases the resources again, the delay is only marginal!

The process that wants to change a key value in the Foreign Key table must wait for the process that is currently using that Foreign Key in a transaction. Due to the nature of a Foreign Key, when the key value is changed, the detail table must always be checked to see whether this key value is being used. To do this, the process requires a SH-Lock on the individual key values. The UPDATE process waits for this lock.

If the Primary Key is changed in a reference table, this can have an impact. The Primary Key of the reference table is usually the foreign key for the detail table. When a new row is entered in the detail table, the Foreign Key constraint must be checked. If the row with the corresponding key value is itself part of a write operation, the INSERT process in the detail table must wait! Delays can occur!

A Foreign Key Constraint and Deadlocks

The use of a Foreign Key Constraint was also seen critically because of „deadlock scenarios“. Of course, a Foreign Key Constraint can lead to deadlock situations. But… – it is not the foreign key relationship that is the problem, but the way in which business processes are implemented.

Transaction 1 holds an exclusive lock on the new data record to be entered, which receives the value 1 for the c_nationkey. There are NO more locks on dbo.nations!

Transaction 2 wants to delete the row with [n_nationkey] = 1 from the [dbo].[nations] table. The Foreign Key Constraint requires that the RDBMS checks rows in the detail table [demo].[customers] to prevent deletion if customers with [c_nationkey] = 1 exist.

NOTE: Up to this point, there is NO risk of a deadlock because the two transactions are each locking different resources!

Transaction 1 wants to access the table [dbo].[nations] and the row with [n_nationkey] = 1. The stalemate that now occurs cannot be resolved and Microsoft SQL Server terminates the transaction with the lower log volume.

Question: Is this a problem because of the foreign key constraint?

Answer: Yes and No.

I admit, the example will probably never happen like this. But in a high transactional system, it cannot be ruled out that locking conflicts can arise. It is the developer’s job to take these situations into account; They should know the processes better than anyone else! Technically, the Foreign Key Constraint is the cause of the deadlock, but the technical implementation is the real cause.

BEGIN TRANSACTION;
GO
	/* Insert a new record int the demo.customers table */
	SELECT	*
	FROM	dbo.nations WITH (UPDLOCK, HOLDLOCK)
	WHERE	n_nationkey = 1;

	INSERT INTO demo.customers
	SELECT	*
	FROM	dbo.customers
	WHERE	c_custkey = 1;

First, the record in [dbo].[nations] is blocked for updates and then the new record is entered. A second process that wants to change the row in [dbo].[nations] must wait until Transaction 1 has finished.

Conclusion

My personal conclusion is that locks and deadlocks should never be used as an excuse for not implementing foreign key constraints in your database. It must be the developers‘ job to deal intensively with locking behavior and concurrency. Then foreign key constraints can live peacefully with the data in a database until new requirements separate them again :)

Thank you for reading!

• Use Foreign Key Constraints in your Database – Part I
• Foreign Key Constraints – Part II
• Foreign Key Constraints – JOIN Eliminition