Explain how an optimistic version column detects a lost update: what the UPDATE statement must look like, how a conflict is discovered, and what the application does next.

## The idea Pessimistic locking assumes a conflict and prevents it. **Optimistic concurrency control** assumes conflicts are rare, lets everyone proceed, and *detects* the conflict at write time. The detector is a version column: a monotonically increasing integer stored on the row and bumped by every write. ## The protocol 1. **Read** the row along with its version: `SELECT id, title, body, version FROM documents WHERE id = ?` → version 7. 2. **Work** — arbitrarily long. Application logic, a user filling in a form for ten minutes, a workflow across several HTTP requests. Nothing is locked and no transaction is held open. 3. **Write conditionally**: ```sql UPDATE documents SET title = ?, body = ?, version = version + 1 WHERE id = ? AND version = 7; ``` 4. **Inspect the affected-row count.** One row → your write applied and the version is now 8. Zero rows → somebody else committed a change since you read, the row's version is no longer 7, and *your write did not happen*. ## Why zero rows is a reliable signal A row-level `UPDATE` takes the exclusive row lock; if another transaction holds it, yours waits and then re-evaluates its `WHERE` clause against the newest committed version of the row. So the version comparison is always made against current data, not against a snapshot. The check and the write are therefore one atomic compare-and-set, with no window between them. This holds regardless of isolation level for the single-statement case, which is what makes the technique portable. The corollary is the number-one implementation bug: **ignoring the affected-row count**. Frameworks that fire-and-forget an update turn a detected conflict back into a silent lost update. If you take one thing from this topic, take that. ## Handling the conflict Detection is only half the design; the other half is policy. - **Automatic retry** suits machine-driven writes where the new value can be recomputed: re-read the row (getting version 8), re-apply the intended change, re-issue. Bound the retries (3–5), add jitter, and give up with a clear error rather than looping forever. - **Surface it to the user** for human edits: "this record was changed by someone else since you opened it." Blindly retrying here would re-apply the user's stale form values on top of the other person's edit — which is exactly the lost update you were preventing, laundered through a retry loop. - **Merge** where the domain allows it: if the two writers touched disjoint fields, present a merge or apply field-level updates so both survive. This requires writing only dirty columns, not the whole row. ## Design details that matter **Every writer must participate.** A code path that updates without the version predicate silently bypasses the scheme and can also leave the version un-incremented, so *other* writers stop detecting conflicts. Enforce it centrally — in the data-access layer, an ORM's version support, or a trigger that increments the version on every update — rather than by convention. **Version vs timestamp.** An integer counter is the safe choice. A last-modified timestamp is tempting but risks equal values within the clock's resolution, clock skew across nodes, and daylight/NTP adjustments; two writes in the same millisecond then look identical. If you must use a timestamp, make sure it is generated by the database and has sufficient resolution — and prefer the integer anyway. **Scope of the version.** One version per row is the standard. A version per aggregate (parent row bumped by any child change) protects cross-row invariants but increases contention on the parent. Choose deliberately. **Deletes.** A conditional delete (`DELETE ... WHERE id = ? AND version = ?`) gives the same protection; zero rows means either the row was changed or already deleted, and the application must distinguish those if it matters. **Read-only readers** do not need the version, but any read that will feed a write must carry it, including reads that go through caches — a cached row with a stale version simply produces a conflict, which is correct behaviour, though it can produce a retry storm if the cache is not invalidated. ## Optimistic versus pessimistic | | Optimistic version check | Pessimistic row lock | |---|---|---| | Holds a lock while thinking | No | Yes | | Works across requests / think-time | Yes | No | | Cost under no contention | Near zero | A lock acquisition and hold | | Behaviour under high contention | Retries, wasted work, possible starvation | Queueing, bounded but serialized | | Failure mode | Late failure after the work is done | Blocking, lock-wait timeout, deadlock | Under heavy contention on one row, optimistic control degrades badly: many writers do the work, one wins, the rest throw it away and try again, and long-running writers can starve behind short ones. That is the point at which you either switch to a lock, express the change as an atomic in-place arithmetic update, or redesign the hot row away (shard the counter, append events and aggregate). ## What it does not solve A version column protects **one row**. If your invariant spans rows — "at least one doctor must remain on call", "the sum of these lines must equal the header total" — checking each row's version independently can still allow two transactions to break the invariant together, because each row they wrote was unchanged. That class of problem needs a serializable isolation level, a lock on a common parent row, or a constraint that materializes the invariant in a single row.