A job queue on FOR UPDATE SKIP LOCKED

At READ COMMITTED, two workers select different available rows, and an explicit rollback makes one selected job available again. This is Demonstrated behavior for the database queue below. It does not execute the email or invoice named in the task column, kill a worker process, or prove duplicate-free external effects.

Keep the selection and database-local completion update in one transaction:

A

B

claim→ job 1

claim→ job 2 (skips locked job 1)

job 1 done

COMMIT

ROLLBACK→ job 2 remains queued

claim→ job 2 (back in queue)

job 2 done

COMMIT

both jobs done

Two workers select available queued rows in id order and mark database state done. Task names are data; no email or invoice is executed.

A> BEGIN;
BEGIN

A> SELECT id, task FROM jobs WHERE state = 'queued'
   ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
 id |        task        
----+--------------------
  1 | send welcome email 
(1 row)

B> BEGIN;
BEGIN

B> SELECT id, task FROM jobs WHERE state = 'queued'
   ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is claimed — skipped without waiting
 id |       task       
----+------------------
  2 | generate invoice 
(1 row)

A marks job 1 done and commits. B explicitly rolls back; no worker process is killed.

A> UPDATE jobs SET state = 'done' WHERE id = 1;
UPDATE 1

A> COMMIT;
COMMIT

B> ROLLBACK;
ROLLBACK

B selects job 2 again after rollback. This checks row availability, not the number of external effects.

B> BEGIN;
BEGIN

B> SELECT id, task FROM jobs WHERE state = 'queued'
   ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
 id |       task       
----+------------------
  2 | generate invoice 
(1 row)

B> UPDATE jobs SET state = 'done' WHERE id = 2;
UPDATE 1

B> COMMIT;
COMMIT

A> SELECT id, state FROM jobs ORDER BY id;
 id | state 
----+-------
  1 | done  
  2 | done  
(2 rows)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

A selects job 1; B skips that locked row and selects job 2. A commits done. B explicitly rolls back, then selects job 2 again and commits done. The final query asserts both database states. It does not count task executions.

The PostgreSQL 18 locking-clause contract permits this queue use but warns that SKIP LOCKED gives an inconsistent view. It skips conflicting row locks, not table locks or every possible wait. ORDER BY id orders available rows; it does not promise global FIFO fairness when an earlier row is locked. All workers must use the same selection and completion protocol in a transaction.

The transaction is the lease

The row lock lasts until transaction end. PostgreSQL's session-termination contract rolls back an open transaction when the connection ends. This scenario uses ROLLBACK instead of testing process or server failure. Even after rollback, an email already sent cannot be undone by this database transaction.

Keep transactions short. An old snapshot can delay reclamation of relevant row versions, as chapter 4 explains; an open READ COMMITTED transaction alone does not establish that it retains such a snapshot. The queue-bloat case measures a particular hung-worker schedule. A claimed-state queue with stale-claim recovery is a different design, not verified here.

The outbox relay uses this row selection pattern. Its database transaction protects queue state, while external delivery needs a separate retry and deduplication policy.

Further reading

• PostgreSQL 18: The Locking Clause
• The same lesson on MySQL

The reconstructed support assessment records the exact manual support and execution limits for this lesson.