Transaction Isolation Levels

Isolation levels trade concurrency for protection against dirty reads, non-repeatable reads, phantom reads, and serialization anomalies. Match the level to the invariant.

data-integrity

Definition

Transaction isolation levels define which concurrent transaction effects a transaction may observe. Read uncommitted allows dirty reads; read committed prevents them but allows non-repeatable reads; repeatable read keeps rows stable but may allow phantoms depending on the engine; serializable guarantees an outcome equivalent to some serial order, often at the cost of retries or locking. Databases implement these guarantees differently, so the level name alone is not a complete specification.

Context and scope

Isolation matters for balances, inventory, permissions, and any multi-row invariant. Higher isolation reduces anomalies but increases contention, lock waits, and retry handling. Some systems offer snapshot isolation with write skew risks, which is not the same as serializability.

Common misconceptions

Serializable means no retries are needed — serializable transactions can abort on conflicts and must be retried
Repeatable read prevents all phantoms — behavior varies by database; PostgreSQL uses snapshots while other engines may lock ranges
Read committed is always safe — it still allows a transaction to see different values on repeated reads

Anti-patterns

Using read committed for a check-then-act invariant without locking or a uniqueness constraint
Assuming the same isolation semantics across database engines
Retrying serialization failures without idempotency or a bounded retry policy

Related concepts

Related examples

Authoritative source

https://www.postgresql.org/docs/current/transaction-iso.html

Anomalies in plain terms

Dirty read — seeing another transaction’s uncommitted write.
Non-repeatable read — reading the same row twice and getting different values.
Phantom read — a repeated query returns new rows because another transaction inserted them.
Write skew — two transactions read overlapping data and write disjoint rows, violating a cross-row invariant under snapshot isolation.

Choosing a level

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- read the invariant
-- write the dependent rows
COMMIT;

Use read committed for independent single-row operations. Use repeatable read or serializable for multi-row invariants. If you use serializable, handle serialization failures with bounded retries and idempotent writes.

-- PostgreSQL: inspect and set the default
SHOW default_transaction_isolation;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

When to use which

Use the lowest level that preserves the invariant. Compared to optimistic concurrency with a version column, isolation levels protect a transaction’s view rather than a single row update. Compared to explicit locks, serializable may avoid deadlocks by aborting instead of waiting, but requires retry logic.

Pitfalls

Check-then-act logic in read committed can race; enforce invariants with constraints, locks, or a higher level. Retrying a serialization failure without idempotency can duplicate effects. Test concurrency with two sessions; single-threaded tests do not exercise isolation.