Wait Policies

Topic: Databases

Concrete problem

A table jobs holds pending jobs. Ten worker processes each want one job. Each worker runs:

SELECT * FROM jobs WHERE state = 'pending' ORDER BY id LIMIT 1 FOR UPDATE;

Worker 1 locks job 1. Workers 2 to 10 want the same row. All nine wait for worker 1. Ten workers do the work of one. How can the workers avoid waiting for each other?

A second problem: a web request must claim a seat. If another request holds the seat, the web request must tell the user “busy” at once. It must not hang.

Background terms

• Wait policy: A wait policy is the rule that a requester follows when its lock request conflicts with a lock that another transaction holds. The wait policy does not change which lock modes conflict. It only changes what the requester does after a conflict.
• Job queue: A job queue is a table (or other store) of work items. Many workers take items from it. Each item must go to exactly one worker.
• SQLSTATE 55P03: SQLSTATE is the error code that Postgres sends with an error. The code 55P03 means lock_not_available. A request that gives up on a lock raises this error.

Intuition

Take the moment when B asks for a row lock and the lock conflicts with one that A holds. B has only three moves.

1. Wait. B stops until A ends. Postgres then re-checks the row and continues. This is the default.
2. Fail. B gives up and raises an error. The application decides what to do next.
3. Skip. B acts as if the locked row does not exist. B continues with other rows.

B cannot use the row, because the lock forbids it. So these three moves are the only moves. Postgres gives one clause for each non-default move:

Move	Clause	Scope
Wait	(none)	Waits without a limit
Wait with a limit	SET lock_timeout = '2s'	Session setting. Raises 55P03 after the limit.
Fail	NOWAIT	Per statement. Raises 55P03 at once.
Skip	SKIP LOCKED	Per statement. Row locks only.

SKIP LOCKED fits a job queue. Each worker skips the jobs that other workers hold. Each worker gets a different job.

SKIP LOCKED gives an incomplete view on purpose. The result does not contain every row that matches. Use it for queues. Do not use it where every matching row must appear in the result.

Numeric example

Five pending jobs: 1, 2, 3, 4, 5. Three workers start at the same moment. Each worker runs ... ORDER BY id LIMIT 1 FOR UPDATE ....

Default (wait).

Worker	Locks	What happens
W1	job 1	Works on job 1.
W2	wants job 1	Waits for W1.
W3	wants job 1	Waits for W1.

Only W1 works. W2 and W3 wait until W1 commits. The three workers run one after the other.

With SKIP LOCKED.

Worker	Locks	What happens
W1	job 1	Works on job 1.
W2	job 2	Skips job 1 (locked). Works on job 2.
W3	job 3	Skips jobs 1 and 2 (locked). Works on job 3.

Three jobs run at the same time. Nobody waits.

With NOWAIT. W2 tries job 1 first. Job 1 is locked. W2 raises error 55P03 at once. W2 does not try job 2.

Visual

graph TD
  C["B asks for a row lock.<br/>A holds a conflicting lock."] --> W["No clause: B waits"]
  C --> T["lock_timeout: B waits, then raises 55P03"]
  C --> N["NOWAIT: B raises 55P03 at once"]
  C --> S["SKIP LOCKED: B skips the row and continues"]

Notation

The clause goes at the end of a locking SELECT:

SELECT ... FOR UPDATE NOWAIT;
SELECT ... FOR UPDATE SKIP LOCKED;
SELECT ... FOR SHARE SKIP LOCKED;

The clause works with all four row lock modes.

The usual job queue statement:

