How transactions overlap

Two bookstore transactions each protect a business rule, yet their combined history can violate it. Atomicity tells whether each transaction wholly commits; isolation tells which concurrent histories PostgreSQL permits them to observe and create. An isolation level is therefore an application contract, not merely a performance knob.

Read Committed, PostgreSQL's default, gives each statement a snapshot of data committed before that statement began. A later statement in the same transaction can see newer commits. This avoids dirty reads but does not promise that repeated reads remain identical. That weaker contract is often useful when application logic can tolerate a changing world.

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.

Trace the invariant that snapshots miss

The bookstore allows at most one employee to approve a risky refund. Transaction A and B each read that neither has claimed it. A updates its own assignment row; B updates a different row. Under a shared stable snapshot, neither sees the other's uncommitted work and the writes do not target the same tuple, so both can otherwise commit. The final state has two approvers: classic write skew.

Serializable isolation tracks read-write dependencies that can make such a combined history impossible to order serially. PostgreSQL may abort one transaction rather than block every potentially related operation in advance. That abort is the mechanism keeping the serial-order invariant, not a database malfunction. Applications requesting this contract must be prepared to retry the complete transaction from a fresh snapshot.

Common anomalies

Dirty read is the easiest anomaly to reject. If transaction A reads a row inserted by B before B commits, A may act on a fact that never becomes true. PostgreSQL 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.

Choose the contract with the retry

Start from the application invariant, identify the reads whose truth it depends on, and ask which concurrent writes can invalidate that reasoning. A constraint may encode the rule directly; a targeted lock may serialize a narrow conflict; Serializable may reject dangerous histories broadly. If the selected mechanism can abort, define retry around the entire transaction and keep external side effects outside that retryable boundary or make them idempotent.