An application reads a row, computes a new value in application code, and writes it back inside one transaction in an MVCC database at READ COMMITTED. Why can concurrent runs silently lose one of the updates, and what are the options for making it correct?

## Why the race exists at all The defining property of MVCC is that a read is satisfied from a version chosen by a snapshot, without any lock. That is what lets long reads coexist with heavy writes. But it also means a read carries **no promise about the future**: the instant your SELECT returns, the row may already be different. Application code that reads a value, does arithmetic or business logic on it, then writes the computed result back is implicitly assuming the row did not move. Nothing enforces that assumption. Concretely, with two concurrent runs of \"read balance 100, subtract 10, write 90\" and \"read balance 100, subtract 5, write 95\": both read 100 (neither blocks the other), then the writes serialize — one waits for the other's row lock, then applies. The final balance is 90 or 95 instead of 85. No error is raised anywhere. This is the **lost update** anomaly. Being inside a single transaction does not save you at READ COMMITTED: the transaction acquired nothing on that row when it read. The write lock is only taken at UPDATE time, far too late. ## Option 1 — Pessimistic locking on read Ask for the lock when you read: a locking read (`SELECT ... FOR UPDATE`) takes the same exclusive row lock an UPDATE would, held to commit. A second transaction reaching that locking read waits, and when it proceeds it re-reads the committed value — so its computation starts from the correct number. This is deliberately turning off the readers-don't-block-writers property for the rows where correctness needs it, giving those rows 2PL semantics. It is easy to reason about and it never fails spuriously. The costs are real: the lock is held for the rest of the transaction, so anything slow in between (an external API call, a payment gateway, user think time) is a lock held that long. Under contention, throughput on that row becomes one transaction at a time. And multiple locked rows in inconsistent order produce deadlocks, so lock rows in a deterministic order. ## Option 2 — Optimistic concurrency control Read the row along with a discriminator — a `version` integer, an update timestamp, or the original value itself — and make the write conditional on it: `UPDATE ... SET balance = 90, version = 8 WHERE id = 7 AND version = 7`. Then inspect the number of rows affected. Zero means someone else changed the row first; you re-read and re-run the logic. This is the right shape when conflicts are rare, when the read and write are separated by a long gap (classic web edit forms, where a pessimistic lock would be held across user think time), or when the read happens in a different transaction from the write entirely. The costs: every caller must actually check the affected-row count — a silent zero is the same bug you were trying to fix — and the retry path must be tested, because under real contention it is a hot path, and naive retry loops can amplify a contention spike. ## Option 3 — Do the computation in the statement If the new value is a pure function of the old value expressible in SQL, do not round-trip through the application at all: `UPDATE accounts SET balance = balance - 10 WHERE id = 7 AND balance >= 10`. The read and write happen inside one locked operation, and at READ COMMITTED the engine re-checks the predicate against the newest committed version after any wait. Nothing is lost, no retry is needed, and the guard in the WHERE clause even enforces the business rule. Check the affected-row count to distinguish \"applied\" from \"insufficient balance\". This is the best answer whenever it applies. ## Option 4 — Raise isolation and retry Run the transaction at SERIALIZABLE. A serializable engine either uses locking or tracks read-write dependencies among concurrent snapshots and aborts a transaction that would produce a non-serializable outcome. You write straightforward read-then-write code and wrap it in a retry loop. This generalizes beyond single-row lost updates — it also catches **write skew**, where two transactions read overlapping data, check an invariant, and each write a *different* row, which neither row locks nor a version column on one row would catch. The cost is throughput under contention (aborts and retries) and the discipline that every transaction must be retryable: no partially applied side effects outside the database, and retry the *whole* transaction, not the failed statement. ## Choosing - Expression computable in SQL, single row: **option 3**. - High contention on a known, short-lived row: **option 1**, with the lock taken as late as possible and released by a short transaction. - Rare conflicts, long human gap between read and write, or read and write in different transactions: **option 2**. - Invariants spanning multiple rows, or complex logic you do not want to hand-verify: **option 4**. And whichever you choose, the diagnostic instinct matters more than the menu: when someone reports numbers drifting under load with no errors in the log, suspect a read-modify-write over a snapshot read.