Isolating a spring JDBC transaction from a hibernate transaction

Question

In spring the HibernateTransactionManager uses the SessionFactory it was initialised with to "bind" a Session to the current thread context when creating a new transaction. Then when HibernateTemplate is used it find that bound Session and uses it.

However I found today that HTM also binds its transaction to the underlying DataSource as well as the SessionFactory (if possible). This allows code to use JdbcTemplate within the transaction scope and, provided the DataSource used by JdbcTemplate is the same as the SessionFactory uses, the Jdbc operations will participate in the transaction (using the same underlying Connection).

This bit me quite badly today when I had some code in my hibernate id allocator that was creating a DataSourceTransactionManager and JdbcTemplate to allocate ids out of a high-lo table. I was intending that this be a standalone transaction that would fetch the next high number and then commit the change to the id table. However because of the above behaviour it was actually participating in my "outer" hibernate transaction AND even worse committing it early. Suffice to say not good.

I tried playing around with transaction propogation settings (used REQUIRES_NEW) but this didn't help.

Does anyone know the best way to use JdbcTemplate within a hibernate transaction and NOT have them share a transaction, even tho they share the same DataSource?

EDIT:

I have a SessionFactory (S) which is created by the spring LocalSessionFactoryBean using a DataSource (D). The HibernateTransactionManager is created with that SessionFactory (S).

some business logic code would look like this..

hibernateTransactionOperations.execute( new TransactionCallbackWithoutResult()
{
    @Override
    protected void doInTransactionWithoutResult( TransactionStatus status )
    {
        // some transactional code here using a HibernateTemplate

        // will include calls to id allocation when doing hibernateTemplate.save(obj)
    }
} );

my id allocation does this (paraphrased), the DataSource below is the same (D) as the one used in the SessionFactory (S).

PlatformTransactionManager txManager = new DataSourceTransactionManager( dataSource );
TransactionOperations transactionOperations = new TransactionTemplate( txManager );

return transactionOperations.execute( new TransactionCallback<Long>()
{
    public Long doInTransaction( TransactionStatus status )
    {
        return allocateBatchTxn( idKey, batchSize );
    }
} );

When the transactionOperations execute above completes it will commit the underlying transaction which seems to be the same as the 'outer' hibernate transaction. I have confirmed this by checking locks/transactions in the DB.

Answer 1

Don't create a new DataSourceTransactionManager in your id allocation code. Instead use REQUIRES_NEW and the HibernateTransactionManager.

In allocateBatchTxn(), the safest way to get the JDBC connection is via Spring's DataSourceUtils.getConnection() method.

Answer 2

I tried it with REQUIRES_NEW - it works as expected (on HSQLDB), perhaps it's DB-dependent:

// txManager is a HibernateTransactionManager obtained from the application context
TransactionOperations transactionOperations = new TransactionTemplate( txManager );
transactionOperations.setPropagationBehavior(TransactionTemplate.PROPAGATION_REQUIRES_NEW);

return transactionOperations.execute(new TransactionCallback<Long>() {
    public Long doInTransaction( TransactionStatus status ) {
        return allocateBatchTxn( idKey, batchSize );
    }
}); 

Answer 3

Answering my own question.

The root cause of my problem is a couple of things in HibernateTransactionManager.

• The setting 'autodetectDataSource' which defaults to true
• In afterPropertiesSet() with the above true it auto-detects the DataSource from the SessionFactory
• In doBegin() if the DataSource is not null it will bind new transactions to the SessionFactory AND the DataSource

This is causing my problem because also I have a new DataSourceTransactionManager it still uses the same underlying storage (TransactionSynchronizationManager) to manage transactions and because both use the DataSource you get this leaking of transactions between txn managers. I might argue that a txn manager should include its own 'key/id' in the key for the transactional resources so there are independent but it doesn't appear to do that.

The response above are sensible. Using the hibernate txn manager rather than creating a new DataSourceTransactionManager and then using REQURES_NEW would solve the problem. However in my case that would introduce a circular dependency between HTM -> SessionFactory -> IdAllocator -> HTM.

I came up a solution that works but isn't the most elegant thing ever.

When constructor the id allocator it is passed a DataSource in the constructor. I simply wrap that DataSource in a delegating wrapper that is 100% pass through. This changes the DataSource reference so the txn logic does not think there is a transaction in progress and works as I want it to.