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:

  1. Adding or modifying a record in a table, when:
  2. That table is a child (using a foreign key contraint) of another table
  3. and then modifying the parent record in the parent table
  4. Using transactions
  5. Concurrently
  6. 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.

Search

Your Host

A picture of me

I am François Beausoleil, a Ruby on Rails and Scala developer. During the day, I work on Seevibes, a platform to measure social interactions related to TV shows. At night, I am interested many things. Read my biography.

Top Tags

Books I read and recommend

Links

Projects I work on

Projects I worked on