NOWAIT, lock_timeout, SKIP LOCKED

Waiting in the lock queue is the default, not the law. PostgreSQL gives you three ways out, and they answer three different questions:

Escape hatch	The question it answers
NOWAIT	"Is it free right now? If not, I'll do something else."
lock_timeout	"I'll wait a little — but I refuse to wait forever."
SKIP LOCKED	"Give me any free row; pretend the locked ones don't exist."

NOWAIT: fail fast

A

B

SELECT … FOR UPDATE (locks row 1)

… FOR UPDATE NOWAIT← 55P03 could not obtain lock on row in relation "accounts"

COMMIT (releases the lock)

… NOWAIT→ row 1

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 — instantly, no waiting
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.4 · Run it yourself · Scenario source

lock_timeout: bounded patience

A

B

UPDATE 200 (locks row 1)

UPDATE 300→ gives up after 100ms ← 55P03 canceling statement due to lock timeout

COMMIT

UPDATE 300 (retry succeeds)

A> BEGIN;
BEGIN

A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1

B> SET lock_timeout = '100ms';
SET

B 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 timeout

The failure canceled only B's statement — a retry after A commits works.

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.4 · Run it yourself · Scenario source

SKIP LOCKED: the job-queue primitive

A

B

C

D

SKIP LOCKED→ job 1

SKIP LOCKED→ job 2 (skips 1)

SKIP LOCKED→ job 3 (skips 1, 2)

SKIP LOCKED→ nothing free

ROLLBACK→ job 1 back on the queue

SKIP LOCKED→ job 1

Four workers run the exact same query at the same time.

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)

Worker D finds the queue empty — an instant answer, not a wait.

D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
(0 rows)

A worker crash (rollback) puts its job straight back on the queue.

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;
COMMIT

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

All three of these speak the same SQLSTATE, 55P03 (lock_not_available) — NOWAIT raises it the instant the row is taken, lock_timeout raises it once your patience runs out. Handle it the way you'd handle a 40001 serialization failure: back off and retry. lock_timeout in particular applies separately to each lock the statement tries to acquire, which is what makes it the seatbelt every migration should wear.

SKIP LOCKED is the odd one out, because it doesn't fail at all — it lies by omission. The manual is upfront that this is deliberate: "Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table." (SELECT — The Locking Clause.) That inconsistency is exactly what a job queue wants: each worker grabs a different free row and never blocks, and a worker that rolls back puts its row straight back for the next taker, so you get crash safety for free. The patterns chapter builds a full worker queue on precisely this.

Further reading

• PostgreSQL docs: SELECT — The Locking Clause — NOWAIT and SKIP LOCKED semantics
• PostgreSQL docs: lock_timeout
• The same lesson on MySQL