FOR UPDATE Locks a Parent Row, Not Child Rows in Postgres

A row lock in PostgreSQL protects one row. It does not protect the set of rows that point at it. Under the default READ COMMITTED isolation, SELECT ... FOR UPDATE on a parent row says nothing about which child rows exist, which ones another session is about to insert, or which ones are changing state. This post follows three concurrency bugs that come from reading a row lock as a lock on a relationship, and the query shape, transaction boundary, and timeout that fix them.

A row lock is not a predicate lock

SELECT ... FOR UPDATE marks the rows that statement returned. Another transaction that tries to update, delete, or lock those same rows waits until you commit or roll back. That is the entire guarantee. It says nothing about rows that did not exist when your statement ran, and nothing about rows in other tables that reference the row you locked.

The misreading comes from how we talk about it. "I locked the parent, so nobody can touch it" quietly becomes "nobody can touch anything that belongs to it". In a document model that reading holds, since children live inside the parent record. In a relational schema they are separate rows with their own locks.

PostgreSQL does have a mode that reasons about sets: under SERIALIZABLE it tracks predicate reads and aborts one of two transactions whose read and write sets conflict, including conflicts through rows that did not exist yet. The default is READ COMMITTED, where every statement takes a fresh snapshot and nothing tracks predicates. There, an invariant covering a set of rows has to be enforced by one statement that touches the whole set, or by serializing every writer behind a single row they all lock.

Trap 1: the gap between two status-scoped updates

The setup: a parent row can be retired (a soft delete). When it is, every child row under it moves to rejected, and the caller wants a count per previous state for an audit line. The straightforward code writes one statement per state.

-- Two statements, one logical intent. The gap between them is the bug.
UPDATE child SET status = 'rejected'
 WHERE parent_id = $1 AND status = 'confirmed';   -- count A

UPDATE child SET status = 'rejected'
 WHERE parent_id = $1 AND status = 'candidate';   -- count B

Both statements are scoped to the parent, both are atomic on their own, and a transaction wraps them, so this reads as safe. It is not. Between them, another session can commit a child row from candidate to confirmed. The first statement already ran and did not see it as confirmed; the second filters on candidate and no longer sees it as candidate. The row matches neither filter and stays confirmed under a retired parent.

Adding SELECT ... FROM parent WHERE id = $1 FOR UPDATE at the top does not close the gap. That lock covers one row in parent. The promoted child row lives in child, and its writer never needed the parent lock, because a foreign key check fires on insert and on key-column updates, not on a status change. Both counts are wrong too, since the row is missing from A and from B.

The fix is a single statement that locks the target set and then writes exactly that set.

WITH target AS MATERIALIZED (
  SELECT id, status AS old_status FROM child
   WHERE parent_id = $1 AND status IN ('confirmed','candidate') FOR UPDATE
), upd AS (
  UPDATE child c SET status = 'rejected'
    FROM target t WHERE c.id = t.id RETURNING t.id, t.old_status
) SELECT id, old_status FROM upd

The FOR UPDATE inside target locks every matching child row. If another session committed a change to one of those rows after the statement's snapshot, PostgreSQL waits for the lock, then re-evaluates the WHERE clause against the newly committed version. That mechanism is EvalPlanQual. A row that just became confirmed still satisfies status IN ('confirmed','candidate') and gets demoted with the rest. A row just moved to rejected drops out, correctly, since it is already in the target state.

The counts come out right for the same reason: they describe the rows the statement actually locked and changed, not two counts sampled at different moments. MATERIALIZED does not change how often the CTE runs here, since it is referenced once; it states the intent, which is to fix the target set and act on exactly that set.

Trap 2: a guard that reads without a lock is not a guard

The same feature needs the opposite defense: once a parent is retired, no new child may be inserted under it and no existing child may be confirmed. The natural place for that check is the top of the write path.

// Broken: the read runs in its own implicit transaction, so any lock it
// takes is released before the write begins.
func AddChild(ctx context.Context, pool *pgxpool.Pool, parentID int64, label string) error {
	status, err := parentStatus(ctx, pool, parentID) // own connection, own snapshot
	if err != nil {
		return err
	}
	if status == statusRejected {
		return ErrParentRejected
	}
	return insertChild(ctx, pool, parentID, label) // new transaction, new snapshot
}

This compiles, passes review, and enforces nothing. The reason turns on a lock the code never asked for.

An insert into child has to validate its foreign key, and that validation takes a FOR KEY SHARE lock on the referenced parent row. FOR KEY SHARE conflicts with FOR UPDATE, so while the cascade holds the parent, the insert blocks inside the foreign key check and waits for it. That part is what you would want.

The problem is where the guard read sits relative to that wait. It ran earlier, on a different connection, in its own single-statement transaction, and saw candidate because the cascade had not committed yet. Then the insert blocks, the cascade commits, and the insert wakes up and succeeds. The foreign key lock does not make that interleaving unlikely. It makes it the preferred one, because it parks the insert exactly until the cascade is done.

The fix is to run the guard in the same transaction as the write, holding a lock that conflicts with the cascade.

SELECT status FROM parent WHERE id = $1 FOR SHARE

FOR SHARE conflicts with FOR UPDATE, so the guard waits for the cascade instead of reading around it, and EvalPlanQual hands back the newly committed version once the lock is granted. The guard sees rejected and refuses. FOR SHARE also conflicts with FOR NO KEY UPDATE, the mode a plain UPDATE parent SET status = ... takes implicitly, so it waits even for a cascade that never wrote a locking clause. Two guards at once still do not block each other, because shared locks are compatible.

