Database isolation levels

Here is a piece of code with no bug in it:

SELECT balance FROM accounts WHERE id = $1;
-- application checks: balance >= amount?
UPDATE accounts SET balance = balance - $1 WHERE id = $2;

Read the balance, check there’s enough, subtract. Every step is correct. Run two of them at once against a default PostgreSQL configuration and you can drive the balance negative.

Two requests arrive together. Both read a balance of 500. Both check whether 500 is at least 400 — it is. Both subtract 400. The row ends at −300.

The bug isn’t in the code. It’s one layer down, in a database setting you probably never touched.

The default nobody questions

READ COMMITTED is PostgreSQL’s default isolation level. It is the most-used level in the most-used open-source database, and it permits exactly what you just saw.

READ COMMITTED guarantees one thing: you will not read data another transaction hasn’t committed. That’s the whole guarantee. Between your SELECT and your UPDATE, another transaction is free to read the same row, change it, and commit. Your UPDATE then overwrites its work without ever knowing it existed.

The four anomalies

The isolation levels are defined by which of these they permit:

¹ PostgreSQL’s REPEATABLE READ actually prevents phantom reads too — its implementation is stronger than the SQL standard requires. This is a good example of why “we use REPEATABLE READ” tells you less than you’d think without knowing the engine.

Dirty read. You read data that hasn’t been committed. If the writing transaction rolls back, you’ve read a value that never existed. Only possible under READ UNCOMMITTED, which almost nobody uses deliberately.

Non-repeatable read. You read the same row twice in one transaction and get different values, because someone else committed a change in between.

Phantom read. You run the same SELECT twice and get more rows the second time. Someone inserted rows matching your predicate.

Lost update. The balance problem above. Two transactions read a value, both compute a new value from what they read, and the second commit silently discards the first.

Lost update is the one that matters in practice, and note where it sits in that table: possible at the default level.

Two ways to close it

The obvious fix is SERIALIZABLE:

await db.transaction(
  async (tx) => {
    const [{ balance }] = await tx.query(
      "SELECT balance FROM accounts WHERE id = $1",
      [accountId],
    );
    if (balance < amount) throw new Error("Insufficient funds");
    await tx.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [
      amount,
      accountId,
    ]);
  },
  { isolationLevel: "serializable" },
);

It works, and it has a cost that people underestimate. PostgreSQL implements SERIALIZABLE optimistically: it doesn’t block, it detects conflicts at commit time and aborts one of the transactions with a serialization failure. Which means every transaction you run at SERIALIZABLE needs retry logic. If you don’t write the retry loop, you’ve swapped a silent wrong answer for a loud failed request.

The more targeted fix, and the one I reach for by default:

await db.transaction(async (tx) => {
  const [{ balance }] = await tx.query(
    "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE",
    [accountId],
  );
  if (balance < amount) throw new Error("Insufficient funds");
  await tx.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [
    amount,
    accountId,
  ]);
});

FOR UPDATE takes a row lock. Any other transaction that tries to read that row FOR UPDATE waits until you commit. Same correctness, but scoped to the one row you actually care about, no impact on anything else in the table, and it works fine under READ COMMITTED — you don’t have to change the isolation level at all.

Which to use

I’m not going to tell you to set everything to SERIALIZABLE; the throughput cost and the retry burden are real. What I’d actually do:

READ COMMITTED for reads you don’t act on — displaying data, building reports. Concurrency isn’t a problem when you aren’t writing a value derived from what you read.

SELECT FOR UPDATE for read-modify-write cycles: checking a balance and changing it, reserving inventory, decrementing a quota. This is the most common case by a wide margin and the one people get wrong.

SERIALIZABLE when the invariant spans multiple rows or tables and you can’t know up front which rows to lock. Rare — but when you need it nothing else will do, and you must write the retry loop.

Most balance bugs I’ve seen aren’t exotic race conditions. They’re someone running SELECT balance, checking it in application code, then running UPDATE balance = balance - amount, with no lock and often no transaction at all. That isn’t a database problem. It’s a “nobody thought about concurrency” problem, and the database was configured to let it through.

Sources

1. PostgreSQL docs: Transaction Isolation — the authoritative table of what each level permits in Postgres specifically, including where it’s stricter than the standard.
2. PostgreSQL docs: Explicit Locking
3. Martin Kleppmann, Designing Data-Intensive Applications, chapter 7 (Transactions). The clearest treatment of why the standard’s levels are defined by which anomalies they forbid rather than by what they do.