You are designing the write path for a record that many users and jobs modify concurrently. How do you choose between an atomic in-place update, pessimistic row locking, and an optimistic version check — and what changes your answer as contention rises?

## Framing There is no universally best answer, which is why this is a judgement question. The decision has three inputs: the **shape** of the write, the **duration** of the gap between read and write, and the **contention** on the target row. A fourth, often decisive in practice, is **who has to obey the protocol** — a technique only a disciplined codebase can maintain is the wrong technique for a large one. ## Input 1 — shape of the write Ask: can the new value be computed by the database from the old one? - **Yes, and it is commutative** (`+ n`, `- n`, set-union-like semantics): use an in-place update. Two concurrent decrements compose correctly with no coordination, and the guard can live in the predicate (`AND qty >= :n`). Nothing for a caller to forget, no retry loop, no held lock. This should be your default whenever it applies. - **No — the new value depends on application logic, another table, an external system, or a human**: the computation must happen outside the statement, so you need either exclusion (a lock) or detection (a version). Also ask whether the invariant is per-row or across rows. Per-row invariants are covered by all three techniques. Cross-row invariants ("at least one on-call engineer", "lines must sum to the header") are not covered by per-row versions at all, and need a lock on a common parent, a materialized invariant row, or a serializable transaction. ## Input 2 — duration of the gap Holding a database lock means holding a transaction, and holding a transaction means holding a connection and blocking every other writer of that row. - **Milliseconds, entirely server-side** → pessimistic locking is fine and gives the simplest reasoning: inside the lock you are alone. - **Seconds** (a slow computation, a call to another service) → do the slow work *before* taking the lock, then take it and re-validate; or use optimistic control. - **Minutes, or spanning requests** (a user editing a form, a wizard) → optimistic only. Locking here is an availability incident waiting for the user to go to lunch. If the domain truly demands exclusive editing, implement an *application-level* lease with an expiry stored in a row — not a database lock. ## Input 3 — contention Model it crudely. Let *r* be the write rate on the row and *t* the time between the read and the write. - **Optimistic**: conflict probability grows roughly with *r × t*. When conflicts are rare, cost is zero — no locks, no blocking, one extra predicate. When they are common, every loser repeats its work; effective throughput collapses and long transactions starve behind short ones because they are likelier to lose each round. Bounded retries with jitter mitigate but do not fix it. - **Pessimistic**: throughput on the row is capped at roughly 1 / *t*, deterministically. Nobody wastes work, but everyone queues, and queued transactions occupy connections — a pool-exhaustion risk that turns a hot-row problem into a whole-service outage. Add lock-wait timeouts and treat `NOWAIT` as a load-shedding tool. - **In-place update**: also caps at 1 / (very small *t*), but *t* is microseconds because no application round trip happens inside the lock. This is why it is orders of magnitude better on hot rows than either alternative. ## When arbitration is the wrong answer Beyond a few thousand writes per second on one row, no locking scheme saves you — the row itself is the bottleneck. Redesign: - **Shard the counter**: N rows per logical counter, writers pick one at random, readers sum. Trades read cost and exactness-at-an-instant for write throughput. - **Append and aggregate**: insert immutable events (no contention on inserts) and compute the current value on read or in a background rollup. Gives an audit trail for free; costs read complexity and staleness. - **Serialize per key off the database**: route all writes for a key through one consumer (partitioned queue, single-writer actor). Removes contention entirely at the cost of an extra system and its own failure modes. - **Question the requirement**: does the counter need to be exact and immediately consistent, or is a slightly stale aggregate acceptable? This is often the highest-leverage question in the room. ## Input 4 — organizational reality Pessimistic locking and version columns are both *protocols*: they work only if every writer participates. A single batch job that reads plainly and updates silently defeats either one. Optimistic versioning is more defensible because you can enforce it centrally — a trigger that increments the version on every update, or a data-access layer that refuses updates without the predicate — and because its failure is loud rather than silent. In-place updates need no protocol at all, which is another reason to prefer them where they fit. ## Conflict policy is part of the design Whichever you pick, decide up front: - Which conflicts are **retried automatically** (recomputable, machine-driven) and which are **surfaced** (human input that must not be blindly re-applied). - The **retry budget**: attempts, backoff, jitter, and the error returned when exhausted. - **Idempotency** of the retried operation, especially if it has side effects outside the database. - **Observability**: conflict rate, deadlock rate, lock-wait time, retry exhaustion. These are the leading indicators that the chosen strategy has outgrown the load, and without them you find out from a support ticket. ## A defensible summary answer "In-place arithmetic where the write is expressible that way; a short `FOR UPDATE` critical section with a fixed lock order where server-side logic spans rows; a version column wherever a human or a request boundary sits between the read and the write. I measure conflict and lock-wait rates; when they climb, I stop trying to arbitrate the hot row and remove it — shard it, or append events and aggregate. And I treat the conflict-handling policy, retry budget and idempotency as part of the design, not an afterthought."