Database Isolation Levels & Read Phenomena: An Extensive Deep Dive

Databases are messy places. In the real world, thousands of users are reading, writing, updating, and deleting data at the exact same millisecond. Because databases are multi-user systems, letting transactions run completely unchecked creates total chaos.

When concurrent transactions access and modify the same data at the same time, anomalies emerge. To keep things under control, databases give us Isolation Levels, which are essentially tuning knobs that let you control which anomalies you are willing to permit in exchange for faster performance.

To understand how to turn these knobs, we first need to understand the dark world of database anomalies. Let's dive deep.

Part 1: Read Phenomena (The Anomalies)

An anomaly (or read phenomenon) occurs when a transaction reads data in a state that breaks logical consistency. Here are the 6 primary anomalies you must know.

1. Dirty Read

A dirty read happens when Transaction A reads data that was written by a concurrent Transaction B, but Transaction B has not committed yet.

T1: BEGIN
T1: UPDATE accounts SET balance = 1000 WHERE id = 42   -- Not committed yet

T2: SELECT balance FROM accounts WHERE id = 42
    -> Returns 1000  <- DIRTY READ

T1: ROLLBACK   -- Oops! That $1000 never actually existed

Why it's dangerous: T2 made real-world decisions based on data that was never truly persisted to the disk. In financial or inventory systems, this is a nightmare. It leads to double-spend bugs, incorrect account totals, or flawed automated fraud decisions.

2. Non-Repeatable Read (Fuzzy Read)

A non-repeatable read happens when a transaction reads the exact same row twice, but gets different values each time because a concurrent transaction modified and committed that row in the middle of the operation.

T1: BEGIN
T1: SELECT balance FROM accounts WHERE id = 42  -> 500

    T2: UPDATE accounts SET balance = 900 WHERE id = 42
    T2: COMMIT

T1: SELECT balance FROM accounts WHERE id = 42  -> 900  <- DIFFERENT!
T1: END

Why it's dangerous: T1's internal logic becomes completely inconsistent. It looks at the same object twice within a single workspace and sees two different realities. This is highly disruptive for read-heavy analytics or multi-step validation logic where you read a row, validate a condition, and then expect that value to remain unchanged while processing.

3. Phantom Read

A phantom read occurs when a transaction executes the same range query twice and gets two different sets of rows, because another transaction inserted or deleted rows matching that criteria in the interim.

T1: BEGIN
T1: SELECT * FROM orders WHERE amount > 1000  -> {row A, row B}

    T2: INSERT INTO orders (id, amount) VALUES (99, 5000)
    T2: COMMIT

T1: SELECT * FROM orders WHERE amount > 1000  -> {row A, row B, row C}  <- PHANTOM!
T1: END

The Key Distinction: People often confuse Non-Repeatable Reads with Phantom Reads. Non-Repeatable Reads are about a single row's values changing. Phantom Reads are about a set of rows appearing or disappearing based on a predicate (like amount > 1000). This distinction matters deeply under the hood: row-level locks cannot prevent phantom reads because you cannot lock a row that does not exist yet. You need predicate locks or range locks to stop phantoms.

4. Lost Update

A lost update happens when two transactions concurrently read a value, both compute a new value based on what they read, and both write their changes back. The second write silently overwrites and obliterates the first write.

T1: READ balance = 100  ->  T1 computes 100 + 50 = 150
T2: READ balance = 100  ->  T2 computes 100 + 75 = 175

T1: WRITE balance = 150
T2: WRITE balance = 175  <- T1's increment is completely LOST!

Final balance: 175 (Should be 225)

Why it's dangerous: This is the classic bank transfer or inventory reduction bug. It is incredibly common and dangerous in modern Object-Relational Mapping (ORM) frameworks (like Hibernate, Prisma, or Entity Framework) because ORMs inherently use this "read-modify-write" application pattern by default.

5. Read Skew (Snapshot Inconsistency)

Read skew happens when a transaction reads multiple rows that have a logical relationship (an invariant), but reads them at different points in time, resulting in an internally inconsistent view of the database.

Imagine an invariant rule: Account A + Account B must always equal $1000.

T1: SELECT balance FROM accounts WHERE id = 'A'  -> 400

    T2: UPDATE A SET balance = 600, UPDATE B SET balance = 400
    T2: COMMIT

T1: SELECT balance FROM accounts WHERE id = 'B'  -> 400  <- Saw B's NEW value!

T1 sees: A = 400, B = 400 -> Total = 800  <- INCONSISTENT!

