← All posts
Deep dive #postgres#concurrency#locking#typeorm

Locking a parent row FOR UPDATE deadlocks against inserts of its children

Postgres takes a hidden FOR KEY SHARE lock on the parent whenever you insert a row whose foreign key points at it. FOR UPDATE on that parent conflicts with it, so "lock the parent first" quietly turns every child insert into a lock wait — and two transactions that each hold one side deadlock. FOR NO KEY UPDATE keeps the serialisation you wanted and lets the FK checks through.

I standardised a lock order — “always lock the parent row, then the child rows” — to stop two writers interleaving on shared counters. It removed one deadlock and created a new, more common one.

What happens on an insert

When you insert a row with a foreign key, Postgres checks the referenced parent still exists by taking a FOR KEY SHARE lock on that parent row. You never write it; the FK trigger does.

FOR UPDATE conflicts with FOR KEY SHARE. So:

  1. Transaction A locks a bucket row, then inserts a child row whose FK points at the parent. The insert needs FOR KEY SHARE on the parent.
  2. Transaction B locked the parent FOR UPDATE (the new “parent first” rule) and is now waiting for the bucket row A holds.

A waits for B’s parent lock, B waits for A’s bucket lock: deadlock. The one Postgres aborts is whichever waited first — in my case, often the request that had just captured a payment.

The fix

Lock the parent FOR NO KEY UPDATE instead:

SELECT 1 FROM parents WHERE id = $1 FOR NO KEY UPDATE;
await manager.findOne(Parent, { where: { id }, lock: { mode: 'for_no_key_update' } });

FOR NO KEY UPDATE still conflicts with itself and with FOR UPDATE, so every writer that takes it is serialised exactly as before. But it does not conflict with FOR KEY SHARE, so FK checks from child inserts pass straight through. You only need FOR UPDATE if you’re going to change the parent’s key columns or delete it.

Takeaways

  • A lock-order rule has to account for the locks the database takes for you — FK checks are the usual hidden one.
  • Use FOR NO KEY UPDATE as the default “serialise writers on this row” lock; reach for FOR UPDATE only when keys change.
  • Put every caller behind one helper so the mode can’t drift; I found eleven hand-rolled copies of the same lock.
  • A green test suite proves nothing here unless a test interleaves an insert against the parent lock.