I'm attempting to create a phantom read in MySQL using Java and JDBC. I have the following transactions
Transaction 1:
String[] querySetOne = new String[5];
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:
String[] querySetTwo = new String[4];
querySetTwo[0] = "use adventureworks";
querySetTwo[1] = "select sleep(2)";
querySetTwo[2] = "insert into vendorcontact values (105, 700, 20, NULL)";
querySetTwo[3] = "COMMIT";
I have attempted to create a phantom read using the InnoDB database engine on the repeatable read isolation level, but my understanding now is that MySQL uses snapshot isolation COMBINED with the REPEATABLE READ and SERIALIZABLE isolation levels. I then switched my database engine to MyISAM and found that it demonstrates no concurrency control whatsoever; lost updates, unrepeatable reads, dirty reads and phantoms occur on ALL isolation levels when I use MyISAM. I have several questions:
- what database engine can I use that will use concurrency control but WITHOUT snapshot isolation, and how would I get it?
- is there any way to turn snapshot isolation in InnoDB?
- why are all anomalies happening on MyISAM, does it actually not support any sort of concurrency control or am I just being stupid?
