Recommended Free Tools
A single SQL statement is not automatically an exclusive job claim. Under PostgreSQL’s default READ COMMITTED isolation, a worker can select a pending row and then update it after another worker has changed it, if the query does not lock the candidate row as part of selection. The exact diagnosis depends on the SQL, schema, transaction boundaries and server version; the pattern below shows how to make queue claims contend by locking candidates and skipping rows already locked by another worker.
How two workers can both appear to claim one job
PostgreSQL uses READ COMMITTED by default. A plain SELECT sees a snapshot taken when its command starts. For an UPDATE that encounters a row concurrently changed by another transaction, PostgreSQL may wait for that transaction to finish and then re-evaluate the update’s WHERE condition against the changed row. Those behaviors make the query’s shape important: selecting a candidate in a subquery does not, by itself, mean that the candidate row is exclusively reserved for that worker.
As an Amazon Associate I earn from qualifying purchases.
If two workers can select the same pending job, and the outer update can still match that row after waiting, both statements may report it as claimed. Whether that is what a particular query does depends on its predicates and structure; without the exact SQL, it is a conditional diagnosis, not a conclusion about a specific statement. PostgreSQL describes the relevant behavior in its transaction-isolation documentation.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Use a row lock when selecting the candidate
For queue consumers, PostgreSQL’s FOR UPDATE SKIP LOCKED lets a worker lock a candidate row while selecting it, and skip rows another worker has already locked. Put the locking clause inside the CTE that selects the candidate:
#1 Best Overall
WITH candidate AS (
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY priority DESC, id
FOR UPDATE SKIP LOCKED
LIMIT 1
)
UPDATE jobs AS j
SET status = 'running', claimed_at = now()
FROM candidate AS c
WHERE j.id = c.id
RETURNING j.*;Adapt the table, columns, eligibility rules and update to the application. This is an illustrative pattern, not a tested query. The CTE locks the selected row; the outer update changes that same row and returns its updated data. A unique ordering, such as priority DESC, id when id is unique, makes the intended choice predictable when the query uses LIMIT. PostgreSQL documents both the row-locking syntax and the need for a unique ordering when a limited result must be predictable in its SELECT documentation.
What changes when workers lock or skip candidates
| Approach | Concurrent behavior | Ordering | Abandoned jobs |
|---|---|---|---|
| Candidate selection without a row lock | Workers may select the same candidate; the exact outcome depends on the query’s update predicates and structure. | A unique ORDER BY with LIMIT makes selection predictable, but does not reserve the row. |
Recovery behavior is not provided by the selection itself; the application must define it. |
Candidate selection with FOR UPDATE SKIP LOCKED |
A worker skips rows locked by another worker rather than waiting on those rows. | A unique ORDER BY with LIMIT makes candidate order predictable. |
Locking does not define recovery if a worker crashes; the application must decide what happens to work left incomplete. |
SKIP LOCKED intentionally produces an inconsistent view of the table, so it is suited to avoiding contention among consumers of a queue-like table, not to general-purpose reads that require a consistent view. It also does not guarantee fairness or exactly-once execution of external side effects. PostgreSQL’s documentation explains the queue-use trade-off; job retries, leases or other crash-recovery policies remain application decisions.
Check the actual statement before changing it
To confirm whether this race applies, inspect the complete statement and the context in which it runs:
- Does the candidate-selection query lock the row, or does it only read a pending job?
- Can the outer
UPDATEstill match a row after another transaction changes it? - What are the transaction boundaries and isolation level?
- Do schema constraints or application rules affect whether more than one claim can be recorded?
- Which PostgreSQL server version is running? The cited explanations describe behavior documented in PostgreSQL 16 and 17.
Without those details, the query pattern identifies a plausible cause and a queue-oriented fix, but cannot establish that it is the cause of a particular duplicate claim.
Quick Recap
Best Value
Rank #3
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
