Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

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
share|improve this answer
    
Yep, not using SERIALIZABLE seems to fix it. Thanks! –  scribu Apr 22 '12 at 13:17
    
And why does SERIALIZABLE not work respectively does work after waiting some time? –  miracle173 Oct 28 '12 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. –  RolandoMySQLDBA Oct 28 '12 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 '12 at 11:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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