A financial application runs at read-committed isolation. Two concurrent transactions each read an account balance, check it is sufficient, and debit it. Both succeed and the account goes negative. What happened, and which fixes are correct?

What happened

A lost update through a read-modify-write race, which read-committed explicitly permits.

T1: read balance = 100
T2: read balance = 100
T1: check 100 >= 80  → ok, write balance = 20, commit
T2: check 100 >= 80  → ok, write balance = 20, commit

Two withdrawals of 80 from a balance of 100, ending at 20. Neither transaction saw uncommitted data, so read-committed's guarantee was not violated — that guarantee simply does not cover this case. Isolation levels describe which anomalies are permitted, and read-committed permits this one.

The insidious part is that it is timing-dependent. The application is correct under light load and becomes incorrect under contention, which is exactly when the money is moving.

The fixes that work

1. Atomic conditional update — the best answer.

UPDATE accounts SET balance = balance - 80
 WHERE id = ? AND balance >= 80;

Check and update in one statement, then verify rows affected. The database takes the row lock, the comparison happens against current committed state, and there is no window. No extra isolation level, no retry logic, no application-level locking. When the invariant can be expressed as a predicate on the row being written, this is almost always right.

2. Pessimistic locking. SELECT ... FOR UPDATE before the check serialises access to the row. Correct, and it costs concurrency: transactions queue on the row, and holding the lock across any network call (a payment provider, a rules service) is how a lock becomes an outage.

3. Optimistic concurrency with a version column. Read the version, include it in the update predicate, retry on zero rows affected. Excellent for low contention, and it degrades badly under high contention because retries multiply exactly when the resource is busiest.

4. Serialisable isolation. Correct by definition, at the cost of aborts the application must retry. Right when the invariant spans multiple rows or tables — "total across these accounts must not go negative" — where a single-row predicate cannot express it.

The fixes that do not work

• Repeatable read. In some implementations it prevents this and in others it does not; relying on engine-specific behaviour for a financial invariant is not a design.
• Application-level checking with a longer transaction. Widens the window rather than closing it.
• A distributed lock in front of the database. Adds a failure mode and a fencing problem to solve something the database already does correctly.
• Retrying on failure. There is no failure. Both transactions succeeded.

The general principle

Invariants belong where the data is, expressed so the database can enforce them. Every invariant enforced by reading, deciding in application code and then writing is a race waiting for enough concurrency. When the check cannot be expressed as a predicate on the write, that is the signal to move up to serialisable or to restructure the data so it can be.