Transaction Isolation Levels: The Anomaly Each One Actually Stops

The SQL standard defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each one rules out one more class of anomaly than the one below it.

Read Uncommitted allows everything, including dirty reads, where one transaction sees uncommitted writes from another. Almost nobody runs at this level. Read Committed bans dirty reads but still allows non-repeatable reads: if you read the same row twice in one transaction, you can see different values because another transaction committed in between. Repeatable Read bans non-repeatable reads on individual rows but still allows phantoms: a range query rerun in the same transaction can return new rows that were inserted by a committer. Serializable bans phantoms too, and the result is supposed to be equivalent to running every transaction one at a time.

The first surprise is that Postgres REPEATABLE READ is not the standard's Repeatable Read. It is snapshot isolation. The transaction sees a consistent snapshot of the database as of its start time, which incidentally prevents both non-repeatable reads and phantoms for read-only queries. What snapshot isolation does not prevent is write skew: two transactions read the same data, each makes a decision based on it, and both commit because they wrote to different rows.

The realistic default for most applications is Read Committed. It is fast, it composes well with ORMs that hold connections briefly, and it covers the anomalies most application code is naive about.

The production failure I have watched twice is a banking app sitting at Read Committed for a money transfer. The transaction reads the source account balance, checks it is high enough, then issues two UPDATE statements: one to debit the source, one to credit the destination. Under low load it works fine. Then a user fires two parallel transfers from the same account in the same second. Both transactions read balance=100. Both decide the transfer is fine. Both write the new balance back. The account ends up with phantom funds, off by one whole transfer, and the ledger no longer reconciles. Three hundred K of credits with no matching debits had to be clawed back manually.

The fix is small. Either lock the source row with SELECT ... FOR UPDATE so the second transaction blocks until the first commits, or run the transfer at SERIALIZABLE and let Postgres abort one of the racing transactions for you. Pick the anomaly you cannot tolerate, then pick the lowest level that prevents it.