SKIP LOCKED for job queues — how do you build a concurrent queue without race conditions?

Q: SKIP LOCKED for job queues — how do you build a concurrent queue without race conditions?

**Target Audience:** Backend developers who want a reliable job queue on PostgreSQL without a broker, and candidates asked to design one in an interview. **Difficulty:** Hard **Engines:** PostgreSQL 16.15 (two real `psql` sessions for every race, the verifier for the claim and plan) and SQLite via sql.js 1.10.2 for the runnable claim and reaper. MySQL facts are cited, not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A restaurant kitchen has a rail of order tickets and four cooks. The rule is simple: take the oldest ticket and cook it. If two cooks look at the rail at the same moment, both see the same oldest ticket, both start cooking it, and table 12 gets two steaks whi

The problem

Question presented to candidate: "We want to run background jobs from a PostgreSQL table instead of adding a message broker. Several workers will poll the same table. How do you make sure each job is picked up by exactly one worker, workers do not block each other, and a job is not lost when a worker crashes?"

What a strong answer should cover:

Why the naive "SELECT the oldest queued job, then UPDATE it" double-claims under concurrency, and why plain FOR UPDATE is correct but makes workers queue one behind another
The claim statement: SELECT ... ORDER BY ... LIMIT 1 FOR UPDATE SKIP LOCKED inside a CTE that UPDATE ... RETURNING the job, so claiming is one atomic step
Crash safety: either keep the transaction open while working (a crash rolls back and releases the row) or commit a lease (locked_until) and have a reaper re-queue expired jobs; attempts counter and a dead-letter state
Indexing: a partial index on the ready jobs so each claim is an index lookup, not a scan of finished rows; table bloat and vacuum on a busy queue
Engine notes: SKIP LOCKED since PostgreSQL 9.5 and in MySQL 8.0; SQLite has one writer, so a single UPDATE ... RETURNING claim is safe there but does not scale out
Limits: ordering is only approximate with SKIP LOCKED, at-least-once delivery means handlers must be idempotent, LISTEN/NOTIFY can replace tight polling

Clarifying questions expected:

How many jobs per second and how long does a job take (seconds or minutes)? That decides between holding the transaction open and using a lease.
Is strict ordering required, or is "roughly oldest first" acceptable?
What should happen to a job that keeps failing?

Code / implementation expected: Yes — the jobs table, the claim query, the completion and the reaper for expired leases.

Schema used by the examples:

skip-locked
queue
concurrency

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Backend developers who want a reliable job queue on PostgreSQL without a broker, and candidates asked to design one in an interview. Difficulty: Hard Engines: PostgreSQL 16.15 (two real psql sessions for every race, the verifier for the claim and plan) and SQLite via sql.js 1.10.2 for the runnable claim and reaper. MySQL facts are cited, not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A restaurant kitchen has a rail of order tickets and four cooks. The rule is simple: take the oldest ticket and cook it.

If two cooks look at the rail at the s

The solution is hidden so you can try first

10 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSQLite: claim the next ready email job in one UPDATE ... RETURNING

SQLRe-queue jobs whose lease expired, or fail them after 5 attempts

PostgreSQL: the concurrent claim with FOR UPDATE SKIP LOCKED inside a CTE

WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE queue = 'email'
    AND status = 'queued'
    AND run_after <= '2026-10-17 09:10'
  ORDER BY priority, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'running',
    attempts = attempts + 1,
    locked_by = 'worker-a',
    locked_until = '2026-10-17 09:15'
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.id, jobs.payload, jobs.attempts, jobs.locked_by;

PostgreSQL: the claim plan, LockRows under Limit over the partial index

SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id
FROM jobs
WHERE queue = 'email' AND status = 'queued'
ORDER BY priority, id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.