Notestransactions🌱 seedling

Isolation Level

Also: Transaction Isolation Level · Isolation Levels · ANSI Isolation Levels · Weak Isolation

An isolation level is a contract about which concurrency anomalies a database allows. Weaker levels admit more anomalies in exchange for less blocking and fewer aborts; only [[Serializability]] admits none.

Why it exists

Serializability costs throughput or aborts, so databases offer weaker levels and default to one of them. The application must know which anomalies remain and design around them.

How it works

Level Prevents Still allows
Read Committed Dirty Reads, dirty writes read skew, Lost Updates, Write Skew, Phantom Reads
Snapshot Isolation the above + read skew; lost updates in most engines write skew, phantom-driven write skew
Serializable everything nothing
  • SQL-92 defined four levels by three phenomena (dirty read, non-repeatable read, phantom). Those definitions are ambiguous and miss dirty writes, lost updates and write skew, and cannot describe snapshot isolation.
  • The precise modern definitions treat each level as forbidding certain cycles in the graph of dependencies between transactions (write-write, write-read, read-write).

Trade-offs

Weaker level Stronger level
more concurrency, fewer aborts fewer anomalies to reason about
invariants enforced by hand: locks, constraints blocking or retries on conflict

In practice

  • Names lie: Postgres "Repeatable Read" is snapshot isolation; MySQL's default "Repeatable Read" is weaker than snapshot isolation and allows lost updates; Oracle's "Serializable" is snapshot isolation.
  • Defaults: Read Committed in Postgres, Oracle and SQL Server; Repeatable Read in MySQL; Serializable in CockroachDB.

Pitfalls

  • Choosing a level by name across databases. Test the anomalies you care about.
  • Replicas can provide weaker isolation than the primary, especially for reads routed to them.

Recall

These cards are also in your review deck, scheduled with spaced repetition.