Unit testing with InnoDB transactions

Question

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?

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

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