Hibernate spring unit testing with mySQL in memory database

I'm using H2 for unit/integration testing and also for development - way faster and doesn't depend on anything external. MySQL is my production database. It's like a perfect marriage...

... until concurrency kicks in, and databases start behaving differently.

Each database has its own transaction isolation policies - for the most part, tests work on MySQL but not on H2. For instance, H2 only supports table-locking with a timeout of just 1,000ms—thus causing some threads to fail and therefore my tests unstable.

There are ways on how H2 can be configured separately (check out H2's transaction isolation levels), however I've yet to test the MVCC option.

I'll keep you posted.

PS. Moral of the story - create concurrent integration tests!

Update: It works! I have created the data source as follows:

final DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("org.h2.Driver");
ds.setUrl("jdbc:h2:~/testdb;MVCC=TRUE");
ds.setUsername("sa");
ds.setPassword("sa");       
return ds;

The MVCC flag works like a charm, however I had some problems using in-memory h2:mem since it is volatile with each connection (therefore, the schema is wiped out whenever Hibernate reattempts the connection). I had to retreat to a file-based database (which is not a big problem for me).

Spring supports only H2, HSQL and Derby as in-memory database implementations. If you are really interested in testing with MySql itself, you should be able to run your unit tests against an external MySql database server, running either locally or on a different machine.