Как произвести фантомное чтение?

InnoDB должен защищать от фантомных чтений, как написали другие.

Но InnoDB имеет другое странное поведение, связанное с блокировкой. Когда запрос получает блокировку, он всегда получает блокировку для самой последней версии строки. Поэтому попробуйте следующее

CREATE TABLE foo (i INT PRIMARY KEY, val INT);
INSERT INTO foo (i, val) VALUES (1, 10), (2, 20), (3, 30);

Затем в двух одновременных сеансах (откройте два окна терминала):

-- window 1                               -- window 2
START TRANSACTION;
                                          START TRANSACTION;

                                           SELECT * FROM foo;

 UPDATE foo SET val=35 WHERE i=3;

                                           SELECT * FROM foo;

Это должно показать val = 10, 20, 30 в обоих SELECT, так как REPEATABLE -READ означает, что второе окно видит только те данные, которые существовали на момент начала транзакции.

Однако:

                                           SELECT * FROM foo FOR UPDATE;

Второе окно ожидает получения блокировки в строке 3.

COMMIT;

Теперь SELECT во втором окне завершается и показывает строки с val = 10, 20, 35, потому что блокировка строки приводит к тому, что SELECT видит самую последнюю зафиксированную версию. Операции блокировки в InnoDB действуют так, как будто они выполняются в режиме READ-COMMITTED, независимо от уровня изоляции транзакции.

Вы даже можете переключаться назад и вперед:

                                           SELECT * FROM foo;

                                           SELECT * FROM foo FOR UPDATE;

                                           SELECT * FROM foo;

                                           SELECT * FROM foo FOR UPDATE;