How does taking an explicit exclusive row lock when reading — `SELECT ... FOR UPDATE` — prevent a lost update, and what does that approach cost?

## What the lock does An ordinary read in a multiversion engine takes no lock — it reads a version of the row and moves on, which is precisely why a subsequent write can be based on stale data. Adding `FOR UPDATE` to the read changes it into a **locking read**: the engine takes the same exclusive row lock it would take for an `UPDATE`, and holds it until the transaction ends. The pattern is: ``` BEGIN; SELECT balance FROM accounts WHERE id = 42 FOR UPDATE; -- 100, row now locked -- application logic, arbitrary complexity UPDATE accounts SET balance = 90 WHERE id = 42; COMMIT; -- lock released here ``` A second transaction executing the same block blocks at its `SELECT ... FOR UPDATE` until the first commits, and then reads the *new* committed value (90) rather than the stale 100. The read-modify-write pairs are serialized, so no update is lost. This is **pessimistic** concurrency control: you assume a conflict and pay for exclusion up front. ## Why it beats an atomic in-place update sometimes The in-place `SET x = x - 10` form is cheaper, but it only works when the new value is arithmetic on the old one. A locking read lets arbitrary application logic sit between the read and the write: pricing rules, validation against other tables, a state machine, or a decision that spans several rows locked in the same transaction. That is the case for using it. ## The three conditions people get wrong 1. **Same transaction.** Row locks are released at commit or rollback. Reading `FOR UPDATE`, committing, then updating in a new transaction protects nothing. In connection-pooled or auto-commit contexts this is an easy accident: if auto-commit is on, the lock is released the instant the `SELECT` returns. 2. **All writers participate.** The lock only excludes other *lock requesters*. A code path that reads without `FOR UPDATE` and then updates will still overwrite — its `UPDATE` will block on your lock, but afterwards it writes the constant it computed from its stale read. Pessimistic locking is a protocol; it works only if every writer obeys it. 3. **Shared locks are not enough.** A shared/read lock (`FOR SHARE`) lets both transactions hold it simultaneously. Each then tries to upgrade to exclusive at write time, each waits for the other to release, and the engine kills one with a deadlock error. Use the exclusive form for read-modify-write. A fourth subtlety: locking rows that **do not exist yet** protects nothing. `SELECT ... FOR UPDATE` on a predicate that currently matches no rows locks nothing at all, so a concurrent insert can still appear. Enforcing uniqueness of something not yet inserted needs a unique constraint or a lock on a parent row, not a locking read. ## What it costs **Serialization on hot rows.** Every writer of the same row queues. Maximum throughput for that row becomes 1 / (time the lock is held). If your transaction holds the lock for 50 ms, that row supports 20 writes per second no matter how many application instances you run. Keep the locked section short: take the lock as late as possible, do slow work before it, and never hold it across an HTTP call, a message publish, or user think-time. **Deadlocks.** Two transactions that lock the same set of rows in different orders will deadlock; the engine detects the cycle and aborts one with an error. The mitigation is a consistent lock ordering (for example, always lock account rows in ascending id order) plus a retry on the deadlock error. Expect a nonzero deadlock rate and instrument it. **Lock-wait pileups.** Under a load spike, waiting transactions occupy connections. A pool of 50 connections all blocked on one row means the whole service stalls, not just that row's callers. Set a lock-wait timeout and treat it as a load-shedding signal. **Held locks and long transactions.** A transaction that takes a lock early and does other work for seconds blocks writers that entire time. Long-lived locking transactions are among the most common causes of production write stalls. ## Fail-fast and work-queue variants Most engines offer modifiers on the locking read: - **`NOWAIT`** — instead of queueing, raise an error immediately if the row is locked. Good for interactive paths where a fast "someone else is editing this" beats a 30-second stall. - **`SKIP LOCKED`** — skip rows other transactions hold and return the rest. This is the standard way to build a work queue: N workers each claim different rows without contending. ## When to prefer optimistic control instead If the gap between read and write includes user think-time (load a form, edit, submit minutes later), you cannot hold a database lock across it — that would pin a transaction and a connection for the whole time. Use a version column and detect the conflict at write time. Pessimistic locking is for short, server-side critical sections; optimistic control is for long or stateless ones. Under very low contention, optimistic control also wins on throughput because it never blocks.