SELECT FOR UPDATE: Why Eight Workers Do the Work of One

Eight workers poll a job table with SELECT ... LIMIT 1 FOR UPDATE. They finish 49 jobs in six seconds. One worker, alone, finishes the same 49. Twelve workers also finish 49, and spend 65.7 worker-seconds waiting to do it.

A locking clause is the FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE or FOR KEY SHARE you can hang off the end of a SELECT. It tells the executor to take a row-level lock on each row the query returns, so that no other session can change that row until your transaction ends. Everybody's first use of it is the same: read a row, decide something from it, write it back, and use the lock to stop a second session doing the same thing in between.

The belief that follows is that the clause makes concurrent work safe, and that safe work still runs concurrently. It does not. FOR UPDATE is not a flag on the query; it is a node in the plan — LockRows — that takes one lock at a time, on one tuple at a time, in whatever order the plan below it hands rows up. When several sessions run the same query, they walk the same order and arrive at the same first row. One takes it. The rest queue.

The panel below runs that queue. It is a model of ExecLockRows, the executor loop in src/backend/executor/nodeLockRows.c: for each tuple, call table_tuple_lock, and act on what it returns — got it, would block, was updated by someone else. Every worker runs BEGIN; SELECT ... FROM jobs WHERE state = 'ready' ORDER BY id LIMIT 1 FOR UPDATE;, works for a fixed time, sets the rows to 'done', and commits. Six seconds of simulated wall clock.

Start by moving workers from 1 to 12 and watching the hero number refuse to change. Then change the locking clause to FOR UPDATE SKIP LOCKED and move it again.

At the default settings the hero number is 49. Drag worker sessions down to 1: still 49, with nothing blocked at all. Drag it up to 12: still 49, and worker-seconds blocked goes from 41.9 at eight sessions to 65.7 at twelve. Adding workers to this queue adds waiting and nothing else. The ceiling is one transaction per work period — 6000 ms ÷ 120 ms, less the one still in flight when the clock stops — because at any instant exactly one session holds the row at the head of the queue and every other session is behind it.

Now switch the clause to FOR UPDATE SKIP LOCKED. Eight workers finish 392. Twelve finish 588. Worker-seconds blocked is zero, because no session ever waits for another. That is an eight-fold difference from adding two words, which is the size of gap that should make you suspicious: SKIP LOCKED is not a faster way to run the same query. It is a different query, and it answers a different question. FOR UPDATE asks "lock the first ready job." FOR UPDATE SKIP LOCKED asks "lock the first ready job that nobody else is already holding" — and the second question has a different answer for each worker, which is the entire point and also the entire risk.

Two more settings worth reaching before you read on. Set the clause to no locking clause and untick the AND state = 'ready' guard. The hero number stays at 49 — there are still only 49 distinct jobs finished — but the workers performed 392 job runs to get there, so 343 jobs ran twice and nothing in the table records that they did. Tick the guard back on and those duplicates become 343 units of work thrown away instead: the worker did the job, then found it was not allowed to record it. And set the isolation level to REPEATABLE READ with a plain FOR UPDATE: the waiting does not go away — 28.2 worker-seconds of it — but every wait now ends in an error instead of a row. 343 of them.

What a row lock actually is

There is no lock table with a row in it for every locked tuple. Postgres cannot afford one: a single statement can lock millions of rows, and shared memory is fixed at startup. The design document for the mechanism, src/backend/access/heap/README.tuplock, opens with exactly that constraint and describes a two-level scheme.

The first level lives in the tuple. "A tuple is marked as locked by setting the current transaction's XID as its XMAX, and setting additional infomask bits to distinguish this case from the more normal case of having deleted the tuple." The XID is the 32-bit transaction id Postgres hands each writing transaction; xmax is the header field that normally records which transaction deleted or superseded a row version. So a row lock costs one write to a heap page, and it is stored in the same place a deletion would be. This is why SELECT ... FOR UPDATE dirties pages and generates write-ahead log traffic even though it is spelled SELECT — and why it is disallowed on a standby.

That storage answers "who holds it" but not "who is next", and the README is explicit about the consequence: waking on the transaction's XID alone "will release all waiters concurrently, so there would be a race condition as to which waiter gets the tuple, potentially leading to indefinite starvation of some waiters." So there is a second level. A session that must wait takes an ordinary heavyweight lock on the tuple's location first:

LockTuple()
XactLockTableWait()
mark tuple as locked by me
UnlockTuple()

