6 min read

SKIP LOCKED: Claim the Next Job in One Statement, Then Test It on Real Postgres

Claiming queue work with one FOR UPDATE SKIP LOCKED statement, and why that forces your tests onto a real Postgres with cloned template databases.
Row of metal lockers with padlocks on doors
Photo by Laura Rivera on Unsplash

Almost every backend I have worked on ends up with a queue table somewhere: jobs waiting for a worker, tickets waiting for someone to pick them up. And almost every one of them starts with the same innocent bug. Two workers ask for "the next one" at the same moment, and both get the same row.

This post is about the fix I now reach for by default, a single Postgres statement built around FOR UPDATE SKIP LOCKED, and about the less obvious consequence: once your correctness depends on row locks, your tests have to run against a real Postgres. Not SQLite, not an in-memory fake.

The bug: read, then update

The first version of a claim usually looks like this in application code:

const { rows } = await db.query(
  `SELECT id FROM jobs WHERE status = 'queued'
   ORDER BY priority, created_at LIMIT 1`
);
if (rows.length === 0) return null;
await db.query(
  `UPDATE jobs SET status = 'running', worker_id = $1 WHERE id = $2`,
  [workerId, rows[0].id]
);

Two statements, nothing holding them together. Worker A reads job 1. Before A's update lands, worker B also reads job 1, because it is still queued. Both update it, both believe they own it, and the job runs twice. Under light load this almost never happens, which is exactly why it survives into production. It shows up on the busiest day, when the work is most expensive to do twice.

The fix: one statement with the lock inside it

The version I use now puts the select, the lock and the update into one statement:

WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE status = 'queued'
  ORDER BY priority, created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs
SET status = 'running', worker_id = $1, claimed_at = now()
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.id;

Each clause does real work:

  • FOR UPDATE takes a row lock on the candidate. No other transaction can lock or update that row until this one commits.
  • SKIP LOCKED means a second worker does not wait for that lock. It passes over the locked row and takes the next eligible one. Without it, twenty workers would line up behind one lock and the last would wait for nineteen round trips.
  • LIMIT 1 inside the CTE means exactly one row gets locked per call, not the whole queue.
  • RETURNING hands back what you claimed, so there is no follow-up read.

To be fair to the two-statement version: you can make it correct by wrapping it in a transaction and adding FOR UPDATE to the select. Row locks last until the transaction ends, not the statement. But then correctness depends on every caller remembering the transaction, and the lock is held across a network round trip to your app. The single statement can't be half-done, and the lock lives exactly as long as it needs to.

Other options I considered and dropped: SERIALIZABLE isolation is correct but turns contention into retries the caller must handle, on the hottest path in the system. An in-process mutex stops working the moment you run a second process. An advisory lock per queue serialises every worker behind one lock, which is the very thing SKIP LOCKED exists to avoid.

Why this forces a real Postgres in your tests

Here is the part I did not see coming. The correctness of the claim now lives entirely inside the database's locking. SQLite has no row locks to skip. Embedded and in-memory Postgres emulators either don't implement SKIP LOCKED or run everything on one connection, so nothing is ever concurrent. A test against either one goes green and proves nothing, which is worse than no test, because it tells you you're safe.

So the suite runs against real Postgres, and the key test is a race:

it('never hands one job to two workers', async () => {
  await seedJobs(20);
  // Check out every connection first, so the claims really overlap.
  const clients = await Promise.all(
    Array.from({ length: 20 }, () => pool.connect())
  );
  try {
    const claimed = await Promise.all(
      clients.map((c, i) => claimNext(c, `worker-${i}`))
    );
    const ids = claimed.map((r) => r.id);
    expect(new Set(ids).size).toBe(20);
    // And the table agrees, not just the return values.
    const { rows } = await pool.query(
      `SELECT count(*)::int AS n FROM jobs WHERE status = 'running'`
    );
    expect(rows[0].n).toBe(20);
  } finally {
    clients.forEach((c) => c.release());
  }
});

Two things about that test took me a while to get right.

The race has to be real. If the pool is smaller than the number of callers, or the connections are opened lazily, the first claim finishes before the last caller even connects. I proved this to myself by deleting the lock and watching a race test still pass: there was no race to lose. Size the pool for the test's concurrency and connect up front.

Distinct is not the same as fair. SKIP LOCKED could quietly turn "the next job" into "whatever row was easiest to reach", and a distinctness check would never notice. So there is a second race: five workers against ten queued jobs, asserting that the five claimed are exactly the first five in queue order. Plus a test that a worker who loses the race gets nothing, rather than someone else's job. And I run the concurrency suite many times in a row before trusting it, because a race that fails one run in twenty is still a race.

Cheap databases from a template

Real Postgres per test sounds slow. It isn't, if you clone instead of migrate. Postgres can create a database as a file-level copy of another one:

CREATE DATABASE app_test_w3_p12345 TEMPLATE app_test_template;

The setup is:

  1. Global setup creates and migrates one template database, guarded by pg_advisory_lock so parallel test workers wait for each other instead of all migrating at once.
  2. Each test worker clones its own database from the template. That takes tens of milliseconds, with no migrations replayed and no container to start.
  3. Between tests, one TRUNCATE ... RESTART IDENTITY CASCADE over the tables. I avoided the usual trick of wrapping each test in a rolled-back transaction, because a test that shares one transaction can't have two connections fighting over a row lock.

This works fine without Docker. A locally installed Postgres is enough, and CI uses the same code path.

One bug here was subtle. I first named each worker's database after the worker slot number the test runner hands out. But those slots get reused: a new process can pick up a slot while an older one is still finishing. So the new process dropped "its" database, which another live process was still connected to. That showed up as connections terminated by the server, in a file whose tests had all passed. The fix was to add the process id to the name (the _p12345 above), so no two live processes can share one. A sweep in global setup drops any worker database whose process no longer exists.

Trade-offs

  • It is not strict FIFO under contention. Claims are taken in order, but they commit in whatever order the workers finish. If you need strict ordering of completion, a queue table with SKIP LOCKED is the wrong tool.
  • SKIP LOCKED hides rows; it doesn't wait for them. An empty result means "nothing I could lock right now", not "the queue is empty". This bit me when I picked one sub-queue first and reported "nothing waiting" because its only job was locked a millisecond earlier, while other sub-queues were full. Try the next candidate before giving up, and never use a SKIP LOCKED query for counting.
  • Everything in the WHERE clause runs under a row lock. Keep the predicate cheap and backed by an index, and check the plan.
  • Template databases need nobody connected to the template. CREATE DATABASE ... TEMPLATE fails if any session is open on it, so close the migration connection before cloning.
  • Every developer needs Postgres installed and running. That's a real cost for a tiny project. I think it's worth it once locks carry correctness.

Takeaway

Claim work in one statement and let the database's row lock do the arguing. Once that lock is what makes your code correct, test against the engine that owns it, with a race that is actually racing.