SKIP LOCKED - Claim the Next Free Job

Day 14 taught queues. Day 24 taught that FOR UPDATE is a real wait. Day 36 taught waits that cycle. Day 30 made redelivery safe; Day 37 made retries calm. Today: two workers, one jobs table — and why the second worker should skip a locked row instead of sleeping on it.

A common pattern: store work in PostgreSQL (jobs with status = ‘pending’), and let N app workers claim rows. The naive claim is:

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE;  -- then mark running / process

Worker A locks the head row. Worker B wants the same head. With plain FOR UPDATE, B blocks until A commits — even though rows 2..N are free. Your “parallel” workers become a single-file line. FOR UPDATE SKIP LOCKED says: if this row is locked, pretend it is not in the result and keep looking. B takes job 2 while A still holds job 1.

The problem: head-of-line blocking

Imagine 40 pending jobs and two workers. Both run the claim query. Without SKIP LOCKED:

• A locks job 1, processes it (maybe 20 ms of real work).
• B’s SELECT … FOR UPDATE wants job 1 too → waits.
• When A finishes, B may finally lock job 1 (already done) or re-run and get job 2 — after wasting a full wait.

In our lab, the blocking mode often leaves one worker with all jobs and the other with zero — perfect serialization dressed up as concurrency. That is head-of-line blocking on a queue head: free work sits behind a locked row because the claimer refuses to look past it.

Worked race: two workers, 40 jobs

Walk the first few claims. Both workers loop: claim one pending row → do 20 ms of work → mark done → claim again. Same table, same ORDER BY id.

Step	FOR UPDATE (block)	FOR UPDATE SKIP LOCKED
t0	A locks job 1; B also wants job 1 → B sleeps	A locks job 1; B sees 1 locked → skips → locks job 2
t1	A works job 1 (~20 ms); B still idle on the wait	A works 1; B works 2 — both busy
t2	A commits, claims job 2; B wakes and chases A’s head	Each claims the next free id; work splits
End sample	A/B ≈ 40 / 0 — one worker did everything	A/B ≈ 18 / 22 — both stayed productive

The product claim is not “locks free.” It is: do not queue free work behind a locked head row. When job bodies are non-trivial, idle time on the second worker is pure waste. Our lab’s sample split (first run) is exactly that race: blocking serialized the fleet; skip shared the set.

The mechanism: SKIP LOCKED

BEGIN;
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- if a row returned:
UPDATE jobs SET status = 'running', worker = $1
WHERE id = $id;
COMMIT;
-- process outside or inside a short tx; then mark done

Official docs are explicit and honest: SKIP LOCKED can return an incomplete set of matching rows — an inconsistent view of “all pending.” Locked matches are omitted on purpose. That is a feature for queues (claim what is free now), and a bug if you needed “every matching row under one snapshot.” Do not paper over that sentence; design around it.

FOR UPDATE vs SKIP LOCKED vs NOWAIT

Clause	When row is locked	Result shape	Typical use
FOR UPDATE	Waits until lock free	Eventually that row (or next after recheck)	You need this row; waiting is correct
FOR UPDATE NOWAIT	Errors immediately	No row; exception	Refuse to block and refuse to skip
FOR UPDATE SKIP LOCKED	Omits locked matches	Next free match, or empty	“Give me any free job” queue claim

Queue workers almost always want skip, not error-on-busy. NOWAIT is the right tool when failing fast beats taking alternate work (e.g. a UI path that must lock a specific order row or bounce).

Docs honesty: incomplete sets are intentional

PostgreSQL’s locking-clause docs state that SKIP LOCKED can return fewer rows than a plain filter would, because locked candidates are skipped. That is not a race bug in the planner — it is the contract:

• OK for queues: “claim up to N free pending jobs right now.” Incomplete is correct; another worker holds the rest.
• Wrong for reports / batch “touch every match”: if business logic needs every status=‘pending’ row under one consistent scan, skip will silently under-count. Use plain FOR UPDATE (and accept waits), or a non-locking snapshot read if you are not claiming.
• Isolation still matters: under stricter levels you still get MVCC visibility rules (Day 24 / Day 41 territory). Skip only changes lock wait policy, not “which committed rows exist.”

When NOT to use SKIP LOCKED

