NOWAIT, lock_timeout, SKIP LOCKED
These PostgreSQL 18 controls change different parts of lock waiting:
| Escape hatch | The question it answers |
|---|---|
NOWAIT | Can this locking read acquire the required row locks without waiting? A conflicting row lock raises 55P03. |
lock_timeout | Has an individual lock acquisition waited longer than the configured nonzero limit? The demonstrated expiry raises 55P03. |
SKIP LOCKED | Which selected rows can this locking read lock now? Conflicting row locks are skipped. |
NOWAIT: fail fast
A> BEGIN;
BEGIN
A> SELECT id FROM accounts WHERE id = 1 FOR UPDATE;
id
----
1
(1 row)
B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- lock_not_available from the conflicting row-lock request
ERROR: 55P03: could not obtain lock on row in relation "accounts"Once A is done, the same statement succeeds.
A> COMMIT;
COMMIT
B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
id
----
1
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
lock_timeout: bounded patience
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1
B> SET lock_timeout = '100ms';
SETB queues for the row lock like anyone else — but gives up after 100ms.
B> UPDATE accounts SET balance = 300 WHERE id = 1; -- lock_not_available, raised after the timeout
ERROR: 55P03: canceling statement due to lock timeoutB's failed standalone statement ends its implicit transaction; a fresh attempt after A commits works here.
A> COMMIT;
COMMIT
B> UPDATE accounts SET balance = 300 WHERE id = 1;
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
300
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
lock_timeout applies separately to each acquisition, not to total statement time. If a shorter statement_timeout applies, it can fire first. Here B runs standalone statements, so its failed statement ends its implicit transaction. Inside an explicit transaction an error requires rollback or recovery through a previously established savepoint before ordinary commands can continue. Retry with fresh decisions only when the application permits it.
SKIP LOCKED: the job-queue primitive
Four sessions run the same query in the listed order with overlapping transactions.
A> BEGIN;
BEGIN
A> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------
1 | send email
(1 row)
B> BEGIN;
BEGIN
B> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is locked by A — skipped, no waiting
id | task
----+--------------
2 | resize image
(1 row)
C> BEGIN;
BEGIN
C> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+--------------
3 | build report
(1 row)D finds no lockable row in this query; three rows still exist.
D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
(0 rows)Explicit ROLLBACK releases A's row lock; a process crash is not injected.
A> ROLLBACK;
ROLLBACK
D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------
1 | send email
(1 row)
B> COMMIT;
COMMIT
C> COMMIT;
COMMITVerified against PostgreSQL 18.6 · Run it yourself · Scenario source
The SELECT contract scopes NOWAIT and SKIP LOCKED to row locks. The required table-level ROW SHARE lock is still acquired normally; other waits and errors remain possible. SKIP LOCKED does not itself raise 55P03 for a skipped row. It produces an inconsistent view suitable for queue-like consumers, not general reporting.
The workers' overlapping transactions are scheduled one after another, not dispatched simultaneously. They assert jobs 1, 2, 3 and then no available row. Explicit rollback releases A's lock, after which D asserts job 1 again. This establishes database-local row availability, not process-crash testing, fairness, duplicate-free external execution, or universal no-wait behavior. The worker-queue lesson builds on this boundary.