Optimistic vs Pessimistic Locking — Choosing by Conflict Rate and Window Length

Concrete Example

Both strategies exist to close the same three-line gap:

t0   app:  SELECT balance FROM accounts WHERE id=42   -> 1000
t1   app:  compute in Go/Python: 1000 - 100 = 900
t2   app:  UPDATE accounts SET balance = 900 WHERE id=42

Between t0 and t2 the row is unguarded. A second request runs the same three steps, both read 1000, both write 900, and one withdrawal disappears.

Pessimistic — lock before you look

SELECT ... FOR UPDATE takes a row-level exclusive lock at read time and holds it until COMMIT:

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;  -- others block HERE
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;                                                  -- lock released

Postgres's row-lock conflict matrix shows exactly which combinations wait:

FOR KEY SHARE is what a foreign-key check takes — this is why an INSERT into a child table does not block an unrelated UPDATE on the parent row.

Optimistic — do not lock, prove nothing changed

A version column plays the role of Kung & Robinson's validation phase:

ALTER TABLE accounts ADD COLUMN version integer NOT NULL DEFAULT 1;

-- read phase: no lock, no open transaction, no held connection
SELECT balance, version FROM accounts WHERE id = 42;   -- -> 1000, 7

-- ... application computes 900. Minutes may pass. ...

-- validation AND write, in one statement
UPDATE accounts
   SET balance = 900, version = version + 1
 WHERE id = 42 AND version = 7;

1 row affected means nobody moved and validation passed. 0 rows affected means someone committed since the read, validation failed, and the caller must discard and retry. The AND version = 7 predicate is the validation phase, at the cost of one integer comparison and no lock.

Real code needs the retry loop built in:

func withdraw(ctx context.Context, db *sql.DB, id int64, amt int) error {
    const maxAttempts = 5
    for attempt := 0; attempt < maxAttempts; attempt++ {
        var balance, version int
        err := db.QueryRowContext(ctx,
            `SELECT balance, version FROM accounts WHERE id = $1`, id,
        ).Scan(&balance, &version)
        if err != nil {
            return err
        }

        if balance < amt {
            return ErrInsufficientFunds
        }

        res, err := db.ExecContext(ctx,
            `UPDATE accounts SET balance = $1, version = version + 1
              WHERE id = $2 AND version = $3`,
            balance-amt, id, version)
        if err != nil {
            return err
        }

        if n, _ := res.RowsAffected(); n == 1 {
            return nil
        }
        sleepWithJitter(attempt)   // 0 rows: someone else won. redo.
    }
    return ErrTooManyRetries
}

There is no BEGIN. Two autocommit statements with a gap between them, and the gap is safe — no transaction is held open and no connection is pinned, so the function stays correct even if the statements run minutes apart. In ORMs this is usually built in: Jakarta Persistence's @Version and SQLAlchemy's version_id_col both emit this exact pattern and raise on a zero rowcount.

The third option — do not read at all

If the new value is a function of the old value rather than a decision based on it, express it as a delta and the race disappears:

UPDATE accounts SET balance = balance - 100
 WHERE id = 42 AND balance >= 100;
-- 0 rows = insufficient funds, with no window between check and debit

One statement, atomic under the row lock the database takes anyway. No version column, no FOR UPDATE, no retry loop needed — reach for this first, before either form of locking.

Visual Model

Both approaches solve the same read-modify-write gap, but they bet on opposite answers to one question: will another transaction enter that gap before you finish? Pessimistic locking bets yes and pays a lock's cost on every access to prevent it. Optimistic locking bets no, pays nothing on the happy path, and instead proves at write time that nobody else got there first. The load-bearing fact behind every choice below: the hazard is not the write, it is the duration of the gap — measured in application time, not database time.

Follow the same two transactions, A and B, through both approaches below — watch exactly where each one catches the conflict.

Choosing between them

Work down this list. The first rule that fires decides it.

1. Does the conflict window span human think time (a form left open for minutes)? → Optimistic, always — a transaction and its pooled connection cannot stay open across a coffee break.
2. Can the update be expressed as an atomic in-place statement? → do that instead of either lock.
3. Is contention on a single row high (last unit of stock, a global counter)? → Pessimistic — optimistic degrades badly here: everyone computes, one commits, the rest discard the work.
4. Is the work between read and write expensive to redo (a paid API call, a long computation)? → Pessimistic, or restructure so the expensive part sits outside the transaction.
5. Otherwise — rare conflicts, cheap redo. → Optimistic. This is most CRUD.

One-line crossover: optimistic wins while P(conflict) × cost(retry) stays below cost(waiting). Both sides are measurable — ship optimistic with a retry counter in your metrics and read the conflict rate after a week.

It depends on the conflict rate and how long the read-to-write window is. Pessimistic pays a fixed cost on every access to avoid a rare failure, and it holds a transaction — and therefore a pooled connection — open for the whole window, so it does not work at all when a human is in the loop. Optimistic pays nothing on the happy path but discards the work on conflict, so it degrades under contention. Rare conflicts or a long window: optimistic. Hot contended row or expensive-to-redo work: pessimistic. And first check whether the update can be expressed atomically in one statement, because then neither is needed.— the interview answer, compressed

