Process a Job Queue With SKIP LOCKED

A table of jobs, and several workers that each pick the next job and run it. The first version is a SELECT for the oldest queued row and an UPDATE to mark it. It works with one worker. With two, both read the same row and run it twice. Locking the row fixes that, but then the second worker waits behind the first. FOR UPDATE SKIP LOCKED is the missing piece: a worker takes the first rows that nobody else holds, and passes over the rest. This page shows all three behaviours with two psql sessions, A and B.

Step 1: six queued jobs

Session A

CREATE TABLE jobs (
  job_id int PRIMARY KEY,
  payload text NOT NULL,
  status text NOT NULL DEFAULT 'queued'
);
INSERT INTO jobs (job_id, payload)
SELECT g, 'send receipt ' || g FROM generate_series(1, 6) AS g;

CREATE TABLE
INSERT 0 6

Step 2: two workers with a plain lock

Worker A claims the first two jobs and keeps its transaction open while it works.

Session A

BEGIN;
SELECT job_id FROM jobs WHERE status = 'queued' ORDER BY job_id LIMIT 2 FOR UPDATE;

BEGIN
 job_id
--------
      1
      2
(2 rows)

Worker B asks for jobs in the same way. It uses NOWAIT here, so that the example shows the conflict as an error instead of hanging.

Session B

\set VERBOSITY terse
BEGIN;
SELECT job_id FROM jobs WHERE status = 'queued' ORDER BY job_id LIMIT 2 FOR UPDATE NOWAIT;
ROLLBACK;

BEGIN
ERROR:  could not obtain lock on row in relation "jobs"
ROLLBACK

Without NOWAIT, B would wait until A commits, and then pick the rows after they changed. Every worker is in a line behind the first one, and that is not what a pool of workers is for.

Step 3: skip the rows that are taken

BEGIN;
SELECT job_id FROM jobs WHERE status = 'queued' ORDER BY job_id LIMIT 2 FOR UPDATE SKIP LOCKED;

BEGIN
 job_id
--------
      3
      4
(2 rows)

B got jobs 3 and 4, and did not wait. The rows that A holds were passed over, so no two workers ever get the same job. Both workers now do their work and mark the jobs as done.

Session A

UPDATE jobs SET status = 'done' WHERE job_id IN (1, 2);
COMMIT;

UPDATE 2
COMMIT

Now suppose B's worker crashes before it finishes. Its transaction rolls back, and the lock goes with it.

Session B

ROLLBACK;
SELECT status, count(*) FROM jobs GROUP BY status ORDER BY status;

ROLLBACK
 status | count
--------+-------
 done   |     2
 queued |     4
(2 rows)

Jobs 3 and 4 are still queued, and the next worker takes them. Nothing is lost, and nothing needs a cleanup job, because the status only changes in the transaction that commits. This is the property that makes a database table work as a queue.

Step 4: claim and mark in one statement

In practice, a worker often claims jobs and marks them in one go, and commits at once, so that other workers see them as taken. A subquery with SKIP LOCKED inside an UPDATE does that, and RETURNING gives the claimed rows back.

UPDATE jobs SET status = 'running'
WHERE job_id IN (
  SELECT job_id FROM jobs
  WHERE status = 'queued'
  ORDER BY job_id
  LIMIT 2
  FOR UPDATE SKIP LOCKED
)
RETURNING job_id, payload;

 job_id |    payload
--------+----------------
      3 | send receipt 3
      4 | send receipt 4
(2 rows)

UPDATE 2

The jobs are marked running and the transaction has already committed. If the worker crashes now, those jobs stay running forever, so a real queue needs a time column such as started_at and a sweep that puts old running jobs back in the queue. The claim above is the safe part. The recovery is yours to design.

Related course: SQL Advanced.