// Fixed: guard and write share one transaction, and the guard holds a lock
// that conflicts with the cascade until that transaction commits.
func AddChildTx(ctx context.Context, tx pgx.Tx, parentID int64, label string) error {
	var status string
	err := tx.QueryRow(ctx,
		`SELECT status FROM parent WHERE id = $1 FOR SHARE`, parentID).Scan(&status)
	if err != nil {
		return err
	}
	if status == statusRejected {
		return ErrParentRejected
	}
	return insertChildTx(ctx, tx, parentID, label)
}

The general lesson: the function signature decides whether the guard works. A function that takes a transaction handle inherits the caller's transaction, so the guard's lock survives until that transaction commits. A function that takes a connection pool opens its own transaction and gives the lock back before returning. Both type-check, both look the same in a diff, and only one enforces anything.

Trap 3: every new lock adds a new place to wait

A guard that takes a lock changes the timing of a path that previously took none, and its opponent is not only the cascade you just wrote. Any transaction holding the parent row for a long time now delays every write under that parent. A nightly job that locks a parent, works slowly, and commits at the end will stall a person's confirm click for its whole run. A row lock wait has no default timeout.

So introduce the lock and its bound together.

BEGIN;
SET LOCAL lock_timeout = '3s';
SELECT status FROM parent WHERE id = $1 FOR SHARE;

SET LOCAL scopes the setting to the current transaction, so it cannot leak into the next user of that pooled connection. When a wait exceeds the bound, PostgreSQL raises SQLSTATE 55P03 (lock_not_available), which needs a translation into something the caller can act on.

var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == "55P03" { // lock_not_available
	return ErrBusyRetryLater
}

For a request a person is waiting on, 55P03 means "this record is busy, try again in a moment". For a background pass over many parents, it means "leave this one and take it next run", not "abort the batch". Getting that wrong turns one click into a failed nightly run: someone confirms a child, the batch hits the 3 second bound, treats it as fatal, and stops with hundreds of parents unprocessed. NOWAIT and SKIP LOCKED are sharper versions of the same idea.

Lock ordering, timestamps, and tests that pin the behavior

Three details decide whether the fixes above stay correct in a real codebase.

Lock ordering comes first. A deadlock needs two paths that take the same two rows in opposite orders. If every path takes the parent before the children, no cycle can form, and the new guard joins that order, since it reads the parent first. One path that locks a child before its parent is enough to create the cycle, so verify by searching, not by memory: grep the write paths for locking reads and updates on the child table, and check what each already holds.

Timestamps are second, and the bug they cause is silent. A bulk transition like this often touches a column such as reviewed_at, meant to record when a person judged the row. If a dashboard groups counts by that column, stamping it during a cascade rewrites history: rows judged yesterday now count as rejections today, and last week's numbers move with nobody editing anything. A bulk consequence is not a human judgement, so leave that timestamp alone.

Tests are third. Whether the transition branches are mutually exclusive is a pure function of current state and requested state, so lift it out of the SQL path and pin it with a table test. In this work, a request to retire an already retired parent fell into the revive branch, because the branch conditions were separate conditionals that were not exclusive. It compiled, existing tests passed, and manual checks missed it because the current client used a newer endpoint and only an older path reached that branch.

tests := []struct {
	name    string
	current string
	request string
	want    action
}{
	{"retire a live parent", statusActive, statusRejected, actionCascade},
	{"retire an already retired parent", statusRejected, statusRejected, actionNoop},
	{"revive a retired parent", statusRejected, statusActive, actionRevive},
	{"revive a live parent", statusActive, statusActive, actionNoop},
}

Common questions

Should I switch to SERIALIZABLE instead?

It addresses the root cause: predicate conflicts, including inserts of rows that did not exist yet, are detected and one transaction aborts with SQLSTATE 40001. The price is that every transaction becomes retryable, so every write path needs a retry loop and every side effect has to tolerate a replay. Predicate lock tracking also costs memory and can escalate from row to page granularity, which shows up as conflicts between transactions that never overlapped. Making one hot path serializable while the rest stays READ COMMITTED buys little, because the checks only apply between transactions both running at that level.

Would an advisory lock be simpler?

pg_advisory_xact_lock on a key derived from the parent id serializes all writers under that parent, and it works even when there is no row to lock. Two drawbacks: the lock is a convention rather than a constraint, so a new code path that forgets it gets no error and no wait, just a race nobody sees; and the key space is global, so unrelated features can collide unless you use the two-argument form with a namespace.

What about FOR NO KEY UPDATE?

That is the weaker mode a plain UPDATE of non-key columns takes, and it exists so foreign key checks do not wait for updates that cannot break a key. Which is the point: FOR NO KEY UPDATE does not conflict with FOR KEY SHARE, so it does not make child inserts wait. To hold inserts back, lock the parent with FOR UPDATE. For the guard, FOR SHARE is enough, since it conflicts with both write modes.

Should the database enforce this with a trigger or a constraint?

A BEFORE INSERT trigger on child that reads the parent status has the same defect as the application guard, and for the same reason: its read needs FOR SHARE, and the foreign key check that would have blocked runs later in the statement. A declarative version is possible: carry the parent state in the child's foreign key, a composite reference to (id, status) with ON UPDATE CASCADE, plus a CHECK forbidding the retired value on children. No code path can bypass that, at the cost of a wider key and an inverted failure: retiring the parent is what fails now.

One boundary is worth stating plainly. Every fix here serializes writers on a single parent row, which keeps it cheap, since two operations on different parents never contend. An invariant spanning several parents, such as a global cap on confirmed children, has no single row to lock, and needs a dedicated row all writers take, an advisory lock on the rule, or SERIALIZABLE. Most of the time the parent row is the right unit, and the only question is whether your statements and transaction boundaries use it.