You own a service where several write paths behave very differently: per-user profile edits, a shared inventory counter for a flash sale, and a nightly bulk reconciliation. How do you decide, per path, between optimistic version checks and pessimistic locking — and what measurements would change your mind?
answer
- Per path, not per system
- Conflict probability × window length × redo cost
- Hot counter: relative UPDATE > shard > lock
- Batch job: lock hold time is the risk, chunk it
- Metrics: conflict rate, lock wait, latency-vs-concurrency
basics
~20 sDecide per path, not per system. Estimate conflict probability, the window between read and write, and the cost of redoing the work. Rare conflicts and long windows favour optimistic; hot rows with short transactions favour pessimistic. Measure conflict rate, lock-wait time and end-to-end latency, and prefer designs that remove the contention entirely.
solid answer
~60 sThere is no system-wide answer; the choice belongs to each write path. **Profile edits:** conflicts are near zero (one user per row) and the edit may span a form. Optimistic, with the version carried to the client and a clean conflict response. Pessimistic here buys nothing and risks holding locks across requests. **Flash-sale counter:** every request targets one row. Optimistic degenerates — most attempts do work and throw it away, latency spikes, some requests starve. Options in order of preference: eliminate the read-modify-write with a single guarded relative update; shard the counter and sum on read; otherwise a short pessimistic transaction, which converts wasted work into a bounded wait. **Nightly reconciliation:** the enemy is lock hold time, not conflicts. Chunk it into bounded batches, keep transactions short, and lock rows in a consistent order to avoid deadlocks with online traffic. Measurements that move me: conflict rate per entity, lock-wait and deadlock counts, p99 latency versus concurrency, and retry-exhaustion errors. A rising conflict rate is a schema signal, not a tuning knob.
go deeper
Give the default rule — optimistic for ordinary edits, pessimistic for short hot critical sections — and say the choice depends on how often rows collide.
Work through each path with conflict probability, window length and redo cost, and note that a relative UPDATE can remove the problem entirely.
Add the operational evidence: conflict rate, lock waits, deadlocks, latency-versus-concurrency shape, and how batch jobs must be chunked to bound lock hold time.
Lead with removing contention by design (sharded counters, narrower rows, allocation records), set the default plus explicit exceptions, and define the metrics that trigger a revisit.
## The decision is per write path The first thing to say out loud is that "we use optimistic locking" is not an architecture; it is a default. Contention is a property of a *row and an access pattern*, so a single service routinely needs both strategies. The framing that survives scrutiny asks three questions of each path. **1. What is the probability that two transactions touch the same row concurrently?** This is a function of the key. Rows keyed by user, order, or document are effectively private — conflicts arise only from double submits and admin overlap. Rows that are singletons by design — a stock counter, a sequence table, an account balance for a popular merchant, a leaderboard aggregate — are contended by construction, and contention grows with traffic rather than staying flat. **2. How long is the window between the read the decision is based on and the write?** Milliseconds inside one service call, or minutes across a user session and several HTTP requests? Anything spanning a user, a queue hop, or a third-party call cannot hold a database lock, which decides the matter regardless of conflict rate. **3. What does redo cost, and is it even safe?** Pure recomputation is cheap. Work that has already emitted external effects is not replayable without idempotency machinery, which pushes toward taking the lock first. ## Applying it **Per-user profile edits.** Low conflict, long window, cheap redo. Optimistic wins on every axis. The version travels to the client (in the payload, or as an ETag on the resource) and a mismatch becomes a conflict response the UI can present as "this record changed while you were editing". Never hold a lock across the form. **Shared inventory counter under a flash sale.** High conflict, short window. Optimistic retry is the wrong instrument: with N concurrent writers, only one attempt in N does useful work, throughput plateaus, tail latency explodes, and unlucky requests exhaust their retry budget — a fairness failure that shows up as random 409s for real customers. The escalation ladder, best first: - **Remove the read-modify-write.** `UPDATE stock SET qty = qty - 1 WHERE sku = ? AND qty >= 1` is atomic in the engine, needs no application retry, and the affected-row count is the sold/sold-out answer. Most "we need locking" arguments dissolve here. - **Remove the shared row.** Shard the counter into K rows and sum on read, or model inventory as issued tickets/allocation rows so writers touch different rows. This trades read complexity for write parallelism and is how genuinely hot counters scale. - **Serialize deliberately.** If invariants are too complex for one statement, take a short pessimistic lock. A bounded wait beats unbounded wasted work when conflict probability approaches one. **Nightly bulk reconciliation.** Conflicts with itself are nil; the risk is what it does to everyone else. Long single transactions hold many locks and enlarge the blast radius of any failure. Chunk into bounded batches with commits between them, order the work by the same key order the online path uses so lock acquisition order is consistent, and run with a lock timeout so the batch yields rather than piling up waiters. Whether it takes locking reads at all depends on whether online traffic can mutate the same rows mid-run. ## Measurements that change the decision Opinions should be revisited by data: - **Conflict rate per entity type** (optimistic failures / attempts). Under ~1% the loop is free. Persistently above roughly 10% on one entity, the design is wrong, not the parameters. - **Lock wait time and deadlock counts** on pessimistic paths, plus the distribution of transaction duration — lock hold time is the real currency. - **Latency versus concurrency curve.** The signature of pathological optimistic contention is throughput that flattens or falls as concurrency rises while CPU stays busy; the signature of pessimistic convoying is throughput flat with CPU idle and waits dominating. - **Retry-exhaustion errors reaching users.** Any nonzero rate is a product-visible symptom and should trigger the ladder above. - **Connection-pool saturation**, which is where long transactions and lock waits eventually announce themselves. ## The judgment to voice A strong answer resists treating this as a binary. Optimistic and pessimistic are both ways of coping with a contended row; the highest-leverage move is usually to stop having one. State the default (optimistic, because it costs nothing in the common case and works offline), name the exceptions explicitly (short, hot, invariant-critical sections), require idempotency wherever retry exists, and treat conflict metrics as a design feedback loop rather than a knob to tune.
- Your optimistic conflict rate on one entity has climbed to 30%. What do you do?Treat it as a design signal rather than tuning retries. First check whether the update can be expressed as a single relative statement so no stale read exists. If not, look at why one row is hot: split the contended field into its own row so unrelated edits stop colliding, or shard the value across several rows and aggregate on read. Only if the invariant genuinely requires a critical section do I switch that path to a short pessimistic transaction, where the wait is bounded and no work is discarded.
- Why not just raise the isolation level to SERIALIZABLE and stop reasoning about this per path?It relocates the work rather than removing it. Serializable implementations either abort transactions with serialization failures — so you still need the same retry loop, now on more paths — or acquire more locks, which increases blocking globally. It is also a blunt, system-wide performance decision, whereas a version column or a locking read applies precisely to the rows that need protection. Serializable is a good answer for a small number of genuinely intricate invariants, not a substitute for per-path judgment.
saying these in an interview costs you the question
- Picking one strategy for the entire system and defending it as an architectural principle
- Treating a high conflict rate as a retry-tuning problem instead of a schema problem
- Assuming optimistic control is always cheaper, ignoring that it is quadratic in wasted work under heavy contention
- Wrapping a long batch job in a single transaction and calling it atomicity
- Ignoring fairness: retry exhaustion under contention hits real users as random failures