Deeper — Edge Cases & Gotchas

Why the version column is airtight — the EvalPlanQual mechanism

Two writers both read version = 7 and both fire the UPDATE. Exactly one wins, although neither took an explicit lock. A bare UPDATE is not lock-free internally — it takes a FOR NO KEY UPDATE row lock on every row it touches. Writer A locks the row, sets version = 8, and commits. Writer B's UPDATE blocked on A's lock; once A commits, B unblocks and, under READ COMMITTED, Postgres runs EvalPlanQual: it fetches the newest committed version of the row and re-evaluates the WHERE clause against it. The newest row has version = 8, the predicate demands version = 7, and the row drops out of the update set — B reports 0 rows. The version column converts the database's internal row lock into an application-visible compare-and-swap. Sequence worth memorizing: block → winner commits → re-fetch newest row → re-evaluate predicate → 0 rows.

How the loser finds out depends on the isolation level

Under READ COMMITTED the losing UPDATE does not abort and raises no exception — it returns rowcount = 0 and the program continues silently. Under REPEATABLE READ there is no EvalPlanQual re-check, because that would mean looking at a row committed after the transaction's snapshot began — a promise REPEATABLE READ has already made not to break. Postgres instead aborts and raises SQLSTATE 40001.

The second column matters more than it looks: under REPEATABLE READ the snapshot is poisoned — every value read in that transaction is frozen at a moment now invalidated — so re-running only the UPDATE would validate against stale reads. See isolation levels for the full anomaly-by-level picture.

Why a timestamp is a bad version token

Three failure modes, and the third is the one that actually happens in production. (a) The clock is coarser than the write rate — MySQL's default DATETIME stores whole seconds, so two writes in the same second share a token and validation cannot tell them apart. (b) The clock moves backwards across instances under NTP correction, which reintroduces the ABA problem: a value returns to an earlier observed state, so an equality check reports "unchanged" when two changes actually occurred. A monotonic integer counter cannot do this. (c) Something changed the row without bumping the column — a migration, a manual fix, a backfill — so the token stays put while the data moves, and every future validation passes against stale data. A version column is not immune to (c), but a BEFORE UPDATE trigger can enforce version = version + 1 structurally; no equivalent guarantee can be placed on a timestamp a caller sets explicitly.

Livelock under a flash sale

Throughput can collapse to near zero on a hot row with no errors and no slow queries in the logs — this is livelock, not deadlock. Nothing waits, no cycle exists; every transaction runs at full speed, but everyone reads the same token, one wins, and the rest discard their work and re-enter the same race. Every individual statement is fast and succeeds, so the monitoring stack reports a healthy database while useful throughput is zero — doing work and throwing it away looks identical to doing work at every layer that measures queries. Unlike a lock queue, which is roughly FIFO, optimistic retry has no queue: a transaction with a longer compute phase has a wider window to lose in, so it can be repeatedly beaten by faster newcomers. Fixes, in order: move the hot row to pessimistic locking (turns the stampede into a bounded queue); better, remove the read-modify-write entirely (an inventory decrement collapses to one atomic UPDATE ... WHERE stock > 0); and instrument a retry-attempt counter regardless, since it is the only signal that surfaces this failure mode.

The per-row blind spot — write skew and phantoms

A version column protects a row, so it is blind to any invariant defined over a set of rows. Write skew: if "the sum of line items must equal the order total" and transaction A edits item 1 while transaction B edits item 2, each validates perfectly — neither row was touched by anyone else — yet both commit and the total is now wrong. This fails for the same reason it fails under snapshot isolation: the conflict is between rows, not on one. Phantoms: two requests both check "is room 4 free at 3pm?", both find nothing, both INSERT — there is no row to carry a version, so there is nothing to validate against. Fixes all operate above the row: a unique or exclusion constraint pushes the invariant into the database regardless of concurrency; SERIALIZABLE's predicate locks catch exactly these read-write dependencies; or an explicit lock on a parent row (or an advisory lock key, when no natural parent row exists) manufactures a single serialization point for the whole set.

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;  -- lock taken
-- call the payment gateway over the network here
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

What else breaks in production

• The rowcount check is not defensive programming — it is the concurrency control. An UPDATE matching 0 rows is a success to the database driver. Code that ignores the rowcount detects nothing, with no symptom until an audit finds a lost update.
• Retry needs idempotency. The transaction rolled back cleanly, but an external call inside it — a payment charge — did not. Anything with a side effect must move outside the retry boundary or carry an idempotency key.
• Retry needs a cap and jittered backoff. An uncapped retry loop under contention is a self-inflicted denial of service: failures cause retries, retries raise contention, contention causes more failures. Cap at 3–5 attempts, then surface the failure.
• FOR UPDATE on multiple rows without ORDER BY deadlocks when two transactions lock the same set in different orders — see deadlocks.