SELECT FOR UPDATE & SKIP LOCKED — Row Locks and the Postgres Job Queue
SELECT ... FOR UPDATE turns an ordinary read into a read-and-reserve: it takes a row-level exclusive lock on every row it returns, held until the transaction ends. It is the explicit, pessimistic escape hatch from MVCC, whose snapshot reads never block and therefore never stop two transactions from reading the same row and both acting on it. SKIP LOCKED then changes only the contention policy — instead of waiting for a locked row, a worker skips it and claims the next one, which is exactly what turns a naive job queue with a concurrency of one into a queue that scales with worker count.
Key Components
SELECT ... FOR UPDATE- A locking clause that reads rows "as though for update": it takes a row-level exclusive lock on every returned row, held until the transaction commits or rolls back, blocking other transactions from locking, modifying, or deleting that row.
SKIP LOCKED- A contention policy on the locking clause: if a candidate row is already locked by another transaction, the scan silently skips it and moves to the next one, instead of waiting.
NOWAIT- The opposite contention policy: if a candidate row is already locked, the statement fails immediately with an error (SQLSTATE
55P03) instead of waiting or skipping. - Row-level lock strength
- Postgres has four row-lock modes —
FOR KEY SHARE,FOR SHARE,FOR NO KEY UPDATE, andFOR UPDATE— ordered weakest to strongest, each conflicting with a different subset of the others. - EvalPlanQual
- The re-check Postgres runs under READ COMMITTED when a blocked locking statement's target row was changed by the transaction it was waiting on: it re-evaluates the
WHEREclause against the new row version before deciding whether to proceed or skip.
Concrete Example
A single-statement job-queue claim. A WITH query does the locking and picks candidates; the outer UPDATE marks them and hands the payload back — one round trip, no window where a row is locked but not yet marked as claimed:
WITH claimed AS (
SELECT id
FROM jobs
WHERE status = 'pending'
AND run_after <= now()
ORDER BY priority DESC, run_after
FOR UPDATE SKIP LOCKED
LIMIT 10
)
UPDATE jobs j
SET status = 'running', started_at = now(), attempts = attempts + 1
FROM claimed c
WHERE j.id = c.id
RETURNING j.id, j.payload;Every clause here is load-bearing:
FOR UPDATEsits inside theWITHquery, not the outer statement. A locking clause on the outer query does not reach into aWITHquery it references — locking has to happen where the candidates are actually selected.LIMIT 10, notLIMIT 1. Locking stops once enough rows satisfy the limit, so batching bounds the lock set while amortizing round trips across workers.ORDER BYruns beforeFOR UPDATE. It gives FIFO/priority semantics — a skipped row does not break the order, a worker simply gets the next unclaimed row in line.- Marking
status = 'running'in the same statement. The lock disappears the instant the transaction commits; without a durable status flag, the next poll would re-claim the same row. The lock provides mutual exclusion, the status column provides the state — both are required.
Visual Model
Picture two workers polling the same jobs table for the next thing to do. Both run the exact same query, ordered the same way, so both reach for the exact same row first. Row-level locking decides what happens at the instant their queries collide — and the clause you choose decides whether that collision means waiting, failing, or moving on. Step through the race below, then compare the three outcomes side by side.
Loading…
The three outcomes side by side, as a quick reference:
| Policy | Row is already locked → | Use when |
|---|---|---|
| (default) wait | block until the holder commits/rolls back | you need this specific row — a bank transfer, an inventory decrement |
NOWAIT | raise 55P03 lock_not_available immediately | you'd rather fail fast than pile up waiting connections |
SKIP LOCKED | skip it, return the next candidate | any row will do — queues, work distribution, batch claiming |
One scope limit applies to all three: NOWAIT and SKIP LOCKED only change what happens to the row-level lock. The ordinary table-level ROW SHARE lock is still taken the normal way, so either policy will still block behind a concurrent ALTER TABLE or VACUUM FULL holding an exclusive table lock.
Deeper — Edge Cases & Gotchas
The four lock strengths and how they conflict
Postgres has four row-level modes, weakest to strongest. The two "key" variants exist almost entirely so foreign-key checks don't block ordinary updates:
| Mode | Blocks | Does not block | Typical origin |
|---|---|---|---|
FOR KEY SHARE | FOR UPDATE, DELETE, key-changing UPDATE | FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE | FK check on the parent row |
FOR SHARE | UPDATE, DELETE, FOR UPDATE, FOR NO KEY UPDATE | FOR SHARE, FOR KEY SHARE | "read this and keep it stable" |
FOR NO KEY UPDATE | UPDATE, DELETE, FOR UPDATE, FOR SHARE | FOR KEY SHARE | any UPDATE not touching a key column |
FOR UPDATE | everything, including itself | — | DELETE; key-column UPDATE; explicit FOR UPDATE |
The shape to remember: it's a staircase. FOR UPDATE conflicts with all four — which is why two FOR UPDATEs on one row always serialize, the fact the job-queue pattern depends on — while FOR KEY SHARE conflicts only with FOR UPDATE. That's why UPDATE users SET last_seen = now() doesn't block a concurrent insert into a table referencing that user: the insert needs only FOR KEY SHARE, the update takes only FOR NO KEY UPDATE, and those two never conflict.
Locks are released only at transaction end (or rollback to a savepoint) — never per statement, and there is no limit on how many rows one transaction can lock. The lock lives in the row's tuple header on disk, so a pure SELECT FOR UPDATE can dirty pages and generate WAL.
READ COMMITTED vs REPEATABLE READ on a locked row
This is the subtlest part of the topic, and it follows directly from READ COMMITTED taking a fresh snapshot per statement. When a statement hits a row a concurrent transaction has locked, it waits, and on commit re-evaluates its WHERE clause against the row's new version (EvalPlanQual). If the new version still matches, it proceeds against it. If it no longer matches, the row is silently skipped — even if the row satisfied the condition both before and after, just not at the exact moment the lock was taken:
BEGIN;
UPDATE website SET hits = hits + 1;
-- from another session: DELETE FROM website WHERE hits = 10;
COMMIT;
-- the DELETE has no effect, even though hits = 10 both before and after the UPDATEREPEATABLE READ handles the same collision differently: since it cannot re-read a newer row version without breaking its own snapshot, it aborts instead of re-checking:
ERROR: could not serialize access due to concurrent update -- SQLSTATE 40001The contract is the same retry loop used throughout Postgres's concurrency control: catch 40001, abort, retry the whole transaction from the top — on retry, the other transaction's change is already part of the new snapshot. Interview framing: READ COMMITTED trades a stable view for liveness (re-check and continue); REPEATABLE READ trades liveness for a stable view (abort and retry).
The gotchas
ORDER BYresults can come back out of order. Under READ COMMITTED, sorting happens before the lock wait, so the ordering column can change while a row is blocked. Wrapping the whole query inFOR UPDATEbefore sorting fixes it, at the cost of locking every row of the table — worth it only if concurrent updates to that column are actually expected.- Rows can be locked but not returned. A row that matched at snapshot time gets locked even if it no longer matches by the time it would be returned. The lock set is a superset of the result set.
OFFSET-skipped rows are still locked.LIMITbounds the lock set;OFFSETdoes not — paginating withOFFSET ... FOR UPDATElocks every row up to and including the current page.- The clause is illegal with
GROUP BY,HAVING,DISTINCT, aggregation, or anyUNION/INTERSECT/EXCEPTinput or output — the output rows can no longer be mapped back to individual table rows.
SKIP LOCKED on a query that must see every matching row — a balance sweep, an inventory total, a reconciliation job.
-- WRONG: silently undercounts if any account row is locked elsewhere
SELECT SUM(balance) FROM accounts
WHERE status = 'active'
FOR UPDATE SKIP LOCKED;SKIP LOCKED returns a deliberately incomplete result set. Any row another transaction has locked is simply missing from the sum — no error, no warning, just a wrong total that looks correct. SKIP LOCKED is correct exactly when "any available row will do," and a total over every row is the opposite of that requirement.
The job-queue failure mode
If a worker crashes mid-job after committing status = 'running', the lock disappears at commit but the row is now stranded — no lock protects it, but its status says it's taken. The standard fix is a reaper that resets rows where status = 'running' AND started_at < now() - interval '5 minutes', plus an attempts cap routing repeat offenders to a dead-letter state. This is why real job rows need a heartbeat or lease column, not just a boolean.
Relation to deadlocks
FOR UPDATE is pessimistic locking, so it inherits pessimistic locking's hazard: transactions locking the same rows in different orders can deadlock, fixed the same way — a consistent global lock order, most simply ORDER BY id so every transaction walks rows in the same direction. SKIP LOCKED is interesting here because it structurally removes the hold-and-wait condition for queue workers: a worker never waits on a row lock at all, so a wait-for cycle among workers can't form. A SKIP LOCKED queue is deadlock-free by construction.
See Also
Two-Phase Locking (2PL) FOR UPDATE's "lock on read, release at commit" behavior is Postgres's strict-2PL half; 2PL is the growing/shrinking-phase theory this practice implements. Deadlocks — Detection, Prevention & the Coffman Conditions Pessimistic row locking can deadlock when transactions lock rows in different orders — the same hazard fixed by consistent lock ordering, and the one SKIP LOCKED queues avoid by construction. Isolation Levels & Read Anomalies Why FOR UPDATE behaves differently under READ COMMITTED (re-check via EvalPlanQual) versus REPEATABLE READ (abort with 40001) traces straight back to each level's snapshot rules. MVCC — How Postgres Implements Multi-Version Concurrency Control FOR UPDATE exists because MVCC's snapshot reads never block — it's the explicit opt-out for the one case where you need a read to reserve, not just observe. Advisory Locks — Application-Level Coordination Through the Database Both are pessimistic locks Postgres arbitrates, but advisory locks guard application-invented integers, not table rows — useful when there's no row to lock at all. Optimistic vs Pessimistic Locking — Choosing by Conflict Rate and Window Length FOR UPDATE is the pessimistic answer to the lost-update race; this entry lays out when a version-column compare-and-swap wins instead. SQL Fundamentals — Joins, Aggregations, Subqueries & CTEs The job-queue claim query depends on knowing that a locking clause on the outer statement doesn't reach into a referenced WITH query — a CTE-scoping rule covered there in full.Sources consulted
- PostgreSQL Documentation — Explicit Locking (13.3. Row-Level Locks) — cited in source research
- PostgreSQL Documentation — SELECT (The Locking Clause) — cited in source research
- PostgreSQL Documentation — Transaction Isolation (13.2. Read Committed / Repeatable Read) — cited in source research
Test Yourself
Two transactions both run a plain SELECT balance ... (no locking clause) under READ COMMITTED, then each computes a new balance and writes it back. What actually stops the classic lost-update race here?
A job-claim query wraps SELECT ... FOR UPDATE SKIP LOCKED inside a WITH clause, and an outer UPDATE uses that CTE's results. A developer moves FOR UPDATE SKIP LOCKED to the outer statement instead. What happens?
Ten workers poll a jobs table every 100ms using FOR UPDATE NOWAIT instead of SKIP LOCKED. What would you expect for throughput and error rates as contention increases, compared to SKIP LOCKED?