Case Study 1 — The Transfer That Half-Happened

Atomicity isn't abstract — its absence loses money. A payments feature performed a multi-step balance transfer without a transaction, and an occasional mid-operation failure left money debited from one account but never credited to the other. Wrapping it in a transaction made the bug impossible.

Background

A wallet feature let users transfer balance between accounts. The code did the obvious two steps — debit the sender, credit the receiver — as separate statements, each auto-committed (no explicit transaction):

-- Step 1 (its own auto-committed statement):
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- ... application does some work, maybe a network call ...
-- Step 2 (its own auto-committed statement):
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

Most of the time it worked. But occasionally — a crash, a deploy restart, a timeout, an error between the two statements — step 1 committed and step 2 didn't. Money vanished: debited from account 1, never credited to account 2. The ledger no longer balanced. Finance found discrepancies during reconciliation, and affected users (rightly) complained their money had disappeared.

What went wrong: no atomicity

Because each UPDATE auto-committed independently, there was no unit of work binding them. The system could (and did) end up in the forbidden state: "the money left but never arrived." This is precisely the atomicity failure — a multi-step operation that must be all-or-nothing was allowed to be partially applied. The gap between the two statements was a window for inconsistency, and at scale, rare windows happen constantly.

The same hazard underlies "place an order" (insert order + insert items + decrement inventory), "enroll" (insert enrollment + increment count), and any operation that changes more than one thing that must agree.

The fix: a transaction

Wrap both steps in a single transaction so they commit together or not at all:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Now the two updates are one atomic unit. If anything fails between them — an error, a crash, even a power loss — the transaction is rolled back (or never committed), and the database recovers (via the WAL, Chapter 28) to the state before the transfer. The money is never debited without also being credited. The forbidden "half-happened" state is now unrepresentable.

The team also added a CHECK (balance >= 0) constraint and, for the overdraft-prevention logic, used row locking (SELECT ... FOR UPDATE, Chapter 27) so two concurrent transfers from the same account couldn't both pass the balance check — but the atomicity fix (the transaction) was the one that stopped money from disappearing.

The analysis

  1. Multi-step changes that must agree need a transaction — always. Any operation that modifies more than one row/table where a partial result is wrong must be atomic. Debit+credit, order+items+inventory, enrollment+count: all transactions, no exceptions.

  2. Auto-commit makes each statement its own transaction. Two separate auto-committed statements have no atomic relationship — there's a gap between them where the system can be interrupted. Only an explicit BEGIN ... COMMIT binds them.

  3. Rare windows happen at scale. "It almost always works" means "it fails sometimes," and at thousands of operations a day, sometimes is often. The bug wasn't a coding error in either statement — it was the absence of a unit of work around them.

  4. Atomicity makes the bad state unrepresentable. This echoes the book's design theme: the best fix isn't "be careful between the statements," it's structuring the work so the half-applied state cannot occur. A transaction does exactly that.

  5. Durability backs atomicity. Even a power loss mid-transaction is safe: the WAL ensures the database recovers to a consistent state (the transfer either fully committed or fully not). Atomicity + durability together are why you can trust a database with money.

Discussion questions

  1. Explain precisely how the no-transaction version could lose money.
  2. Why does auto-commit (two separate statements) fail to provide atomicity?
  3. How does wrapping the steps in BEGIN ... COMMIT make the half-applied state impossible — even across a crash?
  4. Name three other operations that must be atomic for the same reason.
  5. ⭐ Atomicity stops money from disappearing, but two concurrent transfers could still both pass a balance check and overdraw the account. Why is that a separate problem (concurrency), and what tool addresses it (preview of Chapter 27)?