Isolation Levels
Isolation levels define how much concurrent transactions may interfere, trading consistency anomalies against concurrency.
Definition
A transaction isolation level specifies which concurrency anomalies a transaction may tolerate. The classic SQL levels — read uncommitted, read committed, repeatable read, serializable — progressively forbid dirty reads, non-repeatable reads, phantoms, and finally all serialization anomalies. Stronger isolation removes more anomalies but reduces concurrency and increases aborts. The level is a per-transaction or per-session choice, and understanding what your database actually implements matters, since engines vary.
Context and scope
Chosen for every transaction that reads or writes shared data, and a frequent source of subtle bugs when the default is assumed stronger than it is.
Common misconceptions
- Serializable is always safest and therefore best — it is safest but costs concurrency, retries, and may abort more; the level must match the invariant needed
- Read committed prevents lost updates — it prevents dirty reads, but concurrent read-modify-write can still lose an update without explicit locking or versioning
- The standard names match every implementation — engines implement the levels differently; MVCC databases in particular vary
Anti-patterns
- Assuming the default level gives more guarantees than it does, leading to lost updates in production
- Running everything at serializable to "be safe", then suffering aborts and contention without measuring the need
- Mixing explicit locks and a strong isolation level that cause deadlocks or redundant blocking
Related concepts
Related examples
Authoritative source
What it is
Isolation levels describe a spectrum of protection between concurrent transactions. Read uncommitted may see another transaction uncommitted changes. Read committed sees only committed data but new commits can appear mid-transaction, so a repeated read may differ. Repeatable read keeps a stable snapshot so repeated reads agree. Serializable additionally prevents phantoms and other serialization anomalies, making concurrent transactions behave as if run one after another.
Why it matters
The anomalies these levels forbid are exactly the bugs that are hardest to reproduce: an update lost between two users, a row count that changes mid-report, a constraint violated by a phantom insert. Choosing too weak a level leaves those latent; choosing too strong a level, such as serializable for every query, invites contention and aborts that undermine throughput. The level is a deliberate trade, not a setting to ignore.
How to use it well
Identify the invariant a transaction must preserve and pick the weakest level or explicit lock that guarantees it — often read committed plus optimistic versioning suffices. Read what your engine actually implements, since well-known names like repeatable read vary under MVCC. Handle aborts from stronger levels as retried work rather than failures, test the concurrency paths that the level allows to interfere, and keep transactions short so the chosen level has less room to matter.