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