sharakan sharakan - 2 years ago 67
Java Question

Unit tests of Hibernate based code on top of hsqldb no longer work on hsqldb 2.2.9

I frequently write unit tests of my database dependent code using an in-memory HSQL database as the testing database. Recently I decided to upgrade from to 2.2.9 to take advantage of ROW_NUMBER() support that was added in the 2.x release branch.

It seems that in some way, the new version is more strict than the old version. Using Hibernate (3.6.10) as the ORM, I might for example create a

object to create a first
, use that to populate the test data, then uses the
to the class under test, which creates it's own
to do a select. With hsqldb, no problem. With 2.2.9, the select blocks inside of hsqldb code. Below is a SSCCE demonstrating this:

public void testTwoSessionFactories() throws Exception {
boolean withTx = false;

AnnotationConfiguration config = new AnnotationConfiguration().addAnnotatedClass(Entity.class);
config.setProperty("", "create");
config.setProperty(Environment.DIALECT, HSQLDialect.class.getName());
config.setProperty(Environment.DRIVER, jdbcDriver.class.getName());
config.setProperty(Environment.URL, "jdbc:hsqldb:mem:testDB");
config.setProperty(Environment.USER, "SA");
config.setProperty(Environment.PASS, "");

SessionFactory sessionFactory1 = config.buildSessionFactory();
Session session = sessionFactory1.openSession();

Transaction tx = null;
if (withTx)
tx = session.beginTransaction(); Entity("one"));

if (withTx)


config.setProperty("", "");
SessionFactory sessionFactory2 = config.buildSessionFactory();
Session session2 = sessionFactory2.openSession();
List entities = session2.createCriteria(Entity.class).list();

Note the
boolean. With HSQLDB, I can run this code with
true or false, and it'll be fine. With HSQLDB 2.2.9,
must be set to true, otherwise the thread gets blocked in the
call with the following stack:

Unsafe.park(boolean, long) line: not available [native method]
LockSupport.park(Object) line: not available
CountDownLatch$Sync(AbstractQueuedSynchronizer).parkAndCheckInterrupt() line: not available
CountDownLatch$Sync(AbstractQueuedSynchronizer).doAcquireSharedInterruptibly(int) line: not available
CountDownLatch$Sync(AbstractQueuedSynchronizer).acquireSharedInterruptibly(int) line: not available
CountDownLatch.await() line: not available
CountUpDownLatch.await() line: not available
Session.executeCompiledStatement(Statement, Object[]) line: not available
Session.execute(Result) line: not available
JDBCPreparedStatement.fetchResult() line: not available
JDBCPreparedStatement.executeQuery() line: not available
BatchingBatcher(AbstractBatcher).getResultSet(PreparedStatement) line: 208
CriteriaLoader(Loader).getResultSet(PreparedStatement, boolean, boolean, RowSelection, SessionImplementor) line: 1953
CriteriaLoader(Loader).doQuery(SessionImplementor, QueryParameters, boolean) line: 802
CriteriaLoader(Loader).doQueryAndInitializeNonLazyCollections(SessionImplementor, QueryParameters, boolean) line: 274
CriteriaLoader(Loader).doList(SessionImplementor, QueryParameters) line: 2542
CriteriaLoader(Loader).listIgnoreQueryCache(SessionImplementor, QueryParameters) line: 2276
CriteriaLoader(Loader).list(SessionImplementor, QueryParameters, Set, Type[]) line: 2271
CriteriaLoader.list(SessionImplementor) line: 119
SessionImpl.list(CriteriaImpl) line: 1716
CriteriaImpl.list() line: 347
EntityTest.testTwoSessionFactories() line: 46

What changed in HSQLDB between and 2.2.9 that requires this code to do the save within a transaction, and can I turn it off?

Answer Source

HSQLDB 1.8.x uses READ UNCOMMITTED for rows that have been added or changed by another transaction.

HSQLDB 2.x uses READ COMMITTED (by default) or SERIALIZABLE isolation level. Therefore a transaction must commit before its changes are visible. There is also the transaction model to consider.

The default transaction model is LOCKS which locks a table that is modified until the transaction is committed. You can use the MVCC model instead, which allows other sessions to read from the table and to modify rows that have not been modified. You can use this model with a URL property.

config.setProperty(Environment.URL, "jdbc:hsqldb:mem:testDB;hsqldb.tx=mvcc");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download