LockTuple() is the queue. It decides who gets the row next, in arrival order, and because "at most one tuple-level lock will be held or awaited per backend at any time" that queue cannot overflow the lock table. This is the piece the simulation reproduces: waiters do not stampede when a holder commits, they file past one at a time. It is also why the convoy is so orderly and so slow. Fair queueing on a contended resource is still queueing.

When two sessions hold compatible locks on the same row — two FOR SHARE readers, say — one XID is not enough, and xmax becomes a MultiXactId: a pointer into a separate structure listing every transaction that holds a lock on that tuple, and with what strength. MultiXactIds are a 32-bit counter of their own, with their own freezing and their own wraparound risk, which is the connection between a heavy FOR SHARE workload and wraparound maintenance.

The lock lands on rows you did not ask for

Set the clause back to FOR UPDATE and look at the readout labelled rows locked, not returned. At the default settings it is non-zero, and it is not a modelling artefact. The SELECT reference says it plainly:

"In addition, rows that satisfied the query conditions as of the query snapshot will be locked, although they will not be returned if they were updated after the snapshot and no longer satisfy the query conditions."

Here is the sequence. Your statement takes its snapshot and sees job 7 in state 'ready'. Another session has it locked, so you wait. That session commits, having set job 7 to 'done'. You are granted the tuple lock, and now the executor has a choice to make about which version of job 7 it is even talking about.

At READ COMMITTED it follows the chain. In nodeLockRows.c the flag TUPLE_LOCK_FLAG_FIND_LAST_VERSION is added only when !IsolationUsesXactSnapshot() — that is, only at READ COMMITTED. The lock is taken on the newest version, the executor sets epq_needed, and the EvalPlanQual machinery — the executor's re-evaluation path, which re-runs the query's qualifier against a row version the snapshot never saw — asks whether the new version still matches state = 'ready'. It does not. The comment on the next line is the whole behaviour in ten words:

if (TupIsNull(slot))
{
    /* Updated tuple fails qual, so ignore it and go on */
    goto lnext;
}

The row is not returned. The lock stays. Row locks are held to the end of the transaction, always, and there is no statement that releases one early. So your session is now holding a row it is not going to process, and the next session queued behind job 7 is waiting on you. That is the cascade you can watch in the timeline: the head of the queue keeps moving, and the tail keeps re-forming one row further along.

Two consequences worth carrying around. First, SELECT ... FOR UPDATE can return fewer rows than the same SELECT without the clause, run at the same instant, and this is not a bug — it is the documented re-check. Code that asserts on the row count will fire in production and never in staging. Second, the set of rows you have locked is not the set of rows you were given, so reasoning about your own lock footprint from your result set is wrong in the direction that causes outages.

The reference adds two more rules that surprise people. LIMIT stops the locking early — "locking stops once enough rows have been returned to satisfy the limit" — but OFFSET does not: "rows skipped over by OFFSET will get locked." A paginated OFFSET 10000 LIMIT 20 FOR UPDATE locks 10,020 rows to hand you twenty. And a locking clause inside a sub-SELECT locks only what the sub-query actually returns to the outer query, which can be far fewer rows than reading the sub-query alone suggests, because the outer query's conditions are pushed down into it.

Why an unrelated INSERT is hanging

"SELECT FOR UPDATE is blocking everything" is almost never literally true — a plain SELECT is never blocked by a row lock, at any strength, because a reader does not need the lock at all. The table-level lock a locking clause takes is ROW SHARE, which conflicts only with EXCLUSIVE and ACCESS EXCLUSIVE. In practice that means DROP TABLE, TRUNCATE, VACUUM FULL and most forms of ALTER TABLE, which take ACCESS EXCLUSIVE, plus one non-DDL case that catches people: REFRESH MATERIALIZED VIEW CONCURRENTLY takes EXCLUSIVE. Your dashboards keep working throughout.

What does hang, mysteriously, is an INSERT into a completely different table. The reason is that there are four row lock strengths, not one, and only some pairs of them conflict. Choose a mode below and see what it stops.

The default pairing is the one that generates support tickets. Session A holds a plain FOR UPDATE on a customer row — perhaps just to serialise an update to that customer's balance. Session B inserts an order that references that customer. The insert has nothing to do with the balance and touches no column session A cares about, and it blocks.

It blocks because a foreign-key check is itself a locking SELECT. In src/backend/utils/adt/ri_triggers.c, the referential-integrity trigger opens the parent table "in RowShareLock mode since that's what our eventual SELECT FOR KEY SHARE will get on it", and then builds this query:

SELECT 1 FROM [ONLY] <pktable> x WHERE pkatt1 = $1 [AND ...]
       FOR KEY SHARE OF x

