Deadlock Due to Foreign Key Constraints

Another common deadlock pattern is a deadlock involving foreign key validations. This can happen in any of the following scenarios.

1. An Insert parent-Insert child operation deadlocking with another thread performing the same operation

2. A Delete child-Delete parent operation deadlocking with another thread performing the same operation

3. An Insert parent-Insert child operation deadlocking with a Delete parent-Delete child operation.

SQLCat team published a whitepaper on handling this type of deadlocks. The whitepaper discusses various techniques to avoid this kind of deadlocks. Follow this link to read the whitepaper Eliminating Deadlocks Caused By Foreign Keys with Large Transactions

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: