Lesson 2 · PostgreSQL Internals

The balance transfer that goes negative

Tied to Mission: Backend Depth — the transactions gap from the baseline assessment.

Here's the scenario that exposed this gap: two requests transfer $100 out of the same $150 account, at the same instant, on Postgres's default isolation level (READ COMMITTED). The app code looks reasonable:

balance = SELECT balance FROM accounts WHERE id = A;
if balance >= 100:
    UPDATE accounts SET balance = balance - 100 WHERE id = A;

Both requests read balance = 150. Both pass the check. Both deduct 100. Final balance: -50. The check meant nothing.

Why READ COMMITTED doesn't stop this

READ COMMITTED means each statement sees whatever's currently committed at the moment that statement runs — it does not lock rows on SELECT, and it does not re-check anything between your SELECT and your later UPDATE. Nothing links the read to the write. Two transactions can both read the same value, both act on it, before either commits.

This is a check-then-act race: the correctness of the check depends on nothing else touching the data between checking and acting — a guarantee READ COMMITTED never makes. Raising the isolation level to SERIALIZABLE would catch this (one transaction gets a serialization-failure error and must retry), but that's a blunt, expensive tool for a problem with a sharper fix.

The core insight

The bug isn't "missing a lock." It's that the check and the write are two separate operations with a gap between them. Close the gap by making the check part of the same atomic write — not by locking around a wider window.

The fix: push the check into the WHERE clause

UPDATE accounts
SET balance = balance - 100
WHERE id = A AND balance >= 100;

Postgres evaluates balance >= 100 against the current row value at the instant it acquires the row's write lock — not against a value read earlier in app code. If two of these run concurrently, Postgres serializes them at the row-lock level: the first commits, deducting to 50; the second re-evaluates balance >= 100 against the now-current 50, fails the condition, and updates zero rows. Check application code for rows_affected == 0 to know the transfer was rejected.

General pattern: whenever a decision depends on current data, make the decision and the write the same atomic statement. The moment you split "read value → decide in app → write" across two round-trips, you've opened a race window — no matter how small it looks.

Check yourself

An inventory system does: SELECT stock; if stock > 0: UPDATE stock = stock - 1. Under READ COMMITTED with two concurrent buyers on the last unit, what happens?
Right — identical shape to the balance transfer. SELECT doesn't lock, doesn't block, and the check-then-act gap lets both writers through. Not quite — SELECT under READ COMMITTED doesn't lock rows or block other readers. Trace through: what does each transaction see, and when does a lock actually get taken?

Primary source

PostgreSQL Docs — Transaction Isolation for exact READ COMMITTED semantics. For the deeper mental model of race conditions across isolation levels (lost update, write skew, phantom reads), read Ch. 7 of Kleppmann's Designing Data-Intensive Applications — see RESOURCES.md.

Questions on SERIALIZABLE, write skew, or how this changes with an ORM's default transaction handling? Ask directly.

← Lesson 1 Reference: Isolation Cheat Sheet →