CODEWITHSUNDEEP
javamysqljdbc
user1251858
user1251858

Reputation: 313

trying to create a phantom in MySQL under the REPEATABLE-READ isolation level

I'm attempting to demonstrate a phantom read in MySQL through the use of JDBC. I understand that under the REPEATABLE-READ isolation level, phantoms should be possible. But I can't get one to happen. My transactions are set up 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 have them in arrays b/c I'm using the Statement.execute() method to execute each line and I have autocommit set to false.

Why does the query from querySetOne[1] and querySetOne[3] return the same results under the repeatable read isolation level??

Upvotes: 7

Views: 569

Answers (2)

matts
matts

Reputation: 6897

The SQL standard seems to indicate that phantom reads are possible in repeatable read isolation, but doesn't say they are required.

In particular, MySQL's InnoDB engine supports consistent reads under repeatable read isolation, which means the first read in a transaction creates a snapshot and reading again later in the transaction uses the same snapshot.

The MySQL documentation says this about repeatable read isolation:

All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

Upvotes: 4

Aaron Brown
Aaron Brown

Reputation: 334

Phantoms are not possible with REPEATABLE READ. Phantoms are a consequence of READ COMMITTED. That is the main distinction between RR and RC. I have recently written a blog post explaining the two isolation levels, as they are often confused: http://blog.9minutesnooze.com/repeatable-read-read-committed/

Upvotes: 0

Related Questions