UPDATE jobs SET state = 'running'
WHERE id = (
  SELECT id FROM jobs
  WHERE state = 'pending'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;

The inner SELECT finds one job that no worker holds and locks it. The outer UPDATE marks it as running.

Table locks also accept NOWAIT: LOCK TABLE t IN ... MODE NOWAIT. SKIP LOCKED works on row locks only.

Plain-English translation

• “NOWAIT” means “If the row is busy, tell me with an error. Do not make me wait.”
• “SKIP LOCKED” means “If the row is busy, leave it out. Give me the other rows.”
• “lock_timeout” means “Wait for the lock, but only this long.”

The wait policy belongs to the request. The conflict table belongs to the lock modes. These two parts are independent.

Equation

There is no equation. A rule describes the three outcomes. Let $L$ be the set of rows that the statement wants. Let $H\subseteq L$ be the rows that another transaction holds in a conflicting mode.

• Wait: the result contains $L$ after the locks on $H$ end.
• NOWAIT: the result is an error if $H\ne \varnothing$. Otherwise it is $L$.
• SKIP LOCKED: the result is $L\setminus H$.

What changes when parameters change

• No row is locked ($H=\varnothing$). All three policies give the same result.
• LIMIT with SKIP LOCKED. The statement skips locked rows and keeps looking until it has enough unlocked rows or the table ends.
• LIMIT with NOWAIT. The statement locks rows in order. It raises the error when it meets the first locked row, even if later rows are free.
• Many locked rows. SKIP LOCKED returns fewer rows than asked. A worker must accept an empty result.
• SKIP LOCKED on a plain SELECT (no lock clause). This is not allowed. The clause belongs to a locking clause.

Practice problem

Try these on a later review. Do not open the answers first.

1. A page shows “the next 10 pending jobs”. The query uses FOR UPDATE SKIP LOCKED LIMIT 10. Why can this page show a wrong view of the queue?
2. An edit screen runs SELECT ... FOR UPDATE NOWAIT on a record when the user opens it. The screen shows “someone else is editing” on failure. Which error code does the app catch?
3. In problem 2, the app keeps the transaction open while the user types. What problem does this cause?

Answers

1. SKIP LOCKED hides every row that another session has locked. The result depends on what other sessions do at that moment. It is not a view of the whole queue. Use SKIP LOCKED to take work, not to count or display work.
2. 55P03 (lock_not_available).
3. The lock lasts until the end of the transaction. A user who leaves the screen open keeps the lock. Other users see “someone else is editing” for as long as the user stays away. A long transaction also holds back cleanup of old row versions.

Demo

Each scenario opens real concurrent sessions against a local Postgres. The log shows every statement with a time stamp. The folder is 03-wait-policies.

Scenario	What it shows
01-nowait	NOWAIT raises an error at once.
02-lock-timeout	lock_timeout raises an error after a fixed wait.
03-skip-locked-queue	A job queue. SKIP LOCKED runs in parallel. The default wait runs in line.

To run one scenario, start Postgres with ./up.sh. Then run go run ./03-wait-policies/01-nowait in the repository. To run all scenarios of this concept, use ./run.sh 03-wait-policies.

Quiz

Asked on 2026-10-05 after the lesson. All three answers were correct.

Quiz (2026-10-05)

Q1. Jobs 1 to 5 are pending. A holds FOR UPDATE on job 1 and has not committed. B runs SELECT id FROM jobs WHERE state = 'pending' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 2. What does B get?

• Job 1 and job 2, after A ends

• Job 2 only

• Job 2 and job 3

• An error at once

Answer and reasoning

Your answer: Job 2 and job 3 (correct). Correct answer: Job 2 and job 3.

SKIP LOCKED leaves out job 1. The statement keeps looking until it has 2 unlocked rows. The first option is the “wait” result. The second option forgets that LIMIT 2 keeps looking. The fourth option is the NOWAIT result.

Q2. Same setup, but B uses NOWAIT in place of SKIP LOCKED. What does B get?

• Job 2 and job 3

• An error at once

• An empty result

• Job 1 and job 2, after A ends

Answer and reasoning

Your answer: An error at once (correct). Correct answer: An error at once.

B tries to lock job 1 first, because of ORDER BY id. Job 1 is locked. NOWAIT raises error 55P03 at once. B does not try job 2.

Q3. Which statement about wait policies is true?

• SKIP LOCKED makes FOR UPDATE a weaker lock mode

• NOWAIT makes the lock end sooner

• SKIP LOCKED returns every row that matches the WHERE clause

• A wait policy changes what the requester does on a conflict, not which modes conflict

Answer and reasoning

Your answer: A wait policy changes what the requester does on a conflict, not which modes conflict (correct). Correct answer: A wait policy changes what the requester does on a conflict, not which modes conflict.

The wait policy belongs to the request. The conflict table belongs to the lock modes. The two are independent. SKIP LOCKED returns fewer rows on purpose.

My reasoning

Quiz on 2026-10-05: all three correct. The user applied the three outcomes (wait, fail, skip) to a LIMIT 2 case without error.

Session history

• 2026-10-05 — First taught. Quiz all correct. See 2026-10-05 Databases - Postgres Locking.
• 2026-10-05 — Demo section added. See 03-wait-policies.