Why it matters: The database was perfectly consistent before and after T2. But because T1 read different parts of the data at different times, it caught a distorted middle-ground view. Read skew easily corrupts database backup/restore operations, live replication parity checks, and analytical accounting reports.

6. Write Skew

Write skew is the most elusive and hardest anomaly to reason about. It happens when two transactions read an overlapping dataset, and both make a write that is perfectly valid based on what they read. However, when combined, their parallel actions violate a global business invariant.

Imagine a hospital system rule: At least one doctor must be active and on-call at all times. Alice and Bob are currently on call.

T1 (Alice): SELECT COUNT(*) FROM on_call WHERE status='active'  -> 2
T2 (Bob):   SELECT COUNT(*) FROM on_call WHERE status='active'  -> 2

-- Both see 2 doctors on call. Both independently assume it's safe to take the day off.

T1: UPDATE on_call SET status='off' WHERE doctor='Alice'
T2: UPDATE on_call SET status='off' WHERE doctor='Bob'

-- Result: 0 doctors on call. Invariant violated.

Why it matters: Look closely. Neither transaction modified the exact same row. T1 modified Alice. T2 modified Bob. If you look at T1 or T2 in isolation, they are completely valid and responsible queries. The catastrophic failure only emerges from their concurrent interaction. Only true Serializable isolation can prevent write skew.

Part 2: The SQL-92 Standard (And Where It Fails)

To handle these anomalies, the ANSI SQL-92 standard officially defined four classic isolation levels based on which phenomena they permit:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible
Serializable	Prevented	Prevented	Prevented

The Catch: The SQL-92 Standard is Broken

While this table is taught in every university computer science class, it is incomplete and outdated. The standard completely omits Lost Update, Read Skew, and Write Skew. Because of this gap, many databases advertise themselves as "Serializable" when they actually only provide Snapshot Isolation, leaving your application vulnerable to write skew anomalies.

We will build a much more complete version of this table by the end of the post. For now, keep this one in mind as the baseline.

The Engine Under the Hood: How MVCC Works

Before tearing into each isolation level, you need to understand one core mechanism that nearly all of them are built on: Multi-Version Concurrency Control (MVCC).

Here is the problem MVCC solves. In a naive database, when a transaction writes a new value to a row, it overwrites the old value in place. Every reader and every writer are now fighting over the same physical slot. Reads block writes. Writes block reads. Concurrency tanks.

MVCC takes a completely different approach. Instead of overwriting, the database keeps multiple timestamped versions of every row. When T1 writes a new value, it does not erase the old one. It creates a new version of that row tagged with T1's transaction timestamp.

When T2 reads that same row, it does not just grab the latest value. It reads the version that was visible at its own snapshot timestamp. T2 might be reading a version from before T1 even started. Neither transaction blocks the other because they are not competing for the same version.

This is the fundamental reason modern databases can say "readers do not block writers." They are not reading the same version. They are reading from different points in time.

Keep this mental model close as you read through Part 3. MVCC is the engine under the hood of nearly every isolation level discussed below.

Part 3: Deep Dive Into Each Level (Mechanisms and Realities)

Let's tear down how these isolation levels actually behave under the hood in modern database engines.

1. Read Uncommitted

The Definition: Transactions can peek at rows written by other open transactions, regardless of their commit state.

The Mechanism: Zero read locks are acquired. Writers only acquire minimal write locks to avoid dirty writes (overwriting uncommitted data).

Permitted Anomalies: Everything. Dirty reads, non-repeatable reads, phantoms, lost updates, read skew, and write skew.

Real-World Reality: Essentially useless for core application logic where correctness matters. It is sometimes used for fast, approximate aggregate counts (e.g., getting a quick COUNT(*) on a massive logging table where exact precision does not matter).

Database Quirk: Modern relational databases handle this uniquely. PostgreSQL does not even bother implementing it. If you ask for Read Uncommitted, Postgres silently upgrades your transaction to Read Committed. Conversely, MySQL InnoDB implements it by tweaking its MVCC mechanism: it skips building a restricted ReadView, allowing queries to simply read the latest uncommitted row state directly from memory or the active undo log version chain. This means InnoDB does execute true dirty reads, not via traditional row-locking, but via version tracking.

2. Read Committed

The Definition: A transaction can only see data that has been successfully committed before a specific statement begins execution.

The Mechanism:

• In MVCC systems (Postgres, Oracle): Statement-level snapshots. Every single SQL query inside your transaction gets a brand new, fresh snapshot of the latest committed data.
• In traditional lock-based systems (older engines): Short-duration read locks. Shared locks are grabbed to read a row and are instantly released the microsecond the database finishes reading that row.

