Database Isolation: Why Two Committed Transfers Can Create Money

Two wallet transfers can both be successful while simultaneously creating money. In this scenario, a specific query pattern is at play: reading a balance, validating it in Go, calculating a replacement value, and then writing that value later. Importantly, a transaction boundary does not guarantee that the original read remains current.

The key question is whether overlapping transactions preserve the business invariant. To explore this, the lab uses a three-account wallet, PostgreSQL isolation experiments, and concurrent tests.

The starting point is that Alice, Bob, and Charlie each have 1,000,000. A transfer should change the distribution of these balances while keeping the sum at 3,000,000. Additionally, each balance must remain non-negative. The schema implements a row-level invariant: balance BIGINT NOT NULL CHECK (balance = 0). However, this constraint does not enforce overall money conservation across accounts.

A faulty transfer could result in every individual balance remaining positive while the total sum increases. Therefore, correctness requires both checks.

The TransferNaive implementation uses sql.LevelReadCommitted. This transaction begins by reading the sender's balance, checking for sufficient funds, then reading the receiver's balance. After validating, it updates both accounts, inserts an audit record, and commits. The critical issue is that these operations share an atomic boundary, meaning their decisions can still depend on stale application values.

Consider the overlap scenario: Transfer A sends 800,000 from Alice to Bob, while Transfer B sends 800,000 from Alice to Charlie. Both transactions initially read Alice's balance at 1,000,000 before either writes. Each transaction concludes that the transfer is allowed and calculates Alice's new balance as 200,000. However, the naive sender update uses this calculated value instead of deriving the new balance from the current row.

Consequently, the second write can overwrite Alice's balance with the same 200,000, resulting in Bob and Charlie each receiving a credit of 800,000. The expected final state under TestNaiveTransfer_LostUpdate is Alice having 200,000, and Bob and Charlie each having 1,800,000, for a total of 3,800,000. The test orchestrates this overlap explicitly through channels, allowing both transactions to signal when their reads are complete before releasing their writes.

The crucial point is that the lost update vulnerability arises from the query pattern itself. Alice still has 200,000, and there is no negative balance to reject the transaction. The missing debit is simply a lost update, and the violated invariant is the cross-account sum. While a failed transaction would reject the transfer due to a negative balance, this does not necessarily mean that every READ COMMITTED update loses data.

The vulnerable implementation reads, calculates in application memory, and later writes an absolute value. Its query pattern is part of the explanation.

To put it simply, being in READ COMMITTED mode does not guarantee data integrity in every case. The implementation must separate the SQL standard from PostgreSQL behavior to ensure proper handling of concurrent transactions and prevent lost updates.