How MySQL Implements Repeatable Read and Prevents Phantom Reads

1. Transaction Isolation Levels

MySQL defines four isolation levels. The default is Repeatable Read , which guarantees that multiple reads within the same transaction see the same rows but can still encounter phantom reads.

Read Uncommitted

Allows a transaction to see uncommitted changes from other transactions, leading to dirty reads.

Read Committed

Only sees data committed before the current statement, preventing dirty reads but allowing non‑repeatable reads.

Repeatable Read

Ensures that rows read multiple times within a transaction remain consistent, yet new rows inserted by other transactions into the queried range can cause phantom reads.

Serializable

The strictest level forces transactions to execute serially, eliminating phantom reads at the cost of higher lock contention.

Only the InnoDB storage engine supports transactions, so all isolation level discussions apply to InnoDB.

2. How MySQL Implements Repeatable Read

MySQL uses Multi‑Version Concurrency Control (MVCC) based on optimistic locking to implement both Read Committed and Repeatable Read.

MVCC stores a global transaction ID for each row ( DB_TRX_ID) and a delete version ( DB_ROLLBACK_TRX_ID) to track visibility.

Example Table

Creating a table test_zq and inserting rows records the transaction ID in DB_TRX_ID:

begin; -- acquire global transaction ID
insert into `test_zq` (`id`,`test_id`) values ('5','68');
insert into `test_zq` (`id`,`test_id`) values ('6','78');
commit; -- commit transaction

After the inserts, the table shows the transaction IDs in the hidden columns.

Delete Logic

Deleting a row does not physically remove it; instead, the delete version is set to the current transaction ID, making the row invisible to later transactions.

begin; -- transaction ID = 3
delete from test_zq where id = 6;
commit;

Update Logic

Updating creates a new version of the row with the current transaction ID and marks the old version's delete version.

begin; -- transaction ID = 10
update test_zq set test_id = 22 where id = 5;
commit;

Query Logic

When selecting, MySQL returns rows whose DB_TRX_ID is less than or equal to the current transaction ID and whose delete version is either NULL or greater than the current transaction ID.

begin; -- assume transaction ID = 12
select * from test_zq;
commit;

The result set reflects a consistent snapshot, achieving repeatable read.

3. Phantom Reads

A phantom read occurs when a transaction re‑executes a range query and discovers new rows inserted by another transaction after the first read.

InnoDB's Repeatable Read, combined with MVCC, prevents this type of phantom read for SELECT statements.

4. Snapshot Read vs. Current Read

SELECT statements use snapshot reads by default, reading from the snapshot taken at the first SELECT in the transaction.

INSERT, UPDATE, and DELETE statements use current reads, accessing the latest row versions to ensure they operate on up‑to‑date data.

5. How to Solve Phantom Reads

Snapshot reads avoid phantom reads through MVCC. For current reads, MySQL employs locking mechanisms:

Use the Serializable isolation level.

When the WHERE clause targets a primary key, apply record locks.

When the WHERE clause uses a non‑primary index, apply next‑key locks (record lock + gap lock).

Next‑key locking prevents other transactions from inserting rows into the locked range, thereby eliminating phantom reads during updates or deletes.

References

https://juejin.im/post/5c68a4056fb9a049e063e0ab

https://zhuanlan.zhihu.com/p/35500144

https://www.jianshu.com/p/69fd2ca17cfd

https://blog.csdn.net/AAA821/article/details/81017704

https://dbaplus.cn/news-11-2518-1.html

《High Performance MySQL》