The Key Property: The data snapshot advances with each statement, not per transaction.

-- Running in T1 under Read Committed
SELECT balance FROM accounts WHERE id=1;  -- Reads snapshot at T=10ms
-- T2 commits an update to account 1 at T=15ms
SELECT balance FROM accounts WHERE id=1;  -- Reads a new snapshot at T=16ms -> Different result!

• Prevented: Dirty reads.
• Permitted: Non-repeatable reads, phantom reads, read skew, write skew.

The Default Choice: This is the default isolation level for PostgreSQL, Oracle, SQL Server, and DB2. It provides the optimal balance of concurrency performance and basic data safety for the vast majority of online transaction processing (OLTP) applications.

3. Repeatable Read

The Definition: Once your transaction reads a row, it is guaranteed to see that exact same value for the remainder of its lifespan.

The Mechanism:

• Lock-based approach: Shared read locks (S-locks) are held continuously on all read rows until the final COMMIT or ROLLBACK. This blocks any writers from acquiring the exclusive lock (X-lock) they need to modify those rows.
• MVCC-based approach: A single transaction-level snapshot is taken at the exact millisecond the transaction starts. All reads throughout the transaction reference this immutable snapshot, ignoring external changes.

Why Held S-locks Prevent Write Skew

This is the part most explanations skip. S-locks are called "shared" because multiple transactions can hold them on the same row at the same time. That is by design. What an S-lock actually blocks is an X-lock (exclusive lock), which is what any transaction needs in order to perform a write.

The lock compatibility matrix:

	S-lock currently held	X-lock currently held
Requesting an S-lock	Granted	Blocked
Requesting an X-lock	Blocked	Blocked

Now walk through the doctor on-call write skew scenario under Strict 2PL:

