Why does an in-place update such as `UPDATE accounts SET balance = balance - 10 WHERE id = 42` avoid a lost update, while reading the balance into application code and writing back the computed number does not?

## The mechanical difference Compare two ways to decrement a balance by 10. **Application-side read-modify-write** ``` SELECT balance FROM accounts WHERE id = 42; -- 100 -- application computes 100 - 10 = 90 UPDATE accounts SET balance = 90 WHERE id = 42; ``` **In-place update** ``` UPDATE accounts SET balance = balance - 10 WHERE id = 42; ``` The first form contains a *window*: between the `SELECT` and the `UPDATE` there is a network round trip, application code, and possibly seconds of user think-time. Any transaction can commit a change to the row inside that window, and nothing in the second statement notices — it sets a constant. The second form has no window. A single-row `UPDATE` is executed by the engine as one indivisible step: it locates the row, takes an **exclusive row-level write lock**, reads the current value of `balance` under that lock, computes the new value, and writes it. No other transaction can interleave inside a step it cannot enter. ## What happens when two of them collide Suppose T1 and T2 both issue `SET balance = balance - 10` at the same instant. - T1 acquires the row's write lock first and computes 100 → 90. - T2 finds the row locked and **blocks**. It is not reading anything yet. - T1 commits and releases the lock. - T2 wakes up. It does not use the value it might have seen earlier; it re-reads the newest committed version of the row — 90 — and computes 80. The two decrements compose correctly. This re-evaluation on wake-up is the crucial part, and it is why *writers see the latest committed row even in a multiversion engine that would otherwise show them an older snapshot*. Writes always operate on the current version of the row; only reads follow a snapshot. One wrinkle to know: under a transaction-wide snapshot (`REPEATABLE READ` in a snapshot-based engine), re-reading a newer version would contradict the transaction's own snapshot, so the engine instead aborts the waiting transaction with a **serialization failure** and expects the application to retry. Under `READ COMMITTED` it re-evaluates and proceeds. Either way the update is not lost — you either get the correct value or a loud error, never silent overwriting. ## Why the `WHERE` clause matters too The re-evaluation on wake-up also re-checks the predicate. `UPDATE orders SET status = 'SHIPPED' WHERE id = 7 AND status = 'PENDING'` is safe even under concurrency: if another transaction already shipped it, the waiting statement re-evaluates the predicate against the new version, matches nothing, and reports **zero rows affected**. Checking the affected-row count turns the statement into a compare-and-set. This is the single most useful trick in the pattern, and candidates who mention it stand out. Similarly, `UPDATE accounts SET balance = balance - 10 WHERE id = 42 AND balance >= 10` both performs the arithmetic atomically and enforces the invariant. If it reports zero rows, the withdrawal was rejected — no negative balance is possible even under contention. ## When the in-place form is not available - The new value is not a function of the old one that SQL can express: it depends on a pricing service, a machine-learning score, a file, or user input. - The write spans multiple rows or multiple tables and must be consistent across them. - The user edits a whole record over minutes of think-time — no single statement can span that. This is where a version column belongs. - The value is not commutative and the *order* of application matters for business reasons. ## Costs and caveats - **Hot-row serialization.** Every concurrent writer of the same row queues behind one lock. A single global counter updated thousands of times per second becomes the bottleneck; the standard remedy is to shard the counter into N rows and sum them on read, or to append events and aggregate. - **Blocking, not failing, is the default.** Long-running transactions that hold the lock stall every other writer, and lock-wait timeouts appear under load. - **It does not make the whole business operation atomic.** If the debit and the credit are separate statements, wrap them in a transaction — the atomic update only protects each row's own read-modify-write. - **Do not read the row afterwards to "confirm" the value** unless you need it; if you do need the new value, ask for it in the same statement where the engine supports returning updated rows, rather than issuing a second `SELECT`. ## The rule to remember Move the computation to where the lock is. If the database can compute the new value from the old one, let it; if it cannot, you need a lock held across your read and write, or a version check that fails the write when the row moved underneath you.