PostgreSQL, Foreign Keys and Deadlocks
What happens when you mix foreign key constraints, PostgreSQL, ActiveRecord and cached counters (search for :counter_cache
)? Deadlocks.
My colleague, Mina Naguib, spent some time while away from the office to dig and diagnose some problems. The underlying issue is probably not related to PostgreSQL itself, but cached counters, which Rails makes very easy to do, sometimes result in deadlocks. We’ve seen those backtraces in Hoptoad, but had never really investigated them: they were rare
, meaning nobody complained about them. They were just a thorn in our side.
In a nutshell, this flow is problematic:
- Adding or modifying a record in a table, when:
- That table is a child (using a foreign key contraint) of another table
- and then modifying the parent record in the parent table
- Using transactions
- Concurrently
- Sometimes (race condition)
From Mina’s PostgreSQL transactions wrapping child+parent modifications, deadlocks, and ActiveRecord
Read the full text: it’s well worth it. Unfortunately, Mina doesn’t have a resolution, but he wants more people to be aware of the issue, in the hopes that somebody will find a clean solution.
