Database Transaction Isolation Levels Under the Hood: A Step-by-Step Walkthrough
A rigorous deep dive into relational database concurrency controls — covering Read Uncommitted, Read Committed, Repeatable Read, Serializable, read/write anomalies, MVCC row visibility, and Write Skew.
In a modern web application, hundreds of users read and write to the database simultaneously. When you book a seat on a flight, buy a product in an online store, or transfer money between bank accounts, the database must execute multiple operations. If these operations overlap without proper boundaries, the data becomes corrupt: two users book the same seat, inventory counts drop below zero, or money vanishes into thin air. To prevent this, relational databases use **Transactions** backed by the **ACID** security model.
The "I" in ACID stands for **Isolation**. It guarantees that concurrently running transactions do not interfere with each other, making the execution of concurrent transactions feel as if they were running one after another (sequentially). But enforcing perfect isolation requires locks, which slows down the database. To balance safety and speed, databases allow developers to select different **Transaction Isolation Levels**. Selecting the wrong level is a leading cause of subtle, hard-to-debug concurrency anomalies. This guide will walk you through the inner workings of isolation levels, how Multi-Version Concurrency Control (MVCC) implements them under the hood, and how to verify transaction safety in your database.
1. The Core Concept: ACID Transactions and the Concurrency Problem
1.1 Why We Need Isolation Boundaries
A database transaction is a logical unit of work that contains one or more SQL statements (e.g. reading a balance, deducting a fee, writing a new balance). To maintain data integrity, transactions must conform to the ACID properties: Atomicity (all or nothing), Consistency (data invariants are preserved), Isolation (hidden intermediate states), and Durability (committed writes persist). Isolation is the most complex property to implement because it requires coordinating access to shared data resources among concurrent threads.
Without isolation, the intermediate steps of Transaction A would be visible to Transaction B. For example, if you transfer $100 from Account 1 to Account 2, the database must deduct $100 from Account 1, then add $100 to Account 2. If another transaction runs in the middle of these two steps, it will read a state where the $100 has vanished from Account 1 but has not yet arrived in Account 2, leading to temporary (or permanent) reporting errors. Isolation boundaries prevent threads from reading uncommitted, intermediate states.
1.2 Concurrency Anomalies Defined
Concurrency anomalies are semantic bugs that occur when two or more transactions interleave. These anomalies violate database consistency invariants. If left unchecked, they can lead to financial errors, security bypasses, or data corruption. To manage these anomalies, relational databases define specific isolation levels that selectively block these anomalies. Managing this balance requires choosing the appropriate isolation level for the specific query patterns of your application.
Common Misconception — ACID guarantees Serializable Isolation by default: A common misconception is that if your database is ACID-compliant, all transactions run safely in isolation. In reality, most databases default to a lower isolation level (like Read Committed in PostgreSQL and SQL Server, or Repeatable Read in MySQL) to maintain high performance. Under these default settings, your application remains vulnerable to specific concurrency anomalies like phantom reads or write skew.
2. The Four Transaction Anomalies
2.1 Dirty Reads
A **Dirty Read** occurs when Transaction A modifies a row, and Transaction B reads that modified row *before* Transaction A has committed its changes. If Transaction A subsequently aborts (rolls back), the data read by Transaction B is invalid, as it never officially existed in the database. This can lead to critical logical failures, such as shipping a product based on a pending order transaction that was ultimately rolled back.
2.2 Non-Repeatable Reads (Fuzzy Reads)
A **Non-Repeatable Read** occurs when Transaction A reads a row, and Transaction B subsequently modifies or deletes that row and commits the change. If Transaction A re-reads the same row within the same transaction, it finds the row has changed or vanished. This violates the expectation that data should remain stable during the lifetime of a single transaction.
2.3 Phantom Reads
A **Phantom Read** occurs when Transaction A executes a query returning a set of rows matching a specific search condition (e.g. SELECT * FROM users WHERE age > 30). Transaction B then inserts a *new* row matching that condition and commits. If Transaction A executes the exact same query again, it finds "phantom" rows that were not present in the first read. Unlike non-repeatable reads (which affect existing rows), phantom reads deal with the addition of *new* rows that match a range query.
2.4 Write Skew
A **Write Skew** anomaly is a complex, semantic concurrency bug that occurs when two transactions read overlapping data sets, make modifications based on those reads, and commit, violating an app-wide invariant. A classic example is a doctor on-call rotation: the system requires at least one doctor to be on call. Doctor A and Doctor B are both on call. They both simultaneously attempt to leave. Transaction 1 (Doctor A) queries the database: "Are there > 1 doctors on call?" (Yes). Transaction 2 (Doctor B) queries the same: "Are there > 1 doctors on call?" (Yes). Transaction 1 deletes Doctor A; Transaction 2 deletes Doctor B. Both commit successfully. Now, zero doctors are on call, violating the application invariant. This anomaly occurs because neither transaction modified a row the other modified, allowing locks on individual rows to succeed while failing to protect the global state query.
3. The ANSI SQL Isolation Levels
3.1 The ANSI SQL Standard Matrix
The ANSI SQL-92 standard defines four transaction isolation levels. Each level is defined by the anomalies it prevents. The relationship is summarized in this matrix:
| Isolation Level | Dirty Reads | Non-Repeatable Reads | Phantom Reads | Write Skew |
|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible* | Possible |
| Serializable | Prevented | Prevented | Prevented | Prevented |
*Note: In MySQL (InnoDB), Repeatable Read uses gap locking, which prevents phantom reads in addition to non-repeatable reads. In PostgreSQL, Repeatable Read also prevents phantom reads but remains vulnerable to Write Skew.
Pitfall — Assuming Repeatable Read Prevents Write Skew: Many developers think that if their database isolation is set to "Repeatable Read", their application data is completely safe from concurrency bugs. As demonstrated by the doctor on-call example, write skew can slip through Repeatable Read. To prevent write skew, you must either enforce explicit locking using SELECT ... FOR UPDATE or raise the isolation level to SERIALIZABLE.
4. How Databases Isolation Works: Two-Phase Locking
4.1 Locking Rules
Historically, databases enforced isolation using locks. The standard implementation is **Two-Phase Locking (2PL)**. It uses two types of locks: (1) **Shared Locks (S-Locks)**: acquired when reading a row. Multiple transactions can hold shared locks on the same row concurrently. (2) **Exclusive Locks (X-Locks)**: acquired when modifying a row. Only one transaction can hold an exclusive lock on a row, blocking all other readers and writers.
Two-Phase Locking requires that a transaction acquire locks during its execution phase and cannot release any locks until it enters the shrink phase (usually at commit or rollback). Under SERIALIZABLE isolation using Strict 2PL, the database holds all S-locks and X-locks until the transaction commits, ensuring that no other transactions can modify data read by the current transaction, completely preventing conflicts but severely reducing concurrency.
Mermaid Diagram: Locking protocol showing an Exclusive write lock blocked by an active Shared read lock.
5. Modern Concurrency: Multi-Version Concurrency Control (MVCC)
5.1 Readers Do Not Block Writers
Because locking readers blocks writers (and vice versa), pure lock-based systems suffer from poor performance. To solve this, modern databases (PostgreSQL, MySQL, Oracle) implement **Multi-Version Concurrency Control (MVCC)**. The core philosophy of MVCC is: **readers do not block writers, and writers do not block readers**.
Instead of locking a row during a write, the database leaves the existing row unchanged and writes a *new version* of the row. When another transaction reads that row, the database checks the transaction's start time and returns the latest version of the row that was committed *before* the reading transaction started. This logical snapshot isolates the reader from ongoing modifications, ensuring consistent reads without requiring any read locks.
6. MVCC Row Visibility: Transaction IDs and Snapshot Isolation
6.1 Row Metadata in PostgreSQL
To implement MVCC, the database appends hidden metadata fields to every row. In PostgreSQL, these include **xmin** (the transaction ID that inserted the row) and **xmax** (the transaction ID that updated or deleted the row). Every transaction is assigned a monotonically increasing transaction ID ($T_{id}$). When a transaction queries the database, it is provided a **snapshot** containing a list of active transaction IDs that have not yet committed. A row is visible to a transaction if its insertion transaction has committed and its deletion transaction has not yet committed. The visibility checks can be formalized as:
By executing this visibility logic for every row scanned, the database constructs a virtual snapshot of the data, isolating transactions from modifications made by concurrent transactions without setting physical locks.
6.2 Row Version Chains and MySQL's Undo Log
When a row is updated in PostgreSQL, it creates a new physical copy of the row, linking it to the old one to form a **version chain**. This design is simple and makes rolling back a transaction instantaneous (the database simply marks the transaction ID as aborted in the commit log, and the new row versions instantly become invisible). However, this physical duplication causes write amplification and requires regular vacuuming to prune old versions. If a table receives millions of updates, the vacuum process can become a bottleneck, consuming CPU and disk I/O.
In contrast, MySQL's InnoDB storage engine implements MVCC using **Undo Logs**. When a row is modified in MySQL, InnoDB updates the row in-place, but writes the original values to a separate undo log. The row metadata contains a pointer to the latest undo log entry. When a transaction reads the row at a Repeatable Read isolation level, MySQL reads the current row from disk and traces back through the undo log pointer to reconstruct the historic version of the row as of the transaction's start time. This avoids table bloat and write amplification, but makes rolling back or tracing long version chains slower. Understanding this trade-off is essential when choosing and tuning databases for write-heavy workloads.
Pitfall — Long-Running Transactions Bloating Undo Logs: A common pitfall in MVCC databases is letting a read-only transaction (like a slow report generator) run for hours. Because the database must keep all row versions or undo logs visible to that transaction active, it cannot vacuum or delete old versions. In MySQL, this causes the undo log tablespace to grow massive, slowing down all concurrent writes. Always split long reports into smaller batches or execute them on read replicas.
7. Advanced: Serializable Isolation via SSI
7.1 Serializable Snapshot Isolation (SSI)
Historically, the only way to achieve `SERIALIZABLE` isolation was using Two-Phase Locking, which severely bottlenecked database performance. In 2008, researchers introduced **Serializable Snapshot Isolation (SSI)**, which was integrated into PostgreSQL 9.1. SSI allows the database to run transactions concurrently using MVCC without locking. However, the database monitors the dependency graph of transactions during execution, looking for read-write dependency cycles (specifically, write-after-read conflicts). If a cycle is detected that could lead to an anomaly (like write skew), the database aborts one of the conflicting transactions with a serialization failure, allowing the other to commit safely. This optimistic concurrency control provides perfect safety with high read performance.
8. Advanced: Write Skew Anomalies in Repeatable Read
8.1 The Invariant Failure Proof
Write skew is particularly dangerous because it does not violate row-level locks. If Doctor A and Doctor B both read the table count and then update their own rows (Doctor A sets active=false; Doctor B sets active=false), they do not modify the same rows. Under Repeatable Read, both updates succeed. However, the global invariant (at least one doctor active) is violated. This happens because the constraint depends on the *aggregate state* of the table, not individual row values. To prevent write skew without serializable isolation, developers must force row locking on the read step: SELECT * FROM doctors WHERE active = true FOR UPDATE, which tells the database to acquire exclusive locks on the queried rows, forcing concurrent transactions to serialize.
9. Complete SQL Transaction Isolation Level Code Walkthrough
9.1 Reproducing Anomalies in PostgreSQL
The following SQL script demonstrates how to configure and run concurrent sessions in PostgreSQL to observe how Read Committed isolation prevents dirty reads but permits non-repeatable reads:
10. Interactive: Concurrency Anomalies Simulator
Select the isolation level, then click Run Concurrency. Watch how two transactions overlap. Under Read Committed, Transaction 2 reads uncommitted modifications, leading to a dirty read. Under Repeatable Read, the database isolates the queries, protecting the state:
11. Transaction Throughput vs Isolation Levels
The chart below illustrates the database transaction throughput (Transactions Per Second) under varying isolation levels. Higher isolation levels guarantee safety but decrease concurrency due to lock contention and serialization aborts:
12. Frequently Asked Questions
Q1: Why does PostgreSQL not support Read Uncommitted isolation?
In PostgreSQL, Read Uncommitted is mapped internally to Read Committed. Because PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage row visibility, reading an uncommitted row version would require bypassing the visibility snapshot structures, which adds complexity for zero performance gain. Thus, dirty reads are impossible in PostgreSQL under any isolation level.
Q2: How does MySQL's Repeatable Read isolation differ from PostgreSQL's?
In MySQL (InnoDB), Repeatable Read uses **Next-Key Locking** (which combines index-record locks and gap locks) to prevent phantom reads during range queries. In PostgreSQL, Repeatable Read also prevents phantom reads but does so using its MVCC snapshot engine, avoiding gap locks. However, both remain vulnerable to Write Skew anomalies unless Serializable is used.
Q3: What is the performance cost of Serializable isolation?
Serializable isolation guarantees total correctness but degrades transaction throughput. Under pessimistic lock-based Serializable (2PL), readers and writers block each other, causing lock queue bottlenecks. Under optimistic Serializable (SSI in PostgreSQL), transactions execute concurrently, but if a conflict cycle is detected, the database aborts and rolls back one of the transactions, forcing the application to retry the query, increasing latency.
Q4: Why does a standard row update lock not prevent write skew?
Write skew occurs because the conflicting transactions do not modify the same rows. Doctor A updates Row A; Doctor B updates Row B. Since they lock separate rows, row-level locks succeed. The conflict lies in the aggregate constraint (the count of doctors on call). To lock the count, you must lock the range, which requires Serializable isolation or gap locks.
Q5: What is MVCC vacuuming or garbage collection?
Because MVCC creates new row versions instead of overwriting data, old row versions (called "dead tuples") accumulate on disk after updates and deletes. Relational databases run a background process (called `VACUUM` in PostgreSQL, or Purge threads in MySQL) to clean up these dead tuples and reclaim disk space once they are no longer visible to any active transaction.
Q6: What is a lost update anomaly?
A lost update occurs when Transaction A reads a value, Transaction B reads the same value, Transaction A increments the value and commits, and Transaction B subsequently increments its local value and commits, overwriting Transaction A's update. Most databases automatically prevent lost updates in Read Committed or Repeatable Read isolation.
Q7: Can I set the isolation level for a single transaction?
Yes. You can specify the isolation level when beginning a transaction block by executing: BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;. This allows you to run low-risk read operations at default low isolation levels while executing critical inventory or financial writes under high isolation.
Q8: How do I test database transaction anomalies?
Open two terminal sessions connecting to your database. In Session 1, run statements step-by-step, pausing to let Session 2 execute conflicting commands. Commit Session 2, then run re-reads in Session 1 to verify whether the isolation level correctly prevents dirty reads, non-repeatable reads, or serialization failures.