skip to content

You are implementing a job-queue table where many workers claim pending rows. Explain the difference between a locking read that waits, one that fails immediately (NOWAIT), and one that skips already-locked rows (SKIP LOCKED), and which one a queue claim should use and why.

level: seniorimportance: should knowfreq 45%

answer

  1. Wait / error / skip — same lock, three conflict policies
  2. Plain FOR UPDATE on a queue = convoy of one
  3. SKIP LOCKED result is intentionally not consistent
  4. Claim + mark + commit, run the job outside
  5. Lease + reaper for dead workers; index the pending predicate

basics

~20 s

A plain locking read waits for the lock holder, so all workers queue behind the same row. NOWAIT raises an error instead of waiting. SKIP LOCKED silently omits rows locked by other transactions, so each worker claims a different row. A queue claim should use SKIP LOCKED; it turns a convoy into parallel workers.

solid answer

~60 s

All three are exclusive row locks; they differ only in what happens when the row is already locked. - **Wait (default):** the session blocks until the holder commits or rolls back, then proceeds with the fresh value. Correct for "I must have *this* row" — decrementing this account, this seat. - **NOWAIT:** the statement errors out immediately. Useful for interactive paths where a fast failure beats an unpredictable stall, and for detecting contention explicitly. - **SKIP LOCKED:** locked rows are simply excluded from the result set. The result is no longer a consistent view of "the first N pending rows" — which is exactly what a queue wants. For a queue, waiting is pathological: every worker orders by the same criteria, all target the same head row, and N workers serialize into one. NOWAIT converts that into an error storm and a client-side retry loop. SKIP LOCKED lets worker 1 take row 1, worker 2 take row 2, and so on, with the claim and the status update in one short transaction. Cost: no ordering guarantee under contention, and you need an index supporting the pending-rows predicate.

code

sql · 15 lines
sql
BEGIN;
SELECT id
  FROM job
 WHERE status = 'PENDING'
 ORDER BY created_at
 LIMIT 10
   FOR UPDATE SKIP LOCKED;

UPDATE job
   SET status = 'IN_PROGRESS',
       worker_id = ?,
       claimed_at = now()
 WHERE id IN (...);
COMMIT;
-- run the jobs OUTSIDE this transaction, then record terminal state

go deeper

for a junior

State the three behaviours — wait, error, skip — and that a queue claim uses skip so workers do not line up behind the same row.

for a middle

Explain the convoy that plain FOR UPDATE creates, why the result set is intentionally non-consistent, and the claim-then-commit-then-work sequence.

for a senior

Cover lease/reaper recovery, idempotent handlers, index design for the claim predicate, batch size tradeoffs, and where NOWAIT is the right tool instead.

for a principal

Weigh the table-backed queue against a dedicated broker, defend it on transactional-outbox grounds, and set ordering and throughput expectations explicitly.

