Trying to create phantom in MySQL under isolation level REPEATABLE-READ

Question

I am trying to demonstrate phantom reading in MySQL using JDBC. I understand that with the isolation level REPEATABLE-READ phantoms should be possible. But I can't make this happen. My transactions are configured as follows:

Transaction 1:

querySetOne[0] = "use adventureworks"; querySetOne[1] = "select * from vendorcontact where ContactTypeID between 10 and 30"; querySetOne[2] = "select sleep(20)"; querySetOne[3] = "select * from vendorcontact where ContactTypeID between 10 and 30"; querySetOne[4] = "COMMIT"; 

Transaction 2:

 querySetTwo[0] = "use adventureworks"; querySetTwo[1] = "select sleep(2)"; querySetTwo[2] = "insert into vendorcontact values (105, 700, 20, NULL)"; querySetTwo[3] = "COMMIT"; 

I use them in b / c arrays. I use the Statement.execute () method to execute each line, and I have autocommit set to false.

Why does a query from querySetOne [1] and querySetOne [3] return the same results according to read read repeatability

Answer 1

The SQL standard seems to indicate that phantom reading is possible with read re-isolation, but does not say that they are required.

In particular, the MySQL InnoDB engine supports consistent reads with repeatable read isolation, which means that the first read in a transaction creates a snapshot and reads again later in the transaction using the same snapshot.

The MySQL documentation talks about repeated reading isolation:

All consistent reads in a single transaction read the snapshot set by the first read. This convention means that if you issue several simple (non-blocking) SELECT statements in a single transaction, these SELECT statements also match.

Answer 2

Phantoms impossible with REPEATABLE READ. Phantoms are the result of a READING COMMITTEE. This is the main difference between RR and RC. I recently wrote a blog post explaining two levels of isolation, as they are often confused: http://blog.9minutesnooze.com/repeatable-read-read-committed/