Understanding Transaction Isolation Levels: Dirty, Non‑Repeatable, and Phantom Reads Explained

When multiple transactions execute concurrently on a database, three classic anomalies can occur: dirty read, non‑repeatable read, and phantom read.

Isolation levels were introduced to control these problems. The four standard levels are read uncommitted, read committed, repeatable read, and serializable.

We will answer three questions: what the three anomalies mean, what each isolation level guarantees, and how the database implements isolation.

Transaction Anomalies

(1) Dirty Read

Example:

In this example, a record with id = 1 and age = 21 does not exist, yet Transaction 1 reads it because another transaction has written but not committed the data.

This is a dirty read: a transaction reads data modified by another transaction that has not yet committed.

(2) Non‑repeatable Read

Example:

A non‑repeatable read occurs when the same transaction reads the same row twice and gets different results because another transaction modified and committed the row in between.

(3) Phantom Read

Example:

A phantom read happens when a transaction repeats a query with the same condition and obtains a different result set because new rows have been inserted by another transaction. It is a special case of non‑repeatable read.

Isolation Levels

Read Uncommitted : changes made by a transaction are visible to other transactions before the transaction commits.

Read Committed : changes become visible to other transactions only after the transaction commits.

Repeatable Read : a transaction sees a consistent snapshot of the data for the duration of the transaction.

Serializable : rows are locked; if a lock conflict occurs, later transactions must wait for the earlier one to finish.

Consider a table T with an integer column c containing a single record with value 1. Two concurrent transactions perform the following steps:

Transaction 1 starts and reads the value (V1 = 1).

Transaction 2 starts, reads the same value (1), updates it to 2, and commits.

Transaction 1 reads again (V2) and finally commits.

Resulting values under each isolation level:

Read Uncommitted : V1 = 2, V2 = 2, V3 = 2 (Transaction 2’s uncommitted change is visible).

Read Committed : V1 = 1, V2 = 2, V3 = 2 (Transaction 1 does not see Transaction 2’s change until it commits).

Repeatable Read : V1 = 1, V2 = 1, V3 = 2 (Transaction 1 sees a consistent snapshot).

Serializable : Transaction 2 is blocked until Transaction 1 commits; V1 = 1, V2 = 1, V3 = 2.

Implementation of Transaction Isolation

Isolation is achieved using rollback logs and read‑views.

MySQL records a rollback entry for each row update, allowing the system to reconstruct previous versions of a row.

From 1 changed to 2 <- From 3 changed to 2 <- From 4 changed to 3 <- Current value is 4

A read‑view is created when a transaction starts; it maps to the rollback log entries. The diagram below illustrates three transactions (A, B, C) with their corresponding read‑views.

Transaction A always sees the value 1, and other transactions see consistent snapshots based on their isolation level, even if another thread later changes the value to 5.