Unit testing with InnoDB transactions So, I'm running some unit tests against some InnoDB tables in MySQL.

Each unit test is wrapped in a transaction:

setUp:

SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;


tearDown:

ROLLBACK;


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



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.

SUGGESTIONS


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