CODEWITHSUNDEEP
javamysqljdbcinnodbmyisam
user1251858
user1251858

Reputation: 313

phantom reads in InnoDB, MyISAM and other engines

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:

Upvotes: 0

Views: 277

Answers (1)

Ricardo Zuasti
Ricardo Zuasti

Reputation: 151

As far as I know MyISAM tables ignore the isolation level settings. It was explicit on the 5.0 and 5.1 documentation, but they seem to have removed the mention to InnoDB on the newer documentation.

Upvotes: 1

Related Questions