7

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??

user1251858
  • 303
  • 2
  • 4
  • 13
  • Read this question: [How to produce phantom reads?](http://stackoverflow.com/questions/5444915/how-to-produce-phantom-reads) – ypercubeᵀᴹ Mar 23 '12 at 00:56

2 Answers2

4

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.

matts
  • 6,738
  • 1
  • 33
  • 50
0

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/

Aaron Brown
  • 334
  • 1
  • 11
  • Okay, I've switched the database engine to MyISAM. Now all isolation levels can (and do) produce a phantom... Why is the SERIALIZABLE isolation level producing phantoms in MyISAM, shouldn't it still block the 2nd transaction until the 1st finishes?? – user1251858 Mar 23 '12 at 15:28
  • MyISAM is not a transactional storage engine, so REPEATABLE READ/READ COMMITTED/etc have no effect. From the engines that ship with MySQL by default, you have to use InnoDB. – Aaron Brown Mar 25 '12 at 15:01