DB Isolation Levels
Runs in browserVisualize transaction isolation anomalies
Isolation Level
Database State
How to Use
- Select an isolation level
- Begin Transaction A
- Read/Write in Transaction A
- Begin Transaction B (concurrent)
- Observe anomalies (or lack thereof)
Transaction A
idleTransaction B
idleThe Definitive Guide to Database Isolation Levels
Transaction isolation is the conceptual cornerstone of modern relational databases. It dictates exactly how the database engine handles the inevitable chaos of hundreds or thousands of concurrent clients trying to read and write the exact same rows simultaneously. It represents the "I" in the fundamental acronym ACID (Atomicity, Consistency, Isolation, Durability).
Ideally, every transaction would run in a vacuum as if it were the only operation happening on the server. This is called perfect Serializability. However, enforcing perfect serializability requires taking massive locks on entire tables and grinding the database to a completely single-threaded halt—a catastrophic performance tradeoff at scale. Therefore, databases offer differing "Isolation Levels" to allow developers to intentionally trade away absolute mathematical correctness in favor of significantly higher concurrency and throughput.
1. Concurrency Anomalies (Read Phenomena)
The 1992 ANSI SQL standard defined isolation levels entirely by which specific data corruption anomalies (phenomena) they mathematically mathematically prevented. To understand isolation, you must first understand the anomalies.
Anomaly 1: Dirty Read
Reading uncommitted, temporary data from another transaction.
Transaction A updates an account balance from $100 to $50, but hasn't committed yet. Transaction B reads the balance, sees $50, and proceeds to ship a product. Suddenly, Transaction A encounters an error and ROLLS BACK. The balance reverts to $100. Transaction B has made permanent business decisions based on data that technically never existed in the database's permanent history.
Anomaly 2: Non-Repeatable Read (Fuzzy Read)
Reading the same row twice, but getting different results within a single transaction.
Transaction B reads a user's age as 25. Transaction A then updates the age to 26 and COMMITS. Transaction B, still in progress, reads the age a second time and sees 26. The read is not "repeatable." This causes massive bugs if Transaction B is calculating a complex report that depends on the age remaining constant throughout the duration of the report generation.
Anomaly 3: Phantom Read
Running a range query twice, but getting different rows within a single transaction.
Transaction B runs SELECT * FROM users WHERE status = 'active' and sees
10 users. Transaction A inserts a completely new row with
status = 'active'
and commits. Transaction B runs the exact same query again and sees 11 users. A new "phantom"
row appeared out of nowhere. Notice the difference: Non-Repeatable Read alters
existing
rows, while Phantom Read introduces or removes rows from a
range.
2. The Four Standard Isolation Levels
By systematically preventing the anomalies above, the SQL standard defines four increasing levels of strictness. Higher levels increase consistency but drastically decrease performance.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Allowed | Allowed | Allowed |
| Read Committed | Prevented | Allowed | Allowed |
| Repeatable Read | Prevented | Prevented | Allowed |
| Serializable | Prevented | Prevented | Prevented |
Read Uncommitted: Almost never used in production. It simply reads the latest memory state of the database page, bypassing all locks and rules. It's essentially the wild west. Fast, but dangerous.
Read Committed: The default for most databases (PostgreSQL, SQL Server, Oracle). A query will only ever read data that was fully committed before the specific query began. However, if you run the query twice in a transaction, the second query might see new data committed in between the two statements.
Repeatable Read: The default for MySQL (InnoDB). A transaction takes a "snapshot" of the database state when the transaction begins. Every query inside that transaction will see the exact same snapshot, ignoring any changes committed by other transactions afterward.
Serializable: The ultimate guarantee. The database guarantees that if transactions run concurrently, the final result is exactly identical to running them sequentially, one by one. This prevents write-skew anomalies. This is incredibly slow and often causes transactions to be aborted and retried due to lock contention.
3. How it Works Internally: MVCC vs Locking
Historically (e.g., early SQL Server), databases implemented these levels using brute-force Pessimistic Locking. To prevent a Dirty Read, a reader simply couldn't read a row that a writer was modifying—the reader had to physically pause and wait for the writer to commit. "Writers block readers."
MVCC: Multi-Version Concurrency Control
Modern databases (Postgres, MySQL, Oracle) use MVCC. Under MVCC, writes do not overwrite the old data in-place. Instead, they insert a new version of the row. Every row belongs to a specific Transaction ID (XID).
Because old versions are kept, "Readers never block Writers, and Writers never block Readers." If Transaction A is writing version 2 of a row, Transaction B can simply read version 1. The database calculates exactly which version of the row Transaction B is allowed to see based on its Isolation Level rules. The old versions are eventually deleted by a background Garbage Collector (Vacuum in Postgres, Purge in InnoDB).
4. Quirks of Real-World Databases
The ANSI SQL standard was written in 1992 based on lock-oriented architectures. Because modern databases use MVCC, they often provide stronger guarantees than the ANSI standard strictly requires for a given level.
PostgreSQL
- Default: Read Committed.
- Quirk: Postgres's implementation of Repeatable Read is so strong (thanks to MVCC) that it automatically prevents Phantom Reads. If you request RR in Postgres, you are getting near-Serializable protections without lock penalties.
- Quirk: Requesting Read Uncommitted in Postgres silently upgrades you to Read Committed. Postgres physically cannot read dirty uncommitted data due to MVCC.
MySQL (InnoDB)
- Default: Repeatable Read.
- Quirk: InnoDB explicitly prevents Phantom Reads in Repeatable Read using Gap Locks and Next-Key Locks. It locks the index gaps between existing rows to prevent another transaction from inserting new un-phantomable rows in that numeric range.
Comprehensive References
- PostgreSQL Official Docs: Transaction Isolation - Highly recommended reading for MVCC implementation details.
- MySQL InnoDB: Transaction Isolation Levels
- Jepsen: Consistency Models - An intense, academic deep dive into Distributed Consistency models beyond simple ACID logic.