PostgreSQL has an internal mechanism for detecting such a scenario, and it (by default) kicks in 1 second after a blocking lock. If a deadlock is detected, one of the parties receives an error and their transaction is rolled back.

In our case though, we’re not acquiring any obvious locks (explicitly with an SQL LOCK command, or select.. FOR UPDATE). So what gives ?

It turns out that, when tables are referencing each other using foreign keys, PostgreSQL will implicitly acquire a row lock on the referenced row in the secondary (parents) table to protect the atomicity of the entire operation. Therefore, in the above example, both step 2 and step 4 acquire a shared lock on the parents row id 1. Step 5 then blocks on the lock held by step 4. Step 6 causes the deadlock, which aborts session 2’s transaction. This releases the shared lock and allows step 5 to proceed.

This has been a long-standing issue in the database and the PostgreSQL hackers refer to that type of thing as “user-hostile”. Recently, I ran into this issue and through searching saw it was recently re-ignited in August by Joel Jacobson. The discussion had some excellent ideas but it went dormant after a while.

From ActiveRecord’s perspective

All clients to PostgreSQL being equal, Rails/ActiveRecord is at a disadvantage with regards to this bug, mainly because:

It’s a web framework - where the usual use case is concurrent access

A popular little feature called “cached counters”

The ease of extending an ActiveRecord model with callbacks, touching the parent for various reasons

The abstraction between the developer and the raw SQL calls, which, next to lowering the barrier of entry, makes isolating and investigating such a problem more difficult

This problem may manifest itself in Rails with an error whose stack trace includes something like:

The viability of all of the above depends on the application, however fixing the issue in PostgreSQL isn’t out of the question. As a matter of fact, it’s already begun! You can see progress here, here and here

How you can help

With a monetary contribution, small or large.

The above work was commissionned after the August discussion on the pgsql-hackers mailing list, however the funds for it have only been minimally raised and they’re still a few thousand dollars short.

If you or your employer would like to see this fix come to fruition, please visit the donation page at: