Optimistic concurrency - version columns beat blind RMW

Worked first (Chen’s inventory): Eight checkout workers hit the same SKU. Each does the “obvious” app pattern: SELECT qty, decide, UPDATE … SET qty = $new. Under concurrent load the row ends at median 99 instead of 92 — seven of eight decrements silently lost. No error. No deadlock. Just wrong stock. The fix is not “retry harder on the same absolute write.” It is a version column (or equivalent predicate) so a stale write updates zero rows and the app retries from a fresh read.

The lost-update trap (blind RMW)

-- Worker A and B, same row, no version check
SELECT qty FROM inventory WHERE id = 1;   -- both see 100
-- … app think time / network / validation …
UPDATE inventory SET qty = 99 WHERE id = 1;  -- both write 99
-- Final qty = 99, not 98. Second decrement vanished.

Atomic SQL like SET qty = qty - 1 avoids this particular bug because the expression is evaluated under the row’s current value inside one statement. The bug returns the moment the application computes the new value outside the database (HTTP handler, ORM dirty fields, multi-field form merge). That is the common production shape — not the one-liner counter.

Also: multi-field form clobber

Lost updates are not only counters. Two admins open the same product form:

t	Admin A	Admin B	Row after
T0	Load name=Acme, price=10, qty=5	Load same	Acme / 10 / 5
T1	Changes price → 12, saves full row	—	Acme / 12 / 5
T2	—	Changes name → Acme Pro, saves full row still holding price=10	Acme Pro / 10 / 5

B never touched price — but blind full-row UPDATE restored 10. A version (or ETag / If-Match) on B’s save returns 0 rows / 412; B reloads and merges intentionally. Same protocol as the counter lab; different business pain.

Optimistic move: version predicate

SELECT version, qty FROM inventory WHERE id = 1;  -- version = 7, qty = 100
-- think …
UPDATE inventory
SET qty = 99, version = version + 1
WHERE id = 1 AND version = 7;
-- row count 1 → success
-- row count 0 → predicate missed: stale version, deleted/missing, or RLS-hidden

A zero-row result means the full predicate did not match — not automatically “another writer won.” Re-read through the same authorization/RLS scope and a fresh snapshot (end or roll back an enclosing repeatable-read transaction first): if the row is still visible with a newer version, retry or return 409/412; if it is absent or hidden, return the API’s normal not-found result without a privileged existence probe that leaks another tenant’s row. Optimistic means: assume conflict is rare, run without holding a lock across think-time, and detect a stale precondition at write. Pessimistic (SELECT … FOR UPDATE) means: lock first so nobody else can change the row until you commit.

Lab numbers (Podman, 30-run median)

Container til-postgres. One inventory row, start qty = 100, 8 concurrent workers. Each worker reads, sleeps ~15–25 ms (app think), then writes. Blind writes absolute qty = q - 1. Version mode retries on 0-row updates. FOR UPDATE locks across the think sleep.

Mode	median final qty	median lost decrements	median retries	median wall ms
Blind RMW	99	7	0	101.4
Version WHERE	92	0	18	264.4
FOR UPDATE	92	0	0	248.3

Expected if all 8 debits apply: 92. Blind median loses 7 of 8 decrements (last writer wins). Version and FOR UPDATE keep stock correct. Wall times are not the product claim — correctness is. OCC is slightly slower here because retries re-do think sleeps under contention; that is expected. The recorded harness and standalone results are not included in this repository.

Diagram: three paths on one hot row

Production retry loop (sketch)

for (let attempt = 1; attempt <= 8; attempt++) {
const row = await db.oneOrNone(
  `SELECT version, qty FROM inventory WHERE id = $1`, id
);
if (!row) throw new NotFound(); // same role/RLS scope; do not probe as owner
if (row.qty < need) throw new InsufficientStock();
const r = await db.result(
  `UPDATE inventory
      SET qty = qty - $2, version = version + 1
    WHERE id = $1 AND version = $3`,
  [id, need, row.version]
);
if (r.rowCount === 1) return;           // won
// In autocommit/READ COMMITTED this is a fresh snapshot. If an enclosing
// transaction pins a snapshot, roll it back and classify in a new one.
const latest = await db.oneOrNone(
  `SELECT version FROM inventory WHERE id = $1`, id
);
if (!latest) throw new NotFound();       // deleted, missing, or not visible
if (!(latest.version > row.version)) {
  // SELECT can see the row, but the UPDATE predicate/policy still rejected it.
  // This is not contention and retrying cannot make it succeed.
  throw new UpdatePredicateRejected();
}
await sleep(jitter(attempt));           // Day 37
}
throw new ConflictAfterRetries();         // surface to client

Only a newer visible version confirms contention and reaches the retry. A same-version re-read means some other UPDATE predicate or command-specific policy rejected the write; classify that as a non-retry result appropriate to the API instead of spending the conflict budget. Notes: prefer computing the new qty inside SQL when you can (qty = qty - $need) and keep the version predicate — you still detect concurrent business changes to other columns if those bumps share the version. Cap attempts. Jitter. Do not retry forever on a permanently hot row — that is a signal to change the locking strategy.

Pessimistic vs optimistic (decision)