• You need every matching row once under one snapshot — inventory freeze, migration pass, “close all open invoices for merchant X” as a single consistent set.
• You must lock a specific row and fail if busy — use NOWAIT or plain wait; skip would steal a different id.
• Fairness / strict priority across long-held claims — skip can starve lower-priority free work if high-priority rows stay locked and your ORDER BY never reaches the tail; design leases + reclaim, not just skip.
• You hoped skip alone would fix multi-row deadlocks — Day 36 still applies when workers lock shared business rows in opposite order. Skip only avoids convoy waits on queue heads.

What you still need

• Short transactions: claim fast; do heavy work after commit (or keep the critical section tiny).
• Idempotent handlers (Day 30): crashes after claim can redeliver — keys / status machines matter.
• Retry policy (Day 37): transient DB errors need jittered backoff, not a hot loop on the same head.
• Not a full message broker: SKIP LOCKED is excellent for simple Postgres-backed queues; huge fan-out, multi-consumer fan-in, or cross-language consumers may still want Day 14 infrastructure.

Benchmark — two workers, 40 jobs (PostgreSQL 18.4)

Real Podman til-postgres. Two concurrent sessions each loop: claim next pending → pg_sleep(20ms) work → mark done. Modes: blocking FOR UPDATE vs FOR UPDATE SKIP LOCKED. 30 runs, median wall time.

Mode	Jobs done (30 runs)	Sample A/B split	Median wall ms
FOR UPDATE (block)	30/30 finished all 40	40 / 0 (one worker idle)	1594.979
FOR UPDATE SKIP LOCKED	30/30 finished all 40	18 / 22 (both busy)	1271.489

Headline: both modes finished all 40 jobs in 30/30 runs — correctness is not the differentiator. SKIP LOCKED is about keeping peers working — sample split 40/0 vs 18/22. Wall time improved 1.25× (1594.979 → 1271.489 ms) under a 20 ms in-DB work sleep; heavier job bodies amplify the parallelism win. Product claim is not “locks free,” it is “do not queue free work behind a locked head row.”

What breaks? — Anti-patterns

1. Using SKIP LOCKED for “process every matching row once under RR.” Docs: incomplete view. Fix: use it for claiming free work, not full-table business reports.

2. Long transactions while holding the claim lock. Holds the row; peers skip past forever if you never finish. Fix: claim → commit → process → done update.

3. No status machine / idempotency. Crash after claim orphans “running” jobs. Fix: Day 30 keys + lease/heartbeat or reclaim of stale running.

4. Expecting SKIP LOCKED to prevent deadlocks alone. Multi-row lock order still matters (Day 36). Fix: single-row claim patterns + consistent order elsewhere.

5. Hot-spinning empty claims. Empty result means “no free work now,” not “retry without pause.” Fix: Day 37 jittered backoff or a listen/notify wake.

How it connects

• Day 14 (queues): SKIP LOCKED is the SQL-shaped cousin of competing consumers — multiple workers pull free work without a central broker.
• Day 24 (MVCC / FOR UPDATE): plain SELECT does not claim; FOR UPDATE does; SKIP only changes what happens when the candidate is already locked.
• Day 30 (idempotency): redelivery after crash or reclaim needs safe handlers so “claim again” never double-applies side effects.
• Day 36 (deadlocks): blocking on the same queue head is a convoy, not always a cycle — but multi-row business locks still need consistent order; skip reduces needless waits on heads.
• Day 37 (jitter): when claim fails transiently or the queue is empty, backoff — do not hot-spin SELECT.

Transfer questions

What you should be able to do

• Write a claim query with FOR UPDATE SKIP LOCKED.
• Explain head-of-line blocking under plain FOR UPDATE (including a 40/0-style race).
• State that SKIP LOCKED can omit locked matches (by design) and when that is wrong.
• Contrast FOR UPDATE vs NOWAIT vs SKIP LOCKED.
• Compose claims with short txs, idempotent handlers (Day 30), and jittered retries (Day 37).

Quiz

Your teach step

Close this lesson. Write the “Explain like I’m 10” and the “60-second LinkedIn version” from memory. Focus on: head-of-line blocking, SKIP LOCKED skip-to-next, the 40/0 vs 18/22 lab, docs incomplete-set honesty, and when skip is the wrong tool. Post it, and paste the link.

Questions? Ask the agent — FOR NO KEY UPDATE, lease/heartbeat reclaim, or SKIP LOCKED under REPEATABLE READ.