The Second Worker Never Saw Job 2

SELECT … FOR UPDATE LIMIT 1 does not mean "give me the next free job." It means "lock the first matching row, and wait if somebody else already has it." Worker 2 parks on job 1. Job 2 sits queued. SKIP LOCKED, added in PostgreSQL 9.5, is the clause that hands worker 2 the next unlocked row instead.

I replayed it on a five-row table with two workers. Same shape: CPU bored, queue depth 4, throughput 1.

What does FOR UPDATE LIMIT 1 actually wait on?

The locking clause lives in the PostgreSQL 18 SELECT docs. Default behavior: if the row you want is already locked, you wait. NOWAIT errors. SKIP LOCKED skips.

None of FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE, NOWAIT, or SKIP LOCKED are in the SQL standard. Postgres documented that in the same page. So "I'll write standard SQL and the queue will fan out" is not an answer.

The usual claim query looks innocent:

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

UPDATE jobs SET status = 'running' WHERE id = /* that id */;
-- work happens here, still inside the transaction
COMMIT;

Worker 1 takes job 1 and holds the row lock until COMMIT. Worker 2's identical SELECT wants job 1 first — ORDER BY id LIMIT 1 — and blocks.

Job 2 is still queued. Nobody is running it.

Why is job 2 still queued while worker 2 is idle?

Because LIMIT 1 applies to returned rows, not to "rows I am willing to wait on." Worker 2 is still trying to return job 1. It has not moved on.

Postgres is explicit about another version of this trap: "If a LIMIT is used, locking stops once enough rows have been returned to satisfy the limit (but note that rows skipped over by OFFSET will get locked)."

So:

Clause	Locked row in front of you	Job 2
FOR UPDATE (default)	wait	sits there
FOR UPDATE NOWAIT	error, transaction can abort	never attempted
FOR UPDATE SKIP LOCKED	skip	claimed

SKIP LOCKED shipped in PostgreSQL 9.5 ("Add SELECT option SKIP LOCKED to skip locked rows", Thomas Munro). The 18 docs still warn that skipping locked rows "provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table."

That last clause is the interview sentence. Queues: yes. Account balances: no.

Can you prove the wait without standing up Postgres?

Yes. A 70-line in-memory table is enough to encode the three lock policies and the OFFSET footgun. I ran this locally before writing the rest of the post; it printed skip-locked assertions passed.

class MiniQueue {
  constructor(ids) {
    this.rows = ids.map((id) => ({
      id, status: 'queued', lock: null, claimedBy: null,
    }));
  }

  claim(worker, { skipLocked = false, nowait = false } = {}) {
    for (const row of this.rows) {
      if (row.status !== 'queued') continue;
      if (row.lock) {
        if (skipLocked) continue;
        if (nowait) {
          const err = new Error('could not obtain lock on row in relation "jobs"');
          err.code = 'NOWAIT';
          throw err;
        }
        return { blocked: true, waitingOn: row.id, job: null };
      }
      row.lock = worker;
      row.claimedBy = worker;
      return { blocked: false, waitingOn: null, job: row.id };
    }
    return { blocked: false, waitingOn: null, job: null };
  }

  commit(worker) {
    for (const row of this.rows) {
      if (row.lock === worker) {
        row.lock = null;
        if (row.claimedBy === worker) row.status = 'running';
      }
    }
  }
}

The wait case is the one people draw wrong on a whiteboard:

const q = new MiniQueue([1, 2, 3, 4, 5]);
const a = q.claim('A');            // job 1
const b = q.claim('B');            // blocked on 1, job === null
assert(b.waitingOn === 1);
assert(q.rows.find((r) => r.id === 2).status === 'queued');

Worker B does not "fall through" to job 2. Job 2's status never changes. That is the whole bug.

Flip the flag:

const q = new MiniQueue([1, 2, 3, 4, 5]);
const a = q.claim('A', { skipLocked: true }); // 1
const b = q.claim('B', { skipLocked: true }); // 2
const c = q.claim('C', { skipLocked: true }); // 3
assert(new Set([a.job, b.job, c.job]).size === 3);

Three workers, three distinct jobs, zero waits. NOWAIT on the same setup throws could not obtain lock on row in relation "jobs" instead of skipping — which matches the 9.5 release note: it does not throw for locked rows the way NOWAIT does.

After A commits, job 1 is running and unlocked. B with SKIP LOCKED then takes job 2, not a second copy of job 1. The lock and the status are different columns in your head even when they live on the same row.

Why does OFFSET lock rows you never returned?

Because Postgres said so, in the sentence I quoted above. I encoded it:

selectForUpdate(worker, { offset = 0, limit = 1, skipLocked = false } = {}) {
  let skipped = 0, taken = 0;
  const locked = [], returned = [];
  for (const row of this.rows) {
    if (row.status !== 'queued') continue;
    if (row.lock) {
      if (skipLocked) continue;
      return { blocked: true, locked, returned };
    }
    row.lock = worker;
    locked.push(row.id);
    if (skipped < offset) { skipped += 1; continue; }
    if (taken < limit) {
      row.claimedBy = worker;
      returned.push(row.id);
      taken += 1;
      if (taken >= limit) break;
    }
  }
  return { blocked: false, locked, returned };
}

OFFSET 1 LIMIT 1 on jobs [1, 2, 3] returns job 2 and also locks job 1. Worker B with SKIP LOCKED is forced onto job 3.

Do not paginate a queue with OFFSET. Use WHERE id > $last or SKIP LOCKED plus a real cursor. OFFSET in a locker is how you accidentally serialize workers on a row you did not even claim.

How do you say this in the interview?

I walk it as four beats, then stop.

1. Name the default. FOR UPDATE LIMIT 1 waits on the first matching row. Extra workers go idle while later rows sit queued.
2. Name the clause. SKIP LOCKED skips rows that cannot be locked immediately. Official docs restrict that to queue-like tables because the snapshot is inconsistent on purpose.
3. Name the sibling. NOWAIT fails the statement. Retries of NOWAIT still stampede the same hot row. That is not a queue.
4. Name the footgun. OFFSET locks skipped rows. I have the sentence from the SELECT page; I do not paraphrase it.

Then the production shape: claim and UPDATE in a short transaction, COMMIT, then do the work. Holding FOR UPDATE across an HTTP call is how worker 2 waits five minutes for job 1's Stripe round-trip while job 2–5 idle.

This is not a toy pattern. Graphile Worker claims with limit ${batchSize} for update skip locked. If an interviewer asks "have you seen this outside a blog," that file is the receipt.

If you are rehearsing the same job-queue answer for a Staff-loop system-design round — multiple consumers, no double-processing, what you do when a worker dies mid-job — walk the wait / NOWAIT / SKIP LOCKED table out loud, then add the lock-hold timeout. aceround.app is the AI interview assistant I use to get pushed on the follow-up ("the worker crashed, job 1 stays running, now what?"). The SQL does not change because the interviewer added a crash; the status machine around it does.

What I still want to hear in a loop: how you recover a lock that outlived the process. SKIP LOCKED does not help you there. A locked_at timestamp plus a reclaim query does. That is a different pad.

---

This post was drafted with AI assistance and then edited against the PostgreSQL 18 SELECT locking clause, the PostgreSQL 9.5 release notes, and Graphile Worker's getJobs.ts. The Node assertions were run locally before publish.