Choose	When	Cost
FOR UPDATE (pessimistic)	Hot row, conflict rate high, critical section short and server-side	Holders block waiters — set Day 45 budgets; never across browser think-time
Version / ETag OCC	Think-time outside DB; conflicts uncommon; you can retry safely	Retry storms if everyone fights one row (lab: ~18 retries / 8 workers)
Atomic SQL (qty = qty - 1)	Single-field counter with no multi-field merge in the app	Does not protect multi-column form saves or “read, branch, write” workflows
Advisory lock (Day 43)	Logical mutex not tied to one row (leader election, single-flight job)	Different tool — not a substitute for row versioning on inventory

Minimal schema + HTTP shape

ALTER TABLE inventory ADD COLUMN version int NOT NULL DEFAULT 0;
-- every successful business update:
UPDATE inventory
SET …, version = version + 1
WHERE id = $1 AND version = $expected
RETURNING version;

HTTP APIs often expose the version as an ETag / If-Match header — same predicate, different wire shape (412 Precondition Failed on mismatch). ORM “optimistic lock” / @Version annotations are this pattern with less SQL in your face. Some teams use xmin as a cheap system column surrogate; application-owned version is clearer across logical replication and ORM layers — pick one and document it.

OCC vs isolation levels (Day 41)

Read Committed (default) does not prevent the app-level lost update above: each statement sees a fresh snapshot, but two absolute writes still last-writer-wins. Repeatable Read aborts a transaction that tries to update a row changed since its snapshot; Serializable adds SSI and can abort broader read/write dependency patterns. Both require retrying the whole transaction. That is necessary — and still not a substitute for a clear version predicate on Read Committed APIs:

• Isolation-level abort → whole-txn retry, error-driven
• Version 0-row → explicit, works on RC, easy to map to HTTP 409/412

Many production apps stay on Read Committed + OCC (or FOR UPDATE on hot paths) rather than turning the whole cluster to Serializable. Know both levers.

What breaks? — Anti-patterns

• Blind RMW on hot counters — silent under-counts; lab median final 99 vs expected 92.
• Blind full-row form saves — clobber columns you never edited (price restored to 10 above).
• Version check without retry budget — infinite spin under sustained conflict; cap attempts + Day 37 jitter.
• Holding FOR UPDATE across user think-time — turns browsers into lock holders; prefer OCC or short server-side critical sections + Day 45 timeouts.
• Incrementing version on every unrelated column touch without need — false conflicts; scope version to the aggregate you care about.
• OCC + long multi-statement business txn without re-read — version was fresh at T0, stale by T3.
• Assuming Serializable alone fixes app RMW — SSI aborts some anomalies; app still must retry whole transactions. Version predicates make the conflict obvious as 0 rows even on Read Committed.
• Treating wall-ms as the win — lab OCC wall can exceed FOR UPDATE under contention; the product claim is lost = 0, not a speedup.

How it connects

• Day 10 / 24: local transactions + MVCC — writers create new row versions; OCC is an application protocol on top of that storage model.
• Day 36: lock waits can deadlock; OCC trades lock queues for retry loops.
• Day 41: lost update is the anomaly this pattern is named for; isolation levels are the other dial.
• Day 45: if you pick pessimistic, bound the wait; do not hold forever.
• Day 37 / 30: retries need backoff; side effects on retry paths need idempotency keys.
• Day 43: advisory locks are logical mutexes — complementary, not a row-version replacement.

Transfer questions

1. Chen’s admin UI loads a product form (name, price, qty). Two staff save different fields 200 ms apart with blind UPDATE of all columns. What goes wrong, and how does a version column change the second save?
2. Your lab shows version mode median 18 retries for 8 workers. When do you switch the hot SKU path to FOR UPDATE instead of OCC — and which Day 45 setting do you put on that pool role?
3. Why does UPDATE inventory SET qty = qty - 1 not need a version column for a pure counter, and when does that stop being enough?
4. You enable Serializable cluster-wide. Does that let you delete the version column from the product form API? Why or why not?

What you should be able to do

• Draw blind RMW vs version vs FOR UPDATE on one hot row.
• Quote the lab: blind median final 99 (lost 7); version / lock final 92 (lost 0); ~18 OCC retries.
• Write the WHERE version = $v update and distinguish 0-row stale version from absent/hidden without leaking existence.
• Walk a multi-field form clobber timeline and where ETag fits.
• Pick optimistic vs pessimistic vs atomic SQL with one tradeoff each; relate to Day 41 isolation.

Interview version (60s)

“Lost updates happen when two sessions read the same row, compute new values in the app, and write absolute columns — last writer wins. I add a version column and UPDATE … WHERE id AND version. Zero rows means the predicate missed, so I re-read in the same auth scope: newer version is a conflict/retry; absent or RLS-hidden stays not-found. Lab on Postgres: 8 workers, blind RMW ends at qty 99 instead of 92; version and FOR UPDATE both keep 92. OCC avoids holding locks across think-time; high conflict → pessimistic plus lock_timeout.”

Quiz

Questions? Ask the agent — continue with Day 51 (Saga Compensations). You live from Day 24 (MVCC).