A lock was held — and I still lost a payment

A SELECT ... FOR UPDATE was doing its job perfectly, and I still lost a payment.

Two payments of 100 against the same loan, three instalments of 100 each. The only correct outcome is 100 outstanding. I got 200.

The lock wasn’t the problem

Both transactions took a pessimistic write lock on the loan before touching anything, so they never wrote at the same time. The lock worked. The isolation level was the problem.

On MySQL/InnoDB the default is REPEATABLE READ, and a transaction’s read snapshot is pinned by its first non-locking read — not by BEGIN, and not by the lock. So:

1. T2 runs a harmless validation query. Snapshot pinned: sees 300.
2. T1 pays 100 and commits. The real balance is now 200.
3. T2 acquires the lock. This part is correct: a locking read returns the latest committed row.
4. T2 re-reads the instalments. Still 300. Stale.
5. T2 writes balances computed from 300. T1’s payment is overwritten.

No exception. No log line. Numbers plausible enough that nobody notices for weeks.

The fix was one word

READ_COMMITTED on that transaction, so every statement re-evaluates against committed state. Not a free upgrade: you give up repeatable reads and gap locks, so it belongs on the methods that lock and then read, not on the whole application.

The rule I took away

If a transaction acquires a lock and then reads more rows to decide what to write, those reads need READ COMMITTED or their own locking reads. Otherwise the pre-lock snapshot reintroduces exactly the lost update the lock was there to prevent.

In systems that touch money, this class of bug is the worst kind: nothing visibly breaks, the numbers are just wrong. That’s why the payment flows in our systems have integration tests that run against a real database — including one that reproduces exactly this scenario with two concurrent transactions.