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
- Sometimes (race condition)
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.