PostgreSQL Locking Joins

Row locking does not refresh the statement snapshot or restart the statement from scratch. Under Read Committed, rechecking an updated base row against previously fetched join data can discard a match despite a valid foreign key.

Diagnose before changing isolation

Capture SQL, isolation, server version, and EXPLAIN. Look for LockRows above a join and concurrent join-key updates. Reproduction harness: A updates; B starts its locking join; verify B is blocked before committing A. Use only an explicitly selected disposable database.

Lock first, then read

For work_item(id PRIMARY KEY, queue_id NOT NULL REFERENCES queue(id)), run as a non-interactive psql script:

\set ON_ERROR_STOP on
\unset locked_queue_id
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT queue_id AS locked_queue_id
FROM work_item WHERE id = 42
FOR NO KEY UPDATE
\gset
SELECT * FROM queue WHERE id = :'locked_queue_id';
-- Validate and perform dependent writes here.
ROLLBACK; -- Demonstration only.

Bind the returned key into the second statement. Abort/rollback on missing rows, including concurrent deletions. Keep dependent writes inside this transaction.

Sources: Haki Benita, 2026-02-24; PostgreSQL 18 isolation, locking, SELECT, retries, psql. Reviewed 2026-10-07; PostgreSQL 17.11 startup blocked by Unix-socket permissions; examples unexecuted. Recheck on the deployed version.

Pitfalls

• Locking the old related row as well does not repair the join; a left join can merely expose NULLs.
• A locking CTE cannot see a related row inserted after its statement snapshot; prefer separate statements when that race matters.
• Autocommit releases the first lock before the second read. SKIP LOCKED intentionally omits rows; it is not a correctness repair.
• Base locks neither freeze related attributes nor protect absent rows. Broader invariants need a locking protocol followed by every relevant writer; acquire locks consistently.
• For 40001 serialization failures or 40P01 deadlocks, retry the whole transaction, including decisions; avoid duplicating external side effects.