## One lock, three wait policies A locking read requests an exclusive row lock as part of a SELECT, held until the transaction ends. The three variants differ only in the *conflict policy* when another live transaction already holds the lock: 1. **Wait** — block indefinitely (or until a lock-timeout setting fires). The waiter then re-reads the row's latest committed version and continues. 2. **NOWAIT** — do not block; raise an error at once. The application decides whether to retry, back off, or tell the user the resource is busy. 3. **SKIP LOCKED** — do not block and do not error; omit those rows from the result and return whatever is free. A critical property of SKIP LOCKED: the result set is deliberately *not* a consistent snapshot of the predicate. Two concurrent sessions running the identical query legitimately get disjoint results. That is unacceptable for reporting and perfect for work distribution — which is why it exists. ## Why waiting is wrong for a queue A queue claim is typically "the oldest pending job": `WHERE status = 'PENDING' ORDER BY created_at LIMIT 1 FOR UPDATE`. Every worker issues the same query and therefore targets the same row. With the default wait policy, worker 2 blocks on worker 1's lock; worker 3 blocks behind worker 2. Twenty workers behave like one, and their latency is the job's processing time multiplied by their position in the convoy. Worse, if the transaction stays open for the whole job execution, lock hold time equals job duration. NOWAIT removes the blocking but replaces it with failure: nineteen workers get an immediate error and must retry, which produces a hot spin loop and an error rate that looks like an outage. It is the right tool elsewhere — a user action that must either grab a resource now or report "someone else is editing this", where an unbounded stall would be worse than a clean rejection. SKIP LOCKED fits the shape of the problem: each worker's query walks the candidate rows, skips those already claimed by peers, and locks the first free one. N workers claim N distinct jobs in parallel with no blocking and no errors. ## Claiming correctly The durable pattern is a short transaction that both locks and marks: - Begin, select the candidate rows with SKIP LOCKED and a small limit, update their status to something like `IN_PROGRESS` (with worker id and claim timestamp), commit. - Execute the job **outside** that transaction. - Record the terminal state in a second short transaction. Holding the claim transaction open for the whole job is the common mistake: it turns every slow job into a long-running transaction, and if the worker dies the rollback silently returns the job to the pool, possibly after it already had external effects. With explicit status marking plus a claim timestamp, a crashed worker's job is recovered by a reaper that re-queues rows stuck in `IN_PROGRESS` past a lease deadline — an at-least-once guarantee that pushes the burden onto idempotent handlers. ## Costs and caveats **Ordering is best-effort.** Under contention, workers necessarily process rows out of strict order. If per-key ordering matters (all events for one customer in sequence), add a partition key and let a worker claim by key group, or keep a per-key single-claim rule; do not pretend SKIP LOCKED preserves FIFO. **Indexing matters more than usual.** The claim query scans candidates and skips locked ones, so it can examine and discard many rows. A partial or filtered index on the pending predicate (status plus ordering column) keeps that scan short. Without it, throughput degrades as completed rows accumulate; archiving or partitioning finished jobs helps for the same reason. **Locks are per row, and the lock still costs.** Claim in small batches to amortize round trips, but large batches increase the blast radius when a worker dies mid-batch. **Availability varies by engine.** SKIP LOCKED and NOWAIT are widely available in mainstream engines (PostgreSQL 9.5+, MySQL 8.0+, Oracle for a long time; SQL Server expresses the same idea with a readpast hint). On an engine without it, queue-on-a-table degrades badly and a dedicated broker is usually the better answer. **Know when not to build this.** A table-backed queue is excellent when jobs must be transactionally consistent with the data that created them (the outbox pattern). At very high throughput, a purpose-built broker beats it — but the transactional handoff is the reason teams keep choosing the table.

  • A worker crashes after claiming rows but before finishing them. How do the jobs get processed?
    Because the claim transaction committed, the rows stay in IN_PROGRESS with a worker id and a claim timestamp rather than rolling back into PENDING. A reaper job periodically re-queues rows whose claim is older than a lease deadline, optionally incrementing an attempt counter and moving repeat offenders to a dead-letter state. This yields at-least-once delivery, so job handlers must be idempotent.
  • When would you prefer NOWAIT over SKIP LOCKED?
    When you need one specific row rather than any free row, and stalling is worse than failing. A user opening an edit screen for a particular record, or an admin action on a single entity, is better served by an immediate 'this record is busy' than by an unpredictable wait. SKIP LOCKED would be wrong there because silently returning no row is indistinguishable from the record not existing.

saying these in an interview costs you the question

  • Holding the claim transaction open for the entire job execution
  • Expecting strict FIFO ordering from a SKIP LOCKED claim under concurrency
  • Using SKIP LOCKED for reporting or aggregate queries, where silently missing rows is a correctness bug
  • Retrying NOWAIT failures in a tight loop with no backoff
  • Assuming the claim needs no supporting index, so the scan grows with completed-job history
  • Relying on rollback of a crashed worker to re-queue the job instead of a lease and reaper

context

resolving links…