Can Repeatable Read Prevent Phantom Reads? A Deep Dive into MySQL Transaction Isolation Levels

The article investigates whether MySQL's REPEATABLE READ isolation level can prevent phantom reads, starting with a brief overview of the three classic anomalies: dirty read, non‑repeatable read, and phantom read.

Dirty read occurs when a transaction reads data modified by another uncommitted transaction. Non‑repeatable read happens when a transaction reads a row, another transaction updates it, and the first transaction reads a different value on a subsequent query. Phantom read is a special case of non‑repeatable read where a new row that satisfies the original WHERE clause appears between two identical queries.

MySQL supports four isolation levels:

READ UNCOMMITTED : Allows reading uncommitted changes, leading to dirty reads.

READ COMMITTED : Prevents dirty reads but allows non‑repeatable reads and phantom reads. Example configuration: SET session transaction isolation level read committed; SET SESSION binlog_format = 'ROW';

REPEATABLE READ : MySQL's default; prevents dirty reads and non‑repeatable reads, and can prevent many phantom reads using MVCC and next‑key locks.

SERIALIZABLE : Highest isolation; locks rows and gaps for the entire transaction, eliminating all three anomalies at the cost of performance.

Through a hands‑on experiment on a Users table, the author shows that under REPEATABLE READ, Transaction A does not see the updates from Transaction B nor the rows inserted by Transaction C, demonstrating protection against both non‑repeatable reads and phantom reads.

The article then contrasts pessimistic and optimistic locking. Pessimistic locking keeps data locked for the whole transaction, while optimistic locking relies on version numbers (MVCC) to detect conflicts. MySQL's InnoDB implements MVCC by storing hidden creation and deletion version numbers for each row.

MVCC rules in InnoDB (REPEATABLE READ):

SELECT reads rows with creation_version <= current_txn_version and deletion_version IS NULL OR deletion_version > current_txn_version .

INSERT stores the current transaction version as the row's creation version.

DELETE stores the current transaction version as the row's deletion version.

UPDATE inserts a new versioned row and marks the old row with a deletion version.

Two read modes are described:

Snapshot read (ordinary SELECT ) reads the version of data visible at the start of the transaction, avoiding locks.

Current read (SELECT … FOR UPDATE , INSERT, UPDATE, DELETE) reads the latest committed version and acquires appropriate locks.

Code examples for current reads:

SELECT * FROM table WHERE ? LOCK IN SHARE MODE;
SELECT * FROM table WHERE ? FOR UPDATE;
INSERT ...;
UPDATE ...;
DELETE ...;

The concept of next‑key locks is introduced to explain how InnoDB prevents phantom reads without locking entire tables. By locking index ranges (gaps) in a B+‑tree, InnoDB blocks inserts that would create new rows matching the original query range.

Despite these mechanisms, the article presents a scenario where REPEATABLE READ still suffers a phantom read: Transaction A selects rows, Transaction B inserts a new row and commits (releasing its gap lock), then Transaction A updates without a new condition, causing the newly inserted row to be updated and appear in a subsequent select. This demonstrates that under certain interleavings, phantom reads can still occur.

The article concludes by inviting readers to discuss why this example produces a phantom read, linking to several reference materials on MySQL isolation levels, MVCC, and locking.