So, I'm running some unit tests against some InnoDB tables in MySQL.

Each unit test is wrapped in a transaction:


SET autocommit = 0;



Each test does one or more queries: SELECT, INSERT, UPDATE etc.

The first time I run the tests, they all pass. But if I run them again immediately, I start getting failures. If I wait a while and then run the test a third time, the failures go away.

Any insights to what might be causing this and how I could prevent it?

1 Answer 1


Perhaps it is order of doing things. IMHO a combination of SERIALIZABLE isolation and every other transaction test may be the problem.

For starters, what does SERIALIZABLE mean?

According to MySQL 5.0 Certification Study Guide

enter image description here

page 421 Bulletpoint 4 says

SERIALIZABLE completely isolates the effects of one transaction from others. It is similar to REPEATABLE READ with the additional restriction that rows selected by one transaction cannot be changed by another until the first transaction finishes.

The next paragraph says that with SERIALIZABLE, one transaction cannot modify rows if another has merely read them.


  • You should use the default REPEATABLE READ isolation level
  • You should check the sequence of events and make sure you did not miss a ROLLBACK in between tests
  • Yep, not using SERIALIZABLE seems to fix it. Thanks!
    – scribu
    Apr 22, 2012 at 13:17
  • And why does SERIALIZABLE not work respectively does work after waiting some time?
    – miracle173
    Oct 28, 2012 at 1:13
  • Of course, it works after some time. The problem with SERIALIZABLE is that it makes InnoDB have the same mechanical behavior as MyISAM. A high-write transactional environment is definitely no place for the SERIALIZABLE isolation level. Oct 28, 2012 at 1:18
  • "of course"? I neither understand why it does not work after the first run nor do I understand why it works after waiting some time . After a transaction is rolled back the database shoukld be in the same state as befor the transaction began. Independent of the transaction isolation level used. Are there locks that are not released after rollback of a transaction but time out after a while? Would you be so kind to add an @miracle173 tag if you post an answer otherwise I would get no notification that you have answered.
    – miracle173
    Oct 28, 2012 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.