Isolation Levels and Anomalies

How transactions overlap

Transactions make changes feel atomic, but isolation decides how transactions overlap. This is where databases stop being simple state machines and start managing concurrent timelines. Two transactions can both be correct in isolation and still produce a surprising combined result. Isolation levels name the promises a database makes about those overlaps. They define what application code may safely assume.

Read committed is the common default in many Postgre-like systems. Each statement sees a snapshot of data committed before that statement begins. If a transaction runs two select statements, the second may see rows committed by another transaction after the first ran. This is often practical and efficient. It avoids dirty reads, where a transaction sees uncommitted work that may later abort. It does not promise that repeated reads inside one transaction stay the same.

Stronger snapshots cost more

Repeatable read gives a transaction a stable snapshot. Once the transaction starts, its queries see the same committed world, even as other transactions commit changes. This prevents non-repeatable reads and many phantom surprises for ordinary reads. It works well for reports that need internal consistency. It may still allow write conflicts that must be detected when the transaction tries to update data touched by others.

Serializable isolation gives the strongest common promise: transactions behave as if they ran one at a time in some order. The engine may still run them concurrently, but it must detect and prevent dangerous interleavings. In an MVCC system, this can require tracking predicate dependencies and aborting transactions whose combined reads and writes cannot be serialized safely. The stronger illusion costs more bookkeeping and occasional retries.

Common anomalies

Dirty read is the easiest anomaly to reject. If transaction A reads a row inserted by transaction B before B commits, A may act on a fact that never becomes true. Postgre-like MVCC prevents this by making uncommitted tuple versions invisible to other transactions. The tuple may physically exist, but visibility rules keep it out of the reader's view.

Non-repeatable read occurs when a transaction reads a row, another transaction commits an update, and the first transaction reads the row again with a different value. Under read committed, this can happen across statements. Under repeatable read, it should not, because the snapshot is stable. The question is not whether the database is "correct" in some absolute sense. The question is which contract the transaction asked for.

Phantoms are about sets rather than individual rows. A transaction reads all unpaid invoices over a threshold. Another transaction inserts a new invoice that matches. If the first repeats the predicate and sees the new row, a phantom has appeared. MVCC snapshots can prevent visible phantoms for stable reads, but serializable behavior also has to handle cases where predicates and writes interact in ways that could violate a serial order.

Snapshot isolation is not enough

Write skew shows why stable snapshots are not always enough. Imagine two doctors on call, with a rule that at least one must remain available. Two transactions each read that both are available, then each marks a different doctor unavailable. Under snapshot isolation, neither updates the same row, so both may commit, leaving zero available. Each transaction saw a consistent snapshot, but the combined result violates a cross-row invariant. Serializable isolation has to detect this kind of dangerous pattern.

Applications often choose between handling retries and accepting weaker guarantees. Serializable isolation can abort a transaction whose SQL was valid, simply because its concurrent story could not be made serial. Correct application code has to retry. Read committed avoids many such aborts, but application logic must then be robust to changing reads. The isolation level is an API between the database and the application.

Locks can supplement isolation. A transaction that selects rows for update reserves them for later change. Advisory locks can encode application-specific coordination. Constraints can enforce invariants directly at commit. The best design often combines declarative constraints, appropriate isolation, and targeted locking rather than relying on one mechanism for everything.