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

Question

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 1.8.1.3 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 Configuration object to create a first SessionFactory, use that to populate the test data, then uses the Configuration to the class under test, which creates it's own SessionFactory to do a select. With hsqldb 1.8.1.3, 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("hibernate.hbm2ddl.auto", "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();

    session.save(new Entity("one"));

    if (withTx)
        tx.commit();

    session.flush();
    session.close();

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

Note the withTx boolean. With HSQLDB 1.8.1.3, I can run this code with withTx true or false, and it'll be fine. With HSQLDB 2.2.9, withTx must be set to true, otherwise the thread gets blocked in the .list() 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 1.8.1.3 and 2.2.9 that requires this code to do the save within a transaction, and can I turn it off?

Answer 1

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");