Databases
Part 4 of 6 · MVCC & isolationRow Locks: SELECT FOR UPDATE
SELECT FOR UPDATE materializes the read set under SI so concurrent writers wait instead of write-skewing. Know NOWAIT, SKIP LOCKED, lock duration, and deadlock order.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Overview
MVCC lets ordinary SELECT avoid blocking writers. The moment a decision depends on rows still being those rows, you need a lock, SSI, or a constraint. SELECT … FOR UPDATE is the pessimistic tool: take exclusive tuple locks on the read set so concurrent writers wait instead of write-skewing.
It is also the workhorse behind inventory reservation and job-queue claim — especially with SKIP LOCKED. Senior answers name the wait policy, the lock duration, the deadlock order, and the hole: locking existing rows does not lock phantoms.
Why FOR UPDATE wins on a small hot set
SSI retries are cleaner when invariants are scattered. When two doctors or one SKU are the whole world, blocking the row is cheaper than abort storms.
| Path | Concurrent writer | Skip / fail-fast | Best for |
|---|---|---|---|
| Plain SI read then UPDATE | Write skew on disjoint rows | n/a | Never, for multi-row rules |
| SSI + retry | Abort 40001 | n/a | Many invariants, low contention |
| FOR UPDATE (winner, tiny hot set) | Waits on the locked rows | NOWAIT errors; SKIP LOCKED omits | Doctors, inventory row, guard row |
| Table lock | Everyone waits | Brutal | Almost never in OLTP |
- 1
SELECT qty → UPDATE another row
Check-then-act under SI
Two workers both see available. Each updates a different row or inserts a child. No ww-conflict. Double-book. This is the hole SSI also exists to close.
- 2
BEGIN → locks held until COMMIT
Winner for a small read set: FOR UPDATE, ordered
SELECT … WHERE id IN (…) ORDER BY id FOR UPDATEthen decide, then write. Peer waits, re-reads, sees the truth. Canonical order avoids the circular wait. - ?
SKIP LOCKED for queues; NOWAIT for fail-fast
Workers claiming jobs must not block each other — omit locked rows. Money that cannot be skipped should not use SKIP LOCKED. If waiting is a bug, NOWAIT. If SSI abort rate is low, skip locks entirely.
Lock modes
FOR UPDATE. Exclusive lock on each returned row. Blocks UPDATE, DELETE, and other FOR UPDATE / FOR NO KEY UPDATE on that row. In Postgres, FOR NO KEY UPDATE is slightly weaker (allows FOR KEY SHARE). Ordinary SELECT still sees committed versions — readers without FOR * do not take these locks.
FOR SHARE / FOR KEY SHARE. Shared lock. Concurrent readers with SHARE succeed. Exclusive updaters wait. Use when many sessions must prevent a delete/update but do not need to be the unique writer. FOR SHARE is not SSI SIREAD bookkeeping.
Locks live until transaction end (COMMIT / ROLLBACK), not until the statement ends — as long as you are in a real transaction. Autocommit SELECT FOR UPDATE takes and releases in one statement: useless for a later UPDATE.
Wait policies
| Clause | If the row is already locked |
|---|---|
| (default) | Wait until grant (or deadlock / lock_timeout) |
NOWAIT | Error immediately (lock_not_available) |
SKIP LOCKED | Omit that row from the result; do not wait |
SKIP LOCKED is how you build a worker-pull queue without a broker. It is not a correctness tool for “every qualifying row must be considered.” A reservation that skips the last seat because another txn holds it will think stock is empty.
lock_timeout. Fail the wait after N ms instead of sitting forever. Complements NOWAIT when you are willing to wait a little.
MVCC interaction
The lock attaches to the tuple version your snapshot found.
- Another session updating that primary key waits (or you wait on their uncommitted version, then you lock the new one — Postgres
SELECT FOR UPDATEunder RC can follow the chain). - An INSERT of a new row that also matches
WHERE on_call = trueis not locked by your PK-style FOR UPDATE. That is a phantom. Gap locks (InnoDB) or SSI predicate locks / EXCLUDE close it. See phantoms vs write skew.
Under REPEATABLE READ / SI, FOR UPDATE still helps write skew on existing rows: the peer blocks, then runs its logic against a world where your write committed (or you still hold the snapshot and SSI would be the other fix). Under READ COMMITTED, each statement is a new snapshot: FOR UPDATE may lock rows that were invisible earlier in the same txn. Do not mix “I already counted” with RC without re-reading under the lock.
FOR UPDATE OF table_alias (Postgres) locks only that table in a join — avoid locking the whole join tree.
Inventory reserve
BEGIN;
SELECT id, qty FROM inventory
WHERE product_id = $1 AND qty >= $2
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- if no row: out of stock or all locked (SKIP LOCKED cannot tell them apart)
UPDATE inventory SET qty = qty - $2, reserved = true WHERE id = $id;
COMMIT;Prefer a single-row atomic form when one SKU is one row:
UPDATE stock SET qty = qty - $1 WHERE id = $2 AND qty >= $1 RETURNING qty;Zero rows = sold out. No check-then-act. Multi-SKU carts: lock ids in sorted order in one FOR UPDATE, or use SSI.
Job claim (queue)
UPDATE jobs
SET status = 'processing', worker_id = $1, started_at = now()
WHERE id = (
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY priority DESC, created_at
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;Inner SELECT claims the first unlocked queued job. Other workers skip it. Needs an index that matches WHERE status = 'queued' ORDER BY priority DESC, created_at or you seq-scan and lock piles of rows.
Stale claims: a sweeper UPDATE … SET status = 'queued' WHERE status = 'processing' AND started_at < now() - interval '5 minutes'. Processing must be idempotent.
Sequence
- 1
Worker
Step 1 – BEGIN
- 2
Worker → Database
BEGIN
- 3
Worker
Step 2 – lock first free row, skip already locked
- 4
Worker → Database
SELECT id, qty FROM inventory WHERE qty > 0 FOR UPDATE SKIP LOCKED ORDER BY id LIMIT 1
- 5
Database
Step 3 – locked row in this txn
- 6
Database → Worker
id 42 qty 5
- 7
Worker
Step 4 – apply reservation
- 8
Worker → Database
UPDATE inventory SET qty = qty - 1 WHERE id = 42
- 9
Database
Step 5 – one row updated
- 10
Database → Worker
UPDATE 1
- 11
Worker
Step 4b – empty means out of stock or all skipped
- 12
Worker → Worker
retry later
- 13
Worker
Step 6 – COMMIT releases the tuple lock
- 14
Worker → Database
COMMIT
Lesson map
Row Locks: SELECT FOR UPDATE
SELECT FOR UPDATE materializes the read set under SI so concurrent writers wait instead of write-skewing. Know NOWAIT, SKIP LOCKED, lock duration, and deadlock order.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB c["Worker"] db["Database"] c -->|BEGIN| db c -->|SELECT id, qty| db db -->|id 42 qty 5| c c -->|UPDATE inventory| db db -->|UPDATE 1| c c -->|COMMIT| db
Deadlocks
T1 locks row 1, T2 locks row 2, T1 wants 2, T2 wants 1 → circular wait. Postgres aborts one (deadlock detected). InnoDB similar.
Avoid: always ORDER BY id (or any total order) before FOR UPDATE. Acquire all needed rows in one statement. Keep the txn short after the lock. Timeouts for fail-fast.
Two-phase: lock, then call an HTTP API, then update — you hold row locks across the network. That is how queues become outages.
Isolation, indexes, dialects
RC vs RR. RC: new snapshot per statement; FOR UPDATE duration is still end-of-txn, but the set can grow. RR/SI: set of visible rows is stable; write skew on unlocked disjoint rows remains. SERIALIZABLE: you may not need FOR UPDATE; you need a retry loop instead.
Indexes. SKIP LOCKED + LIMIT 1 without an index locks a seq-scan’s worth of rows (or at least visits them). Cover the WHERE and ORDER BY.
Postgres: FOR UPDATE OF alias, FOR NO KEY UPDATE, FOR KEY SHARE, SKIP LOCKED, NOWAIT.
InnoDB: locking reads; SKIP LOCKED from 8.0; gap/next-key behavior on RR means FOR UPDATE on a range locks gaps, not just found rows — more phantom protection, more blocking. See isolation levels.
Oracle: FOR UPDATE NOWAIT / SKIP LOCKED (12c+). SQL Server: UPDLOCK hint; READPAST is the skip analog (no SKIP LOCKED keyword until recent versions — do not paste Postgres SQL).
Connection pools: set default_transaction_isolation to RC for most workers, then BEGIN + explicit FOR UPDATE for claim paths. SERIALIZABLE as a pool default multiplies abort storms. Export lock wait time, deadlock count, and SKIP LOCKED empty-vs-busy if you can tell them apart (often you cannot — log “no row” as a combined metric).
A sweeper that unlocks stale reservations (reserved_at < now() - interval '5 minutes') is part of the lock design, not an afterthought. Claims must be idempotent or the sweeper double-processes.
FOR NO KEY UPDATE (Postgres) is the right exclusive mode when you will change non-key columns and still want concurrent FOR KEY SHARE (FK inserts to this row) to proceed. Reaching for full FOR UPDATE on a parent row that children reference is how “reserve inventory” deadlocks with “insert order_line.” Prefer the weakest lock that still serializes your write.
In-memory locks (run this)
Plain objects: a heap, a lock map, wait policies. I/O contract: lock requests in; granted / skipped / nowait-error / deadlock out.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Deep dive · FOR UPDATE OF, KEY SHARE, and join locking
Postgres FOR UPDATE OF t in FROM a JOIN b locks only t’s rows — use it so a lookup table in the join does not get exclusive locks. FOR KEY SHARE lets INSERTs with FKs to this row proceed while still blocking DELETE / key updates — the right mode for “do not delete this parent while I insert a child.” InnoDB locking reads on RR take next-key locks: the gap before the next index key is locked, so the phantom story differs from Postgres SI + row locks. Metrics: pg_locks, pg_stat_activity.wait_event, deadlock counts. SKIP LOCKED hides wait time (no back-pressure); watch skipped-empty vs truly-empty in the app.
Interview Q&A
What does SELECT FOR UPDATE lock, and until when?
Answer
Each row the SELECT returns, in exclusive mode. Held until COMMIT or ROLLBACK of that transaction. Other FOR UPDATE / UPDATE / DELETE on those rows wait (or NOWAIT / SKIP LOCKED). Plain SELECT still reads MVCC versions. Autocommit makes the lock useless for a follow-up write.
FOR UPDATE vs FOR SHARE vs FOR KEY SHARE?
Answer
FOR UPDATE: exclusive, you will modify. FOR SHARE: shared, many readers, block exclusive changers. FOR KEY SHARE (Postgres): weaker shared, allows some non-key updates and FK inserts. None of these is an SSI SIREAD lock. SHARE does not close write skew if each txn shares different rows and then updates a third.
NOWAIT vs SKIP LOCKED vs lock_timeout?
Answer
NOWAIT: error if any target row is busy — fail-fast, caller retries or gives up. SKIP LOCKED: omit busy rows — worker queues. lock_timeout: wait up to N then error. SKIP LOCKED on a money invariant that must consider every row will skip the only remaining seat and report empty.
Walk a job-queue claim with SKIP LOCKED.
Answer
UPDATE jobs SET status = processing WHERE id = (SELECT id FROM jobs WHERE status = queued ORDER BY priority, created_at FOR UPDATE SKIP LOCKED LIMIT 1) RETURNING *. Each worker locks a different queued row without waiting. Index the filter + order. Re-queue stale processing rows with a timeout. Work must be idempotent.
How do two transactions deadlock on row locks, and how do you stop it?
Answer
T1: lock id=1 then id=2. T2: lock id=2 then id=1. Circular wait; detector aborts one. Fix: ORDER BY id in one FOR UPDATE for the whole set; never lock after a network call; keep txns short. Canonical order is Coffman “break circular wait.”
Does FOR UPDATE on rows matching a predicate prevent phantom inserts?
Answer
No under Postgres SI/RC: you locked found tuples. A new row that matches on_call = true can still be inserted. InnoDB RR next-key locks often do lock the gap. SSI predicate locks or UNIQUE/EXCLUDE are the other closures. See phantoms vs write skew.
How does isolation level change FOR UPDATE?
Answer
RC: each statement’s snapshot is new; you may lock rows that did not exist for statement 1. RR/SI: visible set is stable; FOR UPDATE serializes writers of those rows but not disjoint write skew unless you locked the whole decision set. SERIALIZABLE: prefer retry over locking everything; mixed FOR UPDATE + SSI is allowed but know why you added the lock.
Why does SKIP LOCKED without an index hurt?
Answer
The planner seq-scans, visiting (and often locking or skipping) many rows to find one free. Throughput collapses and you lock extras you did not need. Match WHERE + ORDER BY with a btree. FOR UPDATE OF in joins similarly limits lock fan-out.
When is an atomic UPDATE … WHERE qty ≥ n better than FOR UPDATE?
Answer
Single-row invariant: one statement, no check-then-act, no extra lock round-trip. FOR UPDATE wins when the decision reads several rows (two doctors, a cart of SKUs) before writing. SSI wins when those sets vary and contention is low.
What is the lock_timeout / idle-in-transaction operational pair?
Answer
lock_timeout stops unbounded waits. idle_in_transaction_session_timeout stops sessions that already took FOR UPDATE and then went to lunch — they hold tuples and VACUUM’s horizon. Row locks plus long txns are a fleet-wide stall.
Pitfalls
Write the nested UPDATE … WHERE id = (SELECT … FOR UPDATE SKIP LOCKED LIMIT 1) job claim. Then write a two-SKU cart that locks ORDER BY sku_id. Draw T1/T2 locking 1-then-2 vs 2-then-1. List one case for NOWAIT and one case where SKIP LOCKED would be a product bug.
Go Deeper
Cluster: MVCC hub · Isolation levels · SSI vs SI · VACUUM · Phantoms vs write skew
One-line takeaway: FOR UPDATE serializes the read set you actually found — order the locks, pick SKIP LOCKED only for queues, and do not confuse row locks with phantom or SSI coverage.