Menu
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??
434
REPEATABLE READ. This is the default isolation level for InnoDB . Consistent reads within the same transaction read the snapshot established by the first read.
The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.
It is the default isolation in MySQL. This isolation level allows phantom read. A Phantom read occurs when one user is repeating a read operation on the same records but has new records in the results set.
If you want to hide from phantom reads completely, then you'll need to use either the serializable or snapshot transaction isolation levels. Both of these have the same concurrency effects: No dirty reads, non-repeatable reads, or phantom reads.
The next-strongest of the standard isolation levels after serializable is named repeatable read. The SQL standard specifies that transactions at this level allow a single concurrency phenomenon known as a phantom.
To set the global isolation level at server startup, use the --transaction-isolation= level option on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the permissible values are READ-UNCOMMITTED , READ-COMMITTED , REPEATABLE-READ , or SERIALIZABLE .
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.
116
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
