mysql - How to produce phantom reads?

Question

Using "repeatable read" it should be possible to produce a phantom read, but how? I need it for an example teaching CS-students.

I think that I must make a "SELECT ... WHERE x<=888" on a non-indexed field x, with an upperlimit 888 not present, and then on another connection insert a new row with a value just below 888.

Except it doesn't work. Do I need a very large table? Or something else?

Answer 1

The "phantom read" in MySQL on RR isolation level is hidden deep, but still can reproduce it. Here are the steps:

1. create table ab(a int primary key, b int);

2. Tx1:
   begin;
   select * from ab; // empty set

3. Tx2:
   begin;
   insert into ab values(1,1);
   commit;
   
4. Tx1:
   select * from ab; // empty set, expected phantom read missing.
   update ab set b = 2 where a = 1; // 1 row affected.
   select * from ab; // 1 row. phantom read here!!!!
   commit;