← Lab Runs in your browser
Two hundred transfers, none of them failed
Three accounts with 1,000 each. Two hundred transfers between them, all at the same time. Every one says it worked, nothing errors, and the accounts end up holding 3,296 instead of 3,000. You can run it yourself below.
Where the money goes
Two transfers happen at the same moment. Both read Alice's balance and see 1,000. The first takes off 30 and writes 970. The second is still holding the 1,000 it read a moment earlier, so it takes off 20 and writes 980. The first transfer's 30 is gone.
Neither write was wrong on its own. The database was told to store a number and it did. Nothing to log, nothing to alert on. You find out weeks later, when a balance doesn't match its history.
The code that does it
const from = await db.read(t.from);
const to = await db.read(t.to);
if (from.balance < t.amount) throw new Error("insufficient funds");
await db.write(t.from, from.balance - t.amount, from.version);
await db.write(t.to, to.balance + t.amount, to.version);Run it
The same thing, in this tab. The accounts are ordinary JavaScript objects and the transfers really do overlap — nothing is faked or pre-recorded. One button runs the broken version, the other a fix.
Loading the demo…
Try the third fix
Nothing gets locked. The database lets everyone read freely, then checks at the end whether anything they read has changed since. If it has, that transaction is thrown out and you run it again.
A second way this breaks
Two doctors are on call. Either can go home, as long as the other one stays. Both check at the same moment, both see two doctors on call, both go home. Neither one overwrote the other, and now nobody is on call.
Locking the row you're changing doesn't help here, because they changed different rows. The problem is what they read.
When locks fight each other
A transfer from account 1 to 2 grabs 1, then 2. One going the other way grabs 2, then 1. Now each is holding what the other is waiting for, and neither will ever finish, so the database kills one of them.
Sorting the two account ids before you grab them fixes it completely. On Postgres that one line was the difference between 70 seconds and 96 milliseconds for the same 40 transfers.
Three ways to fix it
Let the database do the maths
Never read a balance, change it in code, and write it back. Ask the database to subtract instead — one move, no gap. Usually the right answer.
Show it in code
-- One statement. The database reads and writes the
-- row without letting anyone in between.
UPDATE accounts
SET balance = balance - $1
WHERE id = $2
AND balance >= $1;
-- The "do they have enough?" check is part of the
-- same statement, so it cannot go stale.
-- 0 rows changed = not enough money.Claim the rows first
Put a hold on both rows before you read them, so nobody else can touch them. For when you really must look before deciding. You pay in waiting.
Show it in code
BEGIN;
-- Claim both rows up front. Always lowest id first,
-- so two transfers can never end up waiting on
-- each other forever.
SELECT balance FROM accounts WHERE id = $lo FOR UPDATE;
SELECT balance FROM accounts WHERE id = $hi FOR UPDATE;
-- Nobody else can read or write these rows now, so
-- it is safe to decide in your own code.
UPDATE accounts SET balance = balance - $amt
WHERE id = $from;
UPDATE accounts SET balance = balance + $amt
WHERE id = $to;
COMMIT;Let the database referee
Postgres checks at the end whether your transaction could have happened in a sensible order, and throws it out if not. Running it again is your job.
Show it in code
for (let attempt = 1; ; attempt += 1) {
try {
await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
// read, decide, write
await client.query("COMMIT");
return;
} catch (err) {
await client.query("ROLLBACK");
// 40001 = "I could not order these safely"
// 40P01 = "two transactions blocked each other"
// Neither is your bug. Both mean: try again.
if (!RETRYABLE.has(err.code) || attempt >= 5) throw err;
// Wait a random moment, or the retries collide too.
await sleep(Math.random() * 10 * 2 ** attempt);
}
}They don't cost the same
All three get the right answer. Here's what each one cost: the same 200 transfers against a real Postgres, and it's a repo, so you can run it yourself.
| Version | Time | Went through | Thrown out | Money after |
|---|---|---|---|---|
| The broken version | 176 ms | 200 / 200 | 0 | 3,011 |
| Let the database subtract | 170 ms | 200 / 200 | 0 | 3,000 |
| Lock the rows first | 254 ms | 200 / 200 | 0 | 3,000 |
| Serializable, with retries | 398 ms | 137 / 200 | 659 | 3,000 |
| Serializable, no retries | 83 ms | 26 / 200 | 174 | 3,000 |
Letting the database subtract was correct and as fast as the broken version. The strictest option cost the most: it got the right answer by throwing 659 transactions away, and 63 transfers never happened. Partly my five-try limit, mostly because every transfer here fights over the same three rows. With more accounts, most of this goes away.
What this isn't
The browser version isn't Postgres. It copies the timing problem, not the database, and it makes the damage look worse than real life — it loses about 300 out of 3,000 where Postgres lost 11. Postgres also gave a different wrong answer every single run: 3,005, 3,008, 3,009, 3,013, 3,019. That's what makes this kind of bug so miserable to chase.
Where this came from
I built this while revising isolation levels for interviews, and put it up because it's easier to show than to describe.
My last job was backend work on a crypto custody platform for banks. I didn't build the ledger there — address book, service authentication, tests — so nothing here describes that work.