SELECT FOR UPDATE and SKIP LOCKED

Transactions, isolation and locking Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Fill the blank

Let several workers each claim a different pending job without waiting on each other.

SQL · PostgreSQL 17

CREATE TABLE jobs (
  id bigserial PRIMARY KEY,
  state text NOT NULL DEFAULT 'pending'
);
INSERT INTO jobs (state) VALUES ('pending'), ('pending');
BEGIN;
SELECT id FROM jobs
WHERE state = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE ____;
COMMIT;

OptionsSKIP LOCKEDNOWAITOF jobsSHARE

Hint 1 of 1

You want to move past rows other workers hold.

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A SKIP LOCKED

Correct. A second worker skips the row this one holds and takes the next.

FOR UPDATE locks the chosen row, and SKIP LOCKED tells other workers to pass over rows already locked instead of waiting. Each worker ends up with a different job.

Why not the others?

B NOWAIT. Fails with an error instead of moving on to the next job.
C OF jobs. Names the table to lock; workers still queue on the same row.
D SHARE. Not valid here, and a shared lock lets two workers claim the same job.

30-second answer

FOR UPDATE takes a row lock on every row the query returns; other transactions that try to update or lock those rows wait until you commit or roll back. Plain readers are not blocked. Adding SKIP LOCKED makes a second worker skip rows another worker has locked instead of waiting, so many workers can each claim a different job with ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED.

Practise saying it · 35 s. No recording, just a timer

Explain row locks and SKIP LOCKED.

Say your answer out loud, then check which of these points you covered:

FOR UPDATE locks returned rows
Readers aren't blocked
SKIP LOCKED for queues
Short claim transaction

Deep dive

Lock strength from weakest: FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, FOR UPDATE. Use FOR NO KEY UPDATE when you won't change the key; it doesn't block foreign-key checks.

NOWAIT errors immediately (55P03) instead of waiting.

SKIP LOCKED gives an inconsistent view on purpose; it's right for queues and wrong for reports.

Keep the claim transaction short: claim, mark as running, commit, then do the work.

Common mistakes

Holding the row lock while calling a slow external API.
Using FOR UPDATE on a query with an aggregate (it isn't allowed).
Building a queue with polling and no SKIP LOCKED, so every worker waits on the same row.

Senior take & follow-ups

Mention lock strength and SKIP LOCKED, and say when a database queue is fine (thousands of jobs per second, not millions).

Follow-up questions

Think of your answer first, then open the hint.

When does a Postgres-backed queue stop being enough?
Show hint
Very high throughput, fan-out to many consumers, or replay needs: move to Kafka or a broker.
Does FOR UPDATE block plain SELECTs?
Show hint
No. MVCC readers never wait for row locks.

Suggest a fix if something here is wrong or out of date.