How to produce “phantom read” in REPEATABLE READ? (MySQL)

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;

Leave a Comment