Two concurrent transactions, both at the READ COMMITTED isolation level, each read an account's balance, compute a new value in application code, and write it back. Explain what goes wrong and what your options are to prevent it.

## The anomaly The interleaving is: 1. T1 reads `balance = 100`. 2. T2 reads `balance = 100`. 3. T1 computes `100 - 50 = 50` and writes 50, commits. 4. T2 computes `100 - 30 = 70` and writes 70, commits. Final balance: 70. Correct answer: 20. T1's withdrawal vanished. This is the **lost update**. Nothing in READ COMMITTED was violated. T2 read committed data (100 was committed at the time). T2 wrote after T1 committed, so there was no dirty write and no lock conflict to block on — by the time T2's `UPDATE` ran, T1's lock was gone. The engine has no way to know that T2's value 70 was *derived from* a version that is no longer current. That derivation lives in your application's memory, invisible to the database. This is the single most common real-world defect attributed to isolation levels, and it is a defect of application shape rather than of the level. ## Why the level cannot save you Some levels do detect this pattern — stronger snapshot-based levels typically abort one of the two transactions with a serialization failure because both modified a row the other had read. READ COMMITTED deliberately does not: its per-statement view means there is no transaction-scoped snapshot to violate, and its whole value proposition is not aborting things. So the responsibility is explicitly pushed to you. ## Remedy 1 — collapse the read-modify-write into one statement ``` UPDATE account SET balance = balance - 50 WHERE id = 1 AND balance >= 50; ``` The new value is expressed as a function of the *current stored value*, not of a value your process read earlier. Concurrent executions serialise on the row's write lock, and after a blocker commits, the expression and the guard are re-evaluated against the newest committed version. Both withdrawals apply; neither is lost. Two disciplines go with it: put every precondition into the `WHERE` clause (a check kept in application code is never re-evaluated), and treat an affected-row count of zero as a real business outcome — "insufficient funds" — not as an anomaly to ignore. This is the best answer whenever the computation is expressible in SQL, because it requires no extra round trips, no retry loop, and no lock held across network latency. ## Remedy 2 — pessimistic locking on read ``` BEGIN; SELECT balance FROM account WHERE id = 1 FOR UPDATE; -- application computes the new value UPDATE account SET balance = :new WHERE id = 1; COMMIT; ``` `FOR UPDATE` takes the row's exclusive write lock at read time. A second transaction reaching the same `SELECT ... FOR UPDATE` blocks until the first commits, and — importantly under READ COMMITTED — then re-reads the *newest committed* version, so it computes from 50, not from 100. Use this when the computation genuinely cannot be expressed in SQL — it involves an external service call, complex branching, or data the database does not hold. The costs are real: you hold a write lock for the entire duration of your application logic, so any latency in that logic becomes contention for everyone else, and you must lock rows in a consistent order to avoid deadlocks. Never hold such a lock across a user's think time. ## Remedy 3 — optimistic concurrency with a version column Add an integer `version` (or use a timestamp/row-version column) and make the update conditional on the version you read: ``` SELECT balance, version FROM account WHERE id = 1; -- 100, 7 UPDATE account SET balance = 50, version = version + 1 WHERE id = 1 AND version = 7; ``` If a competitor committed in between, `version` is now 8, the `WHERE` matches nothing, and the affected-row count is **zero**. You detect the conflict, re-read, recompute, and retry — usually with a bounded retry count so a pathological hot row cannot spin forever. This is the right shape when the gap between read and write is long — an editing form submitted minutes later, a workflow spanning requests — because it holds no locks during the gap. It is also what most object-relational mapping layers implement for their optimistic-locking feature. The trade is that you must write the retry loop and make the retried operation safe to re-run. ## Remedy 4 — raise the isolation level Moving the transaction to a stronger level makes the engine detect the conflict for you, generally by aborting one transaction with a serialization failure. You still need a retry loop, you now pay the cost on every statement in the transaction rather than on the one row that needs it, and you have changed behaviour for code you were not thinking about. Reach for it when there are *many* interacting invariants, not to fix one counter. ## Choosing Ask: can the new value be written as a function of the stored value? If yes, remedy 1, always. If not, is the read-to-write gap short and inside one transaction? Then remedy 2. Is the gap long or spanning requests? Then remedy 3. Only if the invariant spans several rows and tables in ways none of these capture should you escalate the level. And note the free win: declarative constraints — `CHECK (balance >= 0)`, unique indexes, foreign keys — are enforced by the engine at write time regardless of isolation level, so they catch a class of these races unconditionally. They should back up whichever remedy you choose.