FOR KEY SHARE exists precisely to be the weakest thing that still works for this: it stops the parent row being deleted or having its key changed out from under the check, and nothing else. Look at its row in the matrix — it conflicts with exactly one mode, FOR UPDATE, and FOR UPDATE is the mode everybody types.

The fix is one phrase. If your transaction is not going to change the row's key — and if it is a primary key, it almost never is — write FOR NO KEY UPDATE. It is the same exclusive lock against other writers, minus the conflict with foreign-key checks, and it is exactly what a plain UPDATE of a non-key column takes anyway. Set the held mode to NO KEY UPDATE in the panel and watch the child insert go through while everything you actually wanted to exclude still waits.

The same matrix explains the last surprise: DELETE takes FOR UPDATE, and an UPDATE takes FOR UPDATE too if it modifies a column with a unique index on it that could be used in a foreign key. Otherwise it takes FOR NO KEY UPDATE. So whether one of your updates blocks another session's insert depends on which columns that update happens to set — which is why the same code path can be fine for months and start blocking the day someone adds a column to the SET list.

What the isolation level changes

Everything above is READ COMMITTED behaviour, which is the default and what almost every application gets. Set the simulation's isolation level to REPEATABLE READ and the shape of the failure changes completely: the same 49 jobs finish, but 343 transactions die with an error instead of quietly losing a row.

The branch is one line of nodeLockRows.c:

case TM_Updated:
    if (IsolationUsesXactSnapshot())
        ereport(ERROR,
                (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
                 errmsg("could not serialize access due to concurrent update")));

IsolationUsesXactSnapshot() is true for REPEATABLE READ and SERIALIZABLE — the levels that freeze one snapshot for the whole transaction, described in isolation levels. Such a transaction is not permitted to lock a version it cannot see, so there is no chain to follow and no re-check to run: it waits for the other session, then raises SQLSTATE 40001. Note the order. It still waits. Raising the isolation level does not turn a lock wait into a fast failure; it turns a slow success into a slow failure.

This is not a reason to avoid REPEATABLE READ. It is a reason that a locking clause and a high isolation level together demand a retry loop that re-runs the whole transaction, because the transaction is now the unit that fails — see writing a correct retry loop. The same is true of the other retryable error: two sessions taking row locks in different orders produce SQLSTATE 40P01, and unlike a serialization failure, that one is available at every isolation level. Set the claim query to ORDER BY random(), the batch size to 3 and the ready pool to 32, and the panel produces 5 deadlocks in six seconds where ORDER BY id produces none — see deadlock detection for what happens in the second those sessions spend waiting before anybody looks for the cycle.

That contrast is the practical rule. Deadlock between row locks needs two things: sessions that lock more than one row, and sessions that lock them in different orders. Removing either one removes the deadlock. A batch claim with a stable ORDER BY has both properties under control; ORDER BY random(), which people reach for precisely to spread workers apart, destroys the second one.

Where SKIP LOCKED stops helping

SKIP LOCKED is the right answer for a queue and the wrong answer for almost everything else, and the documentation says so in one sentence: "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."

Take that literally. The query returns a result that depends on what other sessions happened to be holding at that instant. Two identical queries a millisecond apart return different rows. An aggregate over a SKIP LOCKED sub-query returns a number that was never true. SELECT count(*) ... FOR UPDATE SKIP LOCKED tells you how much work is unclaimed, not how much work exists, and if you graph it as queue depth it will read zero during your busiest minute.

Reach that state in the panel. Set the clause to FOR UPDATE SKIP LOCKED, workers to 12, and drag jobs ready at t=0 down to 8. The workers finish 8 jobs and then poll 2,839 times finding nothing — with the number of ready rows smaller than the number of consumers, every unlocked row is gone and each remaining worker is told, correctly and uselessly, that the queue is empty. A poller that interprets an empty result as "sleep for a second" now sleeps while work sits in the table.

The second boundary is ordering. Priority in a queue is expressed as ORDER BY priority DESC, run_at, and SKIP LOCKED steps over any high-priority row that is currently held. A worker will happily take a low-priority job while a high-priority one is in flight somewhere else, so the guarantee degrades from "highest priority first" to "highest priority that is free". For most systems that is fine. For a system where priority means a deadline, it is a bug that only appears under load.

And there is a boundary neither clause can cross. Both FOR UPDATE and SKIP LOCKED lock rows that exist. Neither one can lock a row that has not been inserted yet, so neither prevents the classic check-then-insert race: two sessions check that no booking exists for room 4 at 10:00, both find nothing, both insert. No row lock can help, because there was no row. The answers there are a unique constraint, a SERIALIZABLE transaction, or an advisory lock on a hash of the key — not a locking clause.

