Read Committed and the lost update

Why does Read Committed allow lost updates, and how much does it matter where the read-modify-write lives?

Level by level, each derived from the mechanism that produced it

Read Uncommitted: a fossil

Read Uncommitted (RU) forbids only dirty write. Note why dirty write is forbidden even at this level. It has nothing to do with isolation. Undo-based recovery (rolling back by restoring old values) needs a transaction's writes to be the last writes to those items. Otherwise a rollback would erase another transaction's work. So the floor of the isolation hierarchy is set by the recovery mechanism, not by an isolation requirement.

Under MVCC the level buys nothing, because there is no cheaper path to an uncommitted version than to a committed one. PostgreSQL silently maps RU to Read Committed. RU made sense only in lock-based engines, where reading meant taking a shared lock and RU meant skipping it.

Read Committed: cheap, and the home of lost updates

Read Committed (RC) is the cheapest level with a defensible story. Its defining property is not what it forbids but the scope of its snapshot: per statement, not per transaction. Each statement sees the data committed when that statement started. Two statements in one transaction can see two different worlds.

By Little's rule (anomaly count ≈ conflict rate × window), RC makes the window W small while still excluding garbage such as uncommitted values.

Two details carry weight.

In PostgreSQL at RC, if an UPDATE has to wait for a lock held by another transaction, then after the lock is released it re-evaluates the row against the new version. One statement is therefore evaluated against a mixture of two snapshots. It can produce a result that no serial order allows.
This is the detail that actually corrupts production data: RC permits lost update, and the window is not a database window.

The window argument, with numbers

Look at the bonus job:

-- Application code (two round trips, plus code between them)
BEGIN;
SELECT balance FROM accounts WHERE id = 7;        -- returns 100
-- application computes 100 + 100 = 200 ...
UPDATE accounts SET balance = 200 WHERE id = 7;   -- overwrites
COMMIT;

If a transfer adds 50 between the SELECT and the UPDATE, the balance in the database is 150 when the job writes 200. The 50 is gone. The anomaly needs two operations to fall inside the same window.

Suppose 200 conflicting operations per second touch the same hot accounts (an illustration).

Where the read-modify-write lives	Window W	Operations in the window (λ × W)
Application code with network round trips	50 ms = 0.05 s	200 × 0.05 = 10
One SQL statement inside the engine	50 µs = 0.00005 s	200 × 0.00005 = 0.01

The ratio is 10 ÷ 0.01 = 1,000. The typical windows in practice are 1–100 ms for application code and 10–100 µs for a single statement. Taking the ends of those ranges gives ratios from 10× to 10,000×, so "three to four orders of magnitude" describes a common case, not a guarantee. (This is an expected count of overlaps, used as a rough probability measure. At small values it approximates the chance that at least one collision happens.)

The point: same anomaly, same formula, a very large difference in probability, bought purely by where the code lives.

The three fixes, in ascending cost

These are three escapes that recur throughout database design.

Restate the operation so that it commutes. UPDATE accounts SET balance = balance - 100 WHERE id = 7; Two such updates give the same result in either order, and the engine applies each to the latest value. Free. The isolation level becomes irrelevant. This is the right-algebra rule appearing in a place many people do not expect.
Check a version on write. UPDATE accounts SET balance = 200, version = 8 WHERE id = 7 AND version = 7; If zero rows are updated, someone else changed the row, so the code re-reads and retries. This is compare-and-swap (CAS). It costs a retry loop and needs an owner for that loop.
Take the lock. SELECT balance FROM accounts WHERE id = 7 FOR UPDATE; The row is locked until commit, so no other writer can slip in. This is called cursor stability: a level between RC and RR that the standard lacks. Historically it was the answer to lost update, and SELECT ... FOR UPDATE is the fix most application code needs. It costs blocking and a risk of deadlock (two transactions each waiting for a lock the other holds).