Several workers processing one job table must avoid selecting the same available row concurrently. PostgreSQL row locking can coordinate claims. It does not magically guarantee exactly-once external work: worker crashes and expired claims still require an explicit recovery design.
Before you start
Understand transactions, row locks and UPDATE. Assume a jobs table with id, status, created_at, claimed_at and worker_id. Create representative test data in an isolated database. The SQL below uses PostgreSQL syntax and performs a real update when executed.
Step-by-step walkthrough
Step 1: Define available work
Pending is the claimable state. Ordering by created_at and id gives a deterministic preference among available rows. Decide whether strict fairness is required: skipping locked rows deliberately trades waiting for throughput and can bypass an older currently locked job.
Step 2: Lock while selecting
Select one candidate with FOR UPDATE SKIP LOCKED in the claim transaction. Another worker skips that currently locked row and can claim another one. This behavior fits queue-like work; it should not be presented as a general consistent view of every matching row for ordinary reporting.
Step 3: Record the claim and commit
Update the selected row to running and return its identity within the same transaction. Commit promptly, then perform long processing outside that claim transaction. Otherwise the worker holds database locks and a connection while unrelated external work waits.
Worked scenario
BEGIN;
WITH candidate AS (
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at, id
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE jobs AS j
SET status = 'running', claimed_at = CURRENT_TIMESTAMP,
worker_id = 'worker-a'
FROM candidate AS c
WHERE j.id = c.id
RETURNING j.id;
COMMIT;Worker A locks the earliest eligible row and records its ownership. If worker B tries while A’s transaction remains open, B skips that row and can select another pending row. If A rolls back, its claim does not persist. An index aligned with status filtering and ordering is a candidate optimization; inspect the actual plan and data distribution.
Common mistake
A running status is not a recovery policy. A worker can commit its claim and crash before processing. Store and enforce an appropriate lease or heartbeat policy, plus bounded retries. A worker can also complete a remote effect then crash before recording success, so reclaiming that job may repeat the effect unless the destination supports stable idempotency.
Verify the behavior
Open two database sessions, begin concurrent claim transactions and compare returned IDs. Test an empty queue, locked earliest row and rollback. Then test recovery for a committed but abandoned claim. Completion should verify ownership or a claim token so an old worker cannot overwrite a newer claimant’s state after its lease expired.
Interview exercise
Does SKIP LOCKED guarantee fair order and exactly-once processing?
Answer and reasoning
No. Locked rows are skipped, so processing order can differ from creation order. Locks coordinate selection while held; they do not atomically include arbitrary external effects. Use explicit leases, ownership tokens and idempotent effect identity where required. Exactly-once claims about a distributed workflow need a clearly stated boundary and failure model, not merely a SQL clause.
Continue learning
Compare row lock ownership and SQL interview questions. The PostgreSQL SELECT reference explains locking clauses and SKIP LOCKED semantics.