T1: S-lock(on_call) -> granted  [compatible with T2's S-lock]
T2: S-lock(on_call) -> granted  [compatible with T1's S-lock]

T1: wants to write Alice -> requests X-lock
    -> BLOCKED by T2's S-lock

T2: wants to write Bob -> requests X-lock
    -> BLOCKED by T1's S-lock

-> DEADLOCK -> one transaction is aborted -> retries -> sees correct state

The S-lock does not block the read. It blocks the write attempt. Because both transactions hold their S-locks until commit, neither can upgrade to an X-lock to complete the write. That creates a deadlock. One transaction gets aborted. When it retries, it reads the updated state and the invariant holds. Write skew is prevented not by blocking reads, but by making it physically impossible for two transactions to simultaneously write to rows that the other has already read.

• Prevented: Dirty reads, non-repeatable reads, and write skew (under true lock-based 2PL).
• Permitted: Phantom reads (according to strict SQL-92 theory).

Critical Engine Quirks You Must Know:

• MySQL InnoDB Quirk: MySQL's version of Repeatable Read (its default level) is much stronger than the standard definition. It uses MVCC snapshots for standard reads, but it also deploys gap locks and next-key locks on index ranges during writes. This effectively prevents phantom reads entirely.
• PostgreSQL Quirk: PostgreSQL's Repeatable Read level is actually implemented as Snapshot Isolation. Because it uses a frozen snapshot for the whole transaction, it natively prevents phantom reads too. However, it still allows write skew.

4. Snapshot Isolation (SI)

Snapshot Isolation does not officially exist in the old SQL-92 standard, but it is the actual bedrock foundation of modern database engines.

The Definition: Each transaction operates on an entirely frozen, immutable snapshot of the database taken at the start of the transaction.

The "First-Committer-Wins" Rule: Writes are heavily validated at commit time. If Transaction A and Transaction B both attempt to modify the same row concurrently under Snapshot Isolation, the database will only allow the first one to commit. The second transaction will be aborted and forced to roll back.

• Prevented: Lost updates (completely solved by First-Committer-Wins), dirty reads, non-repeatable reads, phantom reads, and read skew.
• Permitted: Write skew. Because Alice and Bob modified different rows, the First-Committer-Wins rule never triggers. Both transactions commit successfully, breaking the invariant.

The Key Insight: Repeatable Read and Snapshot Isolation Are Incomparable

At this point you might be wondering which is stronger: Repeatable Read or Snapshot Isolation.

The honest answer is neither. They are incomparable. Each one prevents an anomaly that the other allows.

	Repeatable Read (true, lock-based)	Snapshot Isolation
Phantom Reads	Possible	Prevented
Write Skew	Prevented	Possible

True Repeatable Read uses S-locks held until commit. That blocks the X-lock upgrades needed to complete writes, which stops write skew via the deadlock mechanism we walked through above. But it cannot lock rows that do not exist yet, so new rows matching a predicate slip through as phantom reads.

Snapshot Isolation uses a frozen snapshot taken at transaction start. That eliminates phantoms entirely since new rows simply do not appear in a snapshot taken before their insertion. But reads acquire no locks at all, so two transactions can read overlapping data, write to different rows, and both commit without triggering any conflict check. Write skew goes through undetected.

This is a critical nuance that even official database documentation gets wrong. PostgreSQL calls its Repeatable Read implementation Snapshot Isolation, but the two are not the same thing and neither is a strict superset of the other. If you are relying on PostgreSQL's "Repeatable Read" to prevent write skew, you are not protected.

5. Serializable

The Definition: The gold standard of transaction safety. It guarantees that the concurrent execution of a group of transactions yields the exact same database state as if they were executed one after another, purely sequentially.

The Mechanism:

• Pessimistic (Strict 2PL + Range/Predicate Locks): Enforces Strict Two-Phase Locking, holding all shared and exclusive locks until the transaction commits, combined with locking entire index ranges or predicates. The 2PL component governs when locks are released (never before commit). The predicate/range locking component governs what is locked, specifically preventing other transactions from inserting new rows into ranges you have already scanned. It is the combination of both mechanisms that fully blocks anomalies. Safe, but expensive. This approach tanks concurrency.

• Serializable Snapshot Isolation (SSI): Used by modern PostgreSQL and CockroachDB. It allows transactions to run concurrently without heavy locks, but tracks rw-anti-dependency edges between transactions in memory to detect dangerous causal cycles. The idea is straightforward. If T1 read data that T2 later wrote, and T2 read data that T1 later wrote, you have a cycle. That cycle means the two transactions cannot be placed in any valid serial order. SSI detects this pattern and aborts one transaction before the anomaly can land.
  

T1 reads X, T2 writes X -> T1 depends on T2  (rw-anti-dependency)
T2 reads Y, T1 writes Y -> T2 depends on T1  (rw-anti-dependency)

Cycle detected: T1 -> T2 -> T1 -> ABORT one transaction

If it detects that a group of concurrent transactions is about to produce write skew, it intentionally aborts one of them with a serialization failure, requiring your application code to retry. The payoff is significant: readers never block writers, writers never block readers, and you still get full serializability.

• Prevented: All anomalies. Period.

Part 4: The Complete Anomaly Map

The SQL-92 table from Part 2 only tells half the story. Here is the full picture, with all six anomalies mapped across all five isolation levels, including Snapshot Isolation as its own separate level.

Anomaly	Read Uncommitted	Read Committed	Repeatable Read (true)	Snapshot Isolation	Serializable
Dirty Read	Possible	Prevented	Prevented	Prevented	Prevented
Non-Repeatable Read	Possible	Possible	Prevented	Prevented	Prevented
Phantom Read	Possible	Possible	Possible	Prevented	Prevented
Lost Update	Possible	Possible	Prevented	Prevented	Prevented
Read Skew	Possible	Possible	Possible	Prevented	Prevented
Write Skew	Possible	Possible	Prevented	Possible	Prevented

A few things jump out immediately from this table:

• Snapshot Isolation and Repeatable Read flip on two rows. Phantom Read and Write Skew are exact opposites between the two levels. This makes the incomparability visual and undeniable.
• Read Committed leaves you exposed to four of the six anomalies. Most developers working on default PostgreSQL or SQL Server setups are running here without realizing the surface area they are leaving open.
• Only Serializable closes every single box. Every other level is a deliberate tradeoff between safety and concurrency.

One critical note on this table: "Repeatable Read (true)" refers to the formal PL-2.99 definition from Adya's 1999 paper, implemented via strict two-phase locking. Most databases that label their isolation level as Repeatable Read are actually delivering Snapshot Isolation under the hood. That means their Write Skew column should read "Possible" rather than "Prevented." The formal definition and the real-world implementation are often not the same thing.

Summary Checklist for Developers

When designing your next system or writing complex queries, use this quick architectural guide:

• Sticking with defaults? If you are on PostgreSQL or SQL Server, you are running on Read Committed. Watch out for non-repeatable reads and lost updates in multi-step workflows.
• Preventing lost updates? Use SELECT ... FOR UPDATE in Read Committed to explicitly lock rows, or step up to Repeatable Read or Snapshot Isolation.
• Protecting global business rules? If your business logic depends on checking an aggregate value across rows before writing a change (like the on-call doctor rule), you must use Serializable isolation or apply explicit pessimistic locking.