How can I get H2 to work with Spring?

Question

I am writing a test which extends Spring's AbstractTransactionalJUnit4SpringContextTests.

In my application code I have a method which I call inside the test annotated by the following:

@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)

Problem

I run into a problem while using H2 as the underlying data source in-memory mode. It gives me the error:

Caused by:org.h2.jdbc.JdbcSQLException: Timeout trying to lock tableMY_TABLE[50200-131]

When I remove the propagation, it works, and when I use an alternative database such as Oracle or MySQL with Propagation.REQUIRES_NEW, everything works fine.

I am using Spring 3.0.2-RELEASE and H2 1.2.131.

How can I get H2 to work with Spring?

Answer 1

I don't know what the problem is, but try appending ;MVCC=TRUE to the database URL.

Answer 2

Had the same problem doing JUnit Tests with play-framework Jobs (that run into separate threads) and trick provided by Thomas works! (appending ;MVCC=TRUE to the database URL.)

I guess this MVCC option enables 'Row Level Locking' instead of locking the whole TABLE, and so the LOCK issue is gone, see "Row Level Locking" feature on: http://www.h2database.com/html/features.html#in_memory_databases

'Row Level Locking' supported in H2: <*9 When using MVCC (multi version concurrency).>