lock_timeout and NOWAIT - fail fast instead of waiting forever

Default PostgreSQL lock waits are unbounded. Session B wants a row Session A holds. B parks until A commits, rolls back, or the server restarts. That is fine for a 5 ms critical section. It is fatal when A is stuck in a slow report, a forgotten BEGIN, or a human transaction left open in psql.

Three ways to not wait forever

Tool	Scope	What happens on conflict	Use when
lock_timeout	Session / transaction GUC	Wait up to N ms, then error 55P03 lock_not_available	Any lock wait (row, table, advisory) should have a ceiling
NOWAIT	One SELECT … FOR UPDATE/SHARE	If a target row lock is unavailable → immediate error	This claim must succeed now or abort
SKIP LOCKED (Day 38)	One claim query	Skip locked rows; take the next free one	Queue workers — work is fungible

SKIP LOCKED finds other work. NOWAIT refuses to wait for the selected row lock; the statement can still wait while acquiring its required table-level lock. Keep lock_timeout (or explicitly acquire the table lock with LOCK … NOWAIT) when the whole lock path needs a ceiling. Pick by whether another row is acceptable.

Worked race: holder vs waiter

Two sessions, one row. A holds FOR UPDATE for 500 ms. B wants the same row.

B’s mode	Behavior	Lab median (30 runs)
Default FOR UPDATE	Blocks until A ends · 0 lock errors	441 ms
SET LOCAL lock_timeout = ‘100ms’	Waits ≤ budget · 30/30 errors	114 ms
FOR UPDATE NOWAIT	Errors if locked · 30/30 errors	13.3 ms

3.9× default/timeout wall (441 / 114). Product claim is bounded wait, not a micro-optimised “speedup.” NOWAIT is “refuse now,” not “wait a little.”

-- Session A
BEGIN;
SELECT id FROM accounts WHERE id = 1 FOR UPDATE;
-- hold… do not commit yet

-- Session B (default): parks until A commits
SELECT id FROM accounts WHERE id = 1 FOR UPDATE;

-- Session B (ceiling):
BEGIN;
SET LOCAL lock_timeout = '100ms';
SELECT id FROM accounts WHERE id = 1 FOR UPDATE;
-- ERROR: canceling statement due to lock timeout
ROLLBACK;

-- Session B (instant refuse):
SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "accounts"

lock_timeout vs statement_timeout vs idle killers

GUC	Measures	Stops
lock_timeout	Time spent waiting for a lock only	Parked acquirers (our lab)
statement_timeout	Whole statement wall (CPU + I/O + waits)	Runaway queries overall
idle_in_transaction_session_timeout	Idle time after BEGIN with no activity	Forgotten open transactions holding locks

Default for lock_timeout and statement_timeout is 0 = disabled. Docs note: if both are set and statement_timeout ≤ lock_timeout, the statement timeout usually fires first. For writer APIs, set a tight lock_timeout on the pool role; use statement_timeout as a coarser safety net; use idle-in-transaction timeout so a crashed client cannot pin rows forever after BEGIN.

NOWAIT vs SKIP LOCKED (decision)

Question	Prefer
Is any free job OK?	SKIP LOCKED (Day 38)
Must I lock this row (inventory unit, seat, advisory key)?	NOWAIT or short lock_timeout + retry/backoff (Day 37)
Is waiting a few ms OK, but not seconds?	lock_timeout (budget), not bare default
Could this be a deadlock risk with multi-lock order?	Consistent order (Day 36) and a timeout so a bug fails loud

Diagram: wait budget

Lab (Podman, real numbers)

Dual backends run inside til-postgres; the recorded harness times the waiter only (no podman-exec tax). Holder hold 500 ms. 30 runs, median. The harness and standalone write-up are not included in this repository.

Mode	median ms	min	max	lock errors / 30
default FOR UPDATE	441	439	596	0
lock_timeout=100ms	114	112	117	30
FOR UPDATE NOWAIT	13.3	11.3	15.8	30

Default sits just under the 500 ms sleep (acquire stagger ~80 ms + commit path). Timeout clusters on the 100 ms budget. NOWAIT is “a few ms of client work,” not zero — still two orders below default wait.

Production shape (pool role)

-- On the app role / pool startup (not only one lucky query)
ALTER ROLE app_pool SET lock_timeout = '2s';
ALTER ROLE app_pool SET statement_timeout = '15s';
ALTER ROLE app_pool SET idle_in_transaction_session_timeout = '10s';

-- Per hot path you can still tighten:
BEGIN;
SET LOCAL lock_timeout = '100ms';
SELECT … FOR UPDATE;  -- or NOWAIT / SKIP LOCKED
COMMIT;

Budgets are product choices. The invariant is: never ship unbounded lock waits on a user-facing pool.

Anti-patterns

• Global lock_timeout in postgresql.conf only — docs warn against blunt cluster defaults; set on the app role / pool.
• NOWAIT on a multi-row queue head when any job would do — you wanted SKIP LOCKED.
• Timeout without retry policy — fail-fast still needs Day 37 jittered backoff or a user-visible “busy, try again.”
• Assuming deadlock_timeout replaces lock_timeout — deadlock detection (Day 36) is a different timer for cycles, not a general wait ceiling.
• Measuring with cold podman exec per session — exec overhead (~1 s) can hide a 100 ms budget; lab times waiter inside the container.

How it connects

• Day 24 / 38: row locks and claim patterns (SKIP LOCKED when work is fungible).
• Day 36: unbounded waits + cycles; timeouts make stuck lockers visible.
• Day 37: after 55P03, retry with full jitter — not a tight loop.
• Day 43: advisory locks honor lock_timeout the same way.
• Day 44: backpressure is the distributed cousin — slow the producer instead of queueing forever.

Interview version (60s)

“Postgres lock waits are infinite by default. I set lock_timeout on writer sessions so a stuck holder cannot pin my API thread for minutes. Lab shape: holder 500 ms → default waiter ~441 ms, lock_timeout=100ms ~114 ms then error (3.9×), NOWAIT ~13 ms. If the business rule is ‘this exact row or fail,’ I use FOR UPDATE NOWAIT. If any free job is fine, I use SKIP LOCKED. Timeouts without a retry/backoff story just turn hangs into error storms.”

Quiz

Questions? Ask the agent — next DB slot after living this day is Mon again in the rotation; prep may already hold later concepts.