SKIP LOCKED: Building a Job Queue

Dequeue work atomically with no collisions and no external broker — and see exactly how NOWAIT fails differently

A naive queue query — SELECT the oldest queued row, mark it processing — has an obvious race: two workers running that same query at nearly the same instant can both select the same row before either has marked it taken. Adding FOR UPDATE fixes the double-pick but introduces a new problem: the second worker now waits, blocked, for the first worker's entire job to finish, single-filing what was supposed to be parallel work. SKIP LOCKED is the missing piece: a worker whose candidate row is already locked by someone else does not wait for it at all — it simply moves on to the next available row, as if the locked one did not exist.\n\nThis lab proves the no-collision guarantee directly: one worker holds a job mid-processing while a second, genuinely concurrent worker asks for work at the same moment, and picks a completely different job with no wait and no error. Then it contrasts this with FOR UPDATE NOWAIT, which fails loudly instead of skipping quietly — a different tool for a different situation.

The Full Dequeue Pattern

BEGIN;
SELECT id FROM jobs WHERE status = 'queued' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
-- ... do the actual work here ...
UPDATE jobs SET status = 'done' WHERE id = <that id>;
COMMIT;

Everything from the SELECT to the final UPDATE lives in one transaction: if the worker crashes mid-job, the row lock (and the whole transaction) is released automatically on disconnect, and the job goes back to being genuinely queued for the next worker to pick up — no job is ever silently lost.

SKIP LOCKED vs NOWAIT

-- SKIP LOCKED: silently move past a row someone else already has
SELECT id FROM jobs WHERE status = 'queued' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;

-- NOWAIT: fail loudly and immediately if the row is already locked
SELECT id FROM jobs WHERE status = 'queued' ORDER BY id FOR UPDATE NOWAIT LIMIT 1;

Both are non-blocking — neither one ever makes a session wait the way plain FOR UPDATE would. The difference is what happens next: SKIP LOCKED quietly tries a different row, which is exactly what a job-queue worker wants. NOWAIT raises an error and aborts the transaction, which is the right behavior when a lock conflict means something is genuinely wrong and should stop the caller rather than being silently routed around.

FOR UPDATE SKIP LOCKED

Modifies a row-locking SELECT to silently exclude any row that another session already has locked, rather than waiting for it or erroring. Combined with LIMIT 1 and an ORDER BY, it turns "give me one thing to work on" into a query that never collides with any other session running the identical query at the same moment.

FOR UPDATE NOWAIT

Also non-blocking, but with the opposite reaction to a lock conflict: it immediately raises could not obtain lock on row and aborts the transaction, rather than trying a different row. Appropriate when encountering a lock at all is itself the meaningful signal — for example, enforcing that only one process may touch a specific row, with any conflict treated as an error condition to surface.

🔒 Worker One Claims a Job

Background a worker that claims a job and holds it, wrapped in a sleep to simulate real processing time.

psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT id FROM jobs WHERE status='queued' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;" -c "UPDATE jobs SET status='done', worker='w1' WHERE id = 1;" -c "SELECT pg_sleep(4);" -c "COMMIT;" > /tmp/w1.log 2>&1 &

student@lab:~$ psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT id FROM jobs WHERE status='queued' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;" -c "UPDATE jobs SET status='done', worker='w1' WHERE id = 1;" -c "SELECT pg_sleep(4);" -c "COMMIT;" > /tmp/w1.log 2>&1 & [1] 143

✅ Worker Two Never Collides

While worker one is still mid-job, run a second worker concurrently and confirm it picks a completely different job with no wait at all.

psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT id FROM jobs WHERE status='queued' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;" -c "UPDATE jobs SET status='done', worker='w2' WHERE id = 2;" -c "COMMIT;"

student@lab:~$ psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT id FROM jobs WHERE status='queued' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;" -c "UPDATE jobs SET status='done', worker='w2' WHERE id = 2;" -c "COMMIT;" SET BEGIN id ---- 2 (1 row) UPDATE 1 COMMIT

🔎 Confirm No Collision Occurred

Wait for worker one to finish, then confirm both jobs were processed by different workers with no overlap and no missed work.

sleep 4

cat /tmp/w1.log

psql -U postgres -d beer_db -c "SELECT id, status, worker FROM jobs ORDER BY id;"

⚠️ Contrast: FOR UPDATE NOWAIT Fails Loudly

Repeat the same contention with NOWAIT instead of SKIP LOCKED, and see it error out rather than pick a different row.

psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT id FROM jobs WHERE status='queued' ORDER BY id FOR UPDATE NOWAIT LIMIT 1;" -c "SELECT pg_sleep(5);" -c "COMMIT;" > /tmp/nowait_holder.log 2>&1 &

sleep 1

psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT id FROM jobs WHERE status='queued' ORDER BY id FOR UPDATE NOWAIT LIMIT 1;" -c "COMMIT;"

student@lab:~$ psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT id FROM jobs WHERE status='queued' ORDER BY id FOR UPDATE NOWAIT LIMIT 1;" -c "SELECT pg_sleep(5);" -c "COMMIT;" > /tmp/nowait_holder.log 2>&1 & [1] 150 student@lab:~$ sleep 1 student@lab:~$ psql -U postgres -d beer_db -c "BEGIN;" -c "SELECT id FROM jobs WHERE status='queued' ORDER BY id FOR UPDATE NOWAIT LIMIT 1;" -c "COMMIT;" SET BEGIN ERROR: could not obtain lock on row in relation "jobs" ROLLBACK

Lab 2.7.3 complete. A collision-free job queue, built entirely from one clause and verified against real concurrent workers:\n\n\n FOR UPDATE SKIP LOCKED : ✅ two concurrent workers, zero collisions\n No wait confirmed : ✅ worker two returned instantly, not blocked\n Exactly-once semantics : ✅ dequeue + process + complete, one transaction\n NOWAIT contrasted : ✅ hard error instead of silently trying another row\n

Enable JavaScript to run the live terminal and track your progress.