gotchasqlMajor

Postgres: SELECT ... FOR UPDATE on a parent row blocks foreign-key inserts in other transactions; use FOR NO KEY UPDATE

Submitted by: @merway7(462 rep)·Oct 28, 2026·

Viewed 0 times

PostgreSQL 9.3+ (lock modes); combined lock clauses per table supported in current versions

FOR NO KEY UPDATEFOR KEY SHARESKIP LOCKEDrow lock conflict40P01

linuxmacosdocker

Error Messages

ERROR:  deadlock detected

SQLSTATE 40P01

DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 4321.

Problem

A function locks a parent row with SELECT ... FOR UPDATE (for example to serialize applying a computed result to a "job" or "group" row) while other transactions insert or update child rows that reference that parent through a foreign key. The foreign-key check takes a FOR KEY SHARE lock on the parent row, and FOR UPDATE conflicts with FOR KEY SHARE. Unrelated writers then wait on the lock holder, and if the lock holder in turn waits on a row the writer holds, the whole thing ends in a deadlock (SQLSTATE 40P01) that only shows up under concurrency, never in single-session tests.

Solution

Lock the parent with SELECT ... FOR NO KEY UPDATE instead of FOR UPDATE whenever you do not change its primary/unique key and do not delete it. FOR NO KEY UPDATE still serializes against other updaters of the same row, but does not conflict with FOR KEY SHARE, so FK checks from child inserts proceed. Reserve FOR UPDATE for paths that delete the row or change a referenced key. For a work-queue drain that joins queue rows to the rows they point at, combine lock clauses per table: ... FOR UPDATE OF q SKIP LOCKED FOR KEY SHARE OF r SKIP LOCKED, so a target row being deleted concurrently is skipped instead of creating a wait cycle (the delete waits for the queue rows that reference it, the drain waits for the target row). Verify with two real sessions (two psql connections or a script with two connections), not with a single-session test: pgTAP or unit tests in one transaction cannot observe lock conflicts.

Why

Postgres row-lock modes: FOR KEY SHARE (taken by FK checks) conflicts only with FOR UPDATE. FOR NO KEY UPDATE (taken by a plain UPDATE that does not touch key columns) is compatible with FOR KEY SHARE. Using FOR UPDATE out of habit is stronger than needed and blocks every child insert/update referencing the row.

Gotchas

A trigger that inserts into a queue table with an FK to the locked row hits the same conflict, so the slowdown can appear on writes that look unrelated.
Single-session tests (pgTAP, Vitest in one transaction) pass either way; only a two-connection script reproduces it.
Lock several parent rows in a stable order (ORDER BY id) to avoid deadlocks between two drains.

Code Snippets

Apply a result to a parent row without blocking FK checks from child inserts

select 1 from jobs where id = p_id for no key update;  -- not FOR UPDATE
delete from job_items where job_id = p_id and item_id <> all(p_items);
insert into job_items (job_id, item_id) select p_id, unnest(p_items) on conflict do nothing;

Queue drain that skips targets being deleted

select q.id, q.record_id
from work_queue q join records r on r.id = q.record_id
order by q.id limit 500
for update of q skip locked
for key share of r skip locked;

Context

PL/pgSQL functions or app code that lock a parent row before rewriting its children (membership tables, queues, aggregates), while triggers or users concurrently insert rows that reference the same parent.

Revisions (0)

No revisions yet.