How MySQL Prevents Phantom Reads in Repeatable Read

This article explains how phantom reads occur in database transactions and details the specific mechanisms MySQL’s InnoDB storage engine uses to prevent them under the Repeatable Read isolation level. You will learn the difference between standard non-blocking reads and locking reads, and how Multi-Version Concurrency Control (MVCC) and Next-Key Locks work together to maintain data consistency.

What is a Phantom Read and How Does It Occur?

A phantom read is a concurrency problem that occurs when a transaction executes the same query twice, but retrieve different sets of rows because another concurrent transaction has inserted or deleted rows in the interim.

To understand how a phantom read occurs, consider the following sequence of events between two concurrent transactions, Transaction A and Transaction B:

  1. Transaction A executes a query to retrieve all users with an age greater than 30: SELECT * FROM users WHERE age > 30; (Result: 2 rows are returned).
  2. Transaction B starts and inserts a new user into the table: INSERT INTO users (name, age) VALUES ('Alice', 35);
  3. Transaction B commits its changes.
  4. Transaction A runs the exact same query again: SELECT * FROM users WHERE age > 30; (Result: 3 rows are returned).

The newly inserted row (‘Alice’) is the “phantom” row. Even though Transaction A is supposed to have a consistent view of the database, it sees a row that did not exist when it started.

How MySQL Prevents Phantom Reads in Repeatable Read

According to the SQL-92 standard, the Repeatable Read isolation level is only required to prevent dirty reads and non-repeatable reads; it is allowed to experience phantom reads. However, MySQL’s default storage engine, InnoDB, goes a step further. It prevents phantom reads in the Repeatable Read isolation level using two distinct mechanisms depending on the type of query being executed: MVCC for consistent (non-locking) reads, and Next-Key Locking for locking reads.

1. Consistent Non-Locking Reads (Using MVCC)

For standard SELECT statements, MySQL uses Multi-Version Concurrency Control (MVCC) to prevent phantom reads without locking the database rows.

When Transaction A starts and performs its first SELECT query, InnoDB creates a Read View (a snapshot of the database state at that exact moment). This snapshot is based on transaction IDs. Even if Transaction B inserts a new row and commits it, any subsequent plain SELECT queries by Transaction A will read from its established Read View. Because the new row’s transaction ID is newer than Transaction A’s Read View, InnoDB hides the new row from Transaction A, successfully preventing a phantom read.

2. Locking Reads (Using Next-Key Locks)

MVCC works perfectly for read-only queries, but it cannot prevent phantom reads when a transaction intends to modify data using locking reads, such as: * SELECT ... FOR UPDATE * SELECT ... FOR SHARE * UPDATE ... * DELETE ...

If Transaction A performs a locking read on a range of rows, InnoDB cannot rely solely on a snapshot because it must lock the actual records to prevent other transactions from modifying them. To prevent other transactions from inserting “phantom” records into the queried range, InnoDB uses Next-Key Locks.

A Next-Key Lock is a combination of two types of locks: * Record Lock: A lock placed on the actual index records that match the query. * Gap Lock: A lock placed on the “gaps” (the spaces before, between, and after) the index records.

Example of Next-Key Locking in Action

Suppose a table has index values of 10 and 20.

If Transaction A executes: SELECT * FROM users WHERE id > 15 FOR UPDATE;

InnoDB will not only lock the record with ID 20 (Record Lock), but it will also place a Gap Lock on the range (10, 20) and the range (20, positive infinity).

If Transaction B attempts to run INSERT INTO users (id) VALUES (18); or INSERT INTO users (id) VALUES (25);, MySQL will force Transaction B to wait until Transaction A commits or rolls back. Because Transaction B is blocked from inserting new rows into the locked gaps, phantom reads are entirely prevented.