Checking it on a real system

The diagnostic sequence takes about a minute. Start with who is waiting for whom:

SELECT pid, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       now() - xact_start AS xact_age,
       left(query, 70) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND wait_event_type = 'Lock'
ORDER BY xact_age DESC;

wait_event = 'transactionid' means the session is waiting for another transaction to end — it has reached XactLockTableWait() and is holding a place in the queue. wait_event = 'tuple' means it is waiting for LockTuple(), the second-level queue, which tells you there are at least two sessions already queued for the same row. Seeing many sessions on tuple is the signature of the convoy, and it is the difference between "one slow transaction" and "a structural queue".

Then look at the locks themselves. Row locks do not appear in pg_locks as such — remember they live in the tuple header — but the waits do:

SELECT l.pid, l.locktype, l.mode, l.granted,
       l.transactionid, l.relation::regclass, l.page, l.tuple
FROM pg_locks l
WHERE l.locktype IN ('transactionid', 'tuple')
ORDER BY l.granted, l.pid;

An ungranted row with locktype = 'transactionid' and mode = 'ShareLock' is a session waiting on a row lock. An ungranted locktype = 'tuple' names the exact page and tuple offset being fought over, which you can turn back into a row with SELECT * FROM jobs WHERE ctid = '(page,tuple)'. If the same (page, tuple) pair has several waiters, you have found the head of your queue.

Turn on the log line that makes this visible without anyone watching. It is off by default in PostgreSQL 18:

ALTER SYSTEM SET log_lock_waits = on;   -- logs any wait longer than deadlock_timeout
SELECT pg_reload_conf();

Every wait that outlasts deadlock_timeout — 1000 ms by default, which is also the delay before a deadlock is even looked for — then produces a process ... still waiting for ShareLock on transaction ... line naming both sessions and both statements. Counting those lines per hour is the cheapest queue-contention metric there is. Check the default before you rely on it: it is off in PostgreSQL 18 and has already been flipped to on for the next major release, so on a newer server you may find these lines already in your log.

If you chose NOWAIT, that setting will not show you anything, because a failed acquisition is not a wait. PostgreSQL 18 added a second parameter for exactly that case, also off by default:

ALTER SYSTEM SET log_lock_failures = on;
SELECT pg_reload_conf();

Its documentation is narrow and worth quoting, because it tells you what the parameter is for: it controls whether a detailed log message is produced when a lock acquisition fails, and currently, only lock failures due to SELECT NOWAIT is supported. Turn it on for an hour if the 55P03 readout in the panel above describes your application, and you will get the losing statement rather than an error count.

Finally, confirm what the plan is doing rather than what you think you wrote:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM jobs
WHERE state = 'ready'
ORDER BY id
LIMIT 5
FOR UPDATE SKIP LOCKED;

 Limit
   ->  LockRows
         ->  Index Scan using jobs_state_id_idx on jobs
               Index Cond: (state = 'ready'::text)

Three things to read off it. LockRows must sit below Limit and above the scan — that is the node the whole lesson is about, and if it is missing your locking clause did not survive the rewrite (a common casualty of putting the clause in a WITH query, where the reference says it does not apply). The scan under it should be an index scan; a sequential scan means every poll walks the dead rows too, and a queue table accumulates those faster than anything else in your schema, which is where a queue meets index bloat and vacuum. Third, compare Buffers: shared hit against the row count: a claim that returns five rows and touches four hundred buffers is walking dead tuples to find them. Do not read Rows Removed by Filter as that measure — it counts live rows that failed the qualifier, and dead tuples never reach the filter at all, because the visibility check discards them first. In the plan above there is no filter to read: the predicate became an Index Cond. For dead-tuple accumulation, query pg_stat_user_tables for n_dead_tup and last_autovacuum on the queue table directly.

One structural note, because it saves more time than any of the above: a queue table's rows are inserted, updated several times and then deleted, which is the worst possible workload for both the heap and its indexes. Keeping the claim path index-only and the row narrow — no payload column that gets rewritten, a low fillfactor so claims can stay on-page as heap-only tuple updates — is worth more than any amount of tuning to the locking clause.

Two directions from here. If your transactions are ending in errors rather than waits, the question is what a retry has to re-read and what it must never carry over: serialization failures and retry. If they are ending in 40P01, the mechanism that decides which session dies, and why nothing notices for a whole second, is deadlock detection.