Investigating Phantom Reads in InnoDB REPEATABLE-READ Isolation Level: Experiments and Analysis

1 Background

InnoDB's default isolation level is REPEATABLE‑READ, which uses lock‑based concurrency control (LBCC) for current reads and multi‑version concurrency control (MVCC) for snapshot reads to prevent phantom reads. This article investigates whether phantom reads are completely eliminated under this level.

2 Validation

2.1 Preparation

Environment

MySQL version: 5.6.36
Storage engine: InnoDB
Isolation level: REPEATABLE-READ

Data setup

A table user_info is created and three rows are inserted.

CREATE TABLE `user_info` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'name',
  `gender` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'gender',
  `email` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'email',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='user info';

INSERT INTO `user_info` (`id`,`name`,`gender`,`email`) VALUES
(1,'Curry','男','[email protected]'),
(2,'Wade','男','[email protected]'),
(3,'James','男','[email protected]');
commit;

2.2 Current‑read scenario

The test uses SELECT ... LOCK IN SHARE MODE to perform a current read.

Start transaction 1 and execute a locked select.

Start transaction 2 and attempt to insert a new row.

Commit transaction 2 (which times out due to lock).

Execute the locked select again in transaction 1.

The second select does not see the new row because transaction 2 was blocked, confirming that LBCC prevents phantom reads in the current‑read case.

Summary

Lock‑based concurrency control fully avoids phantom reads for current reads.

2.3 Snapshot‑read scenario

Three sub‑scenarios are executed using plain SELECT (snapshot reads).

Scenario 1

Transaction 1 performs a plain select, transaction 2 inserts a row and commits, then transaction 1 selects again.

-- transaction 1
START TRANSACTION;
SELECT * FROM user_info;
-- transaction 2
START TRANSACTION;
INSERT INTO user_info (...) VALUES (...);
COMMIT;
-- transaction 1 again
SELECT * FROM user_info;
COMMIT;

Transaction 1 sees the newly inserted row, indicating a phantom read does not occur because the second select uses the same snapshot taken before the insert.

Scenario 2

Transaction 1 selects, transaction 2 inserts, transaction 2 commits, then transaction 1 updates a row and selects again.

-- transaction 1
START TRANSACTION;
SELECT * FROM user_info;
-- transaction 2
START TRANSACTION;
INSERT INTO user_info (...) VALUES (...);
COMMIT;
-- transaction 1 updates
UPDATE user_info SET name='Iversen' WHERE id=1;
SELECT * FROM user_info;
COMMIT;

Even after the update, transaction 1 still does not see the row inserted by transaction 2, so no phantom read occurs.

Scenario 3

Transaction 1 selects, transaction 2 inserts and commits, then transaction 1 updates all rows (changing the email domain) and selects again.

-- transaction 1
START TRANSACTION;
SELECT * FROM user_info;
UPDATE user_info SET email = REPLACE(email,'@163.com','@gmail.com');
SELECT * FROM user_info;
COMMIT;
-- transaction 2
START TRANSACTION;
INSERT INTO user_info (...) VALUES (...);
COMMIT;
SELECT * FROM user_info;

After the bulk update, transaction 1 sees the row inserted by transaction 2, demonstrating a phantom read caused by the update touching the newly inserted row.

Summary

Snapshot reads can produce phantom reads when an update in the first transaction accesses rows inserted by a second transaction.

3 Analysis

The experiments show that current reads (LBCC) completely prevent phantom reads, while snapshot reads (MVCC) may allow them depending on the operations performed. The root cause is the visibility rules of MVCC: an update creates a new version of a row, and if that version is linked to a row inserted after the original snapshot, the row becomes visible to the first transaction.

4 Conclusion

Current reads avoid phantom reads via locking.

Snapshot reads may encounter phantom reads when updates affect rows inserted by other transactions.

To prevent phantom reads, consider using serializable isolation (not recommended), adjust application logic, or explicitly use current reads where appropriate.