Isolation Levels

DatabasesSeniordb

The Question

What are the database isolation levels and what anomalies does each prevent?

What a Strong Answer Covers

• All four in order. Three anomaly terms: dirty
• non-repeatable
• phantom.

Senior-Level Answer

Database isolation levels define how and when the changes made by one transaction become visible to other concurrent transactions. SQL defines four standard levels, each preventing an additional set of read anomalies at the cost of greater contention.

Read Uncommitted is the weakest level. A transaction can read changes made by other transactions that have not yet committed. This allows dirty reads -- reading data that may be rolled back, producing a view of the database that never actually existed. Almost never used in production.

Read Committed prevents dirty reads -- a transaction only sees data that has been committed. However, it allows non-repeatable reads: if transaction A reads a row, transaction B updates and commits that row, and A reads it again, A sees different values for the same row within the same transaction. This is the default in PostgreSQL and Oracle.

Repeatable Read prevents dirty reads and non-repeatable reads. Once a transaction reads a row, that row will return the same data for the duration of the transaction, even if another transaction modifies it. MySQL InnoDB uses MVCC (Multi-Version Concurrency Control) to implement this efficiently. However, phantom reads are still possible: a transaction running the same range query twice may see different sets of rows because another transaction inserted or deleted rows in that range. Repeatable Read is the default in MySQL InnoDB.

Serializable is the strongest level. Transactions execute as if they were run serially, one after another. This prevents all three anomalies. Implementations typically use range locks or predicate locks. PostgreSQL implements Serializable Snapshot Isolation (SSI), which uses MVCC with conflict detection rather than blocking locks, achieving full serializability with better concurrency than traditional lock-based approaches.

In practice, most applications use Read Committed or Repeatable Read. Choose Serializable only when correctness absolutely requires it -- for example, financial transactions where a race condition would produce double-spending.

Key Differences

What Separates a 2/3 from a 3/3

Common Mistakes

• Confusing non-repeatable reads (same row, different value) with phantom reads (different set of rows in a range query)
• Thinking Serializable always uses locks -- PostgreSQL's SSI avoids many locks by using conflict detection
• Not knowing the real defaults: PostgreSQL and Oracle use Read Committed, MySQL InnoDB uses Repeatable Read
• Treating higher isolation as always better -- Serializable significantly reduces throughput under contention

Follow-Up Questions

• What is MVCC and how does it improve concurrency? — MVCC maintains multiple versions of each row; readers see a consistent snapshot at their transaction start time, so reads do not block writes and writes do not block reads.
• What is a write skew anomaly? — Write skew occurs when two transactions each read overlapping data and write non-overlapping data, creating a combined state that violates a constraint. Only Serializable prevents it.
• How does SELECT FOR UPDATE interact with isolation levels? — SELECT FOR UPDATE acquires a row-level write lock, preventing concurrent modifications -- useful for implementing pessimistic locking patterns on top of standard isolation.

Related Questions

• ACID
• Indexes
• Clustered vs Non-clustered
• Normalization
• PostgreSQL Internals — MVCC, WAL, VACUUM