Spring: HibernateTransactionManager handling multiple datasources

Question

In the following piece of code (Spring 3):

@Transactional("txManager")
public class DaoHolder {

    @Transactional(value="txManager", readOnly=false, propagation=Propagation.REQUIRES_NEW, rollbackFor={Exception.class})
    private void runTransactionalMethod() throws Exception {
        dao1.insertRow();
        dao2.insertRow();
        //throw new Exception();
    }
    //...
}

• dao1 uses a session factory attached to datasource1
• dao2 uses a session factory attached to datasource2
• txManager is a HibernateTransactionManager using the same session factory as dao1

The code above works correctly in a transactional manner - in particular, when no exception is thrown, each dao operation gets committed (to 2 different datasources). When an exception is thrown each dao operation gets rolled back.

My question is: why does it work? Everywhere I've read I've been told to use a JtaTransactionManager when handling multiple datasources. I'd prefer not to use JTA. What might be the consequences if I leave it running under a HibernateTransactionManager?

---

---

Some more details for the interested:

Each datasource is defined like so:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
    <property name="initialSize" value="${jdbc.initial_size}" />
    <property name="maxActive" value="${jdbc.max_active}" />
</bean>

Each session factory is defined like so:

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="mappingResources">
        <list>
            ... multiple *.hbm.xml files here ...
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
        </props>
    </property>
</bean>

The transaction manager is defined like so:

<bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory"/>
</bean>

Each dao class extends HibernateDaoSupport and the content of the insertRow method is more or less like so for dao1:

getHibernateTemplate().save(obj);

and for dao2:

getHibernateTemplate().merge(obj);

The Question:

I just spent the past day dealing with this exact question: Why do transactions across data sources appear to work with one hibernate transaction manager?

Like you, I also read in multiple places that I needed to use a JtaTransactionManager...and it turns out they were right! I'll explain:

Configuration:

Just like you, I started with 2 data sources, 2 session factories, and 1 HibernateTransactionManager.

My test code also looked very similar to yours and I could save objects to both databases successfully. If I manually threw an exception, neither save would appear in the database. So it seemed that both were being rolled back correctly. However, when I turned on hibernate's debug logging, I could see that neither save was actually sent to the databases so there was nothing to rollback.

The problem is in the test, so I'll change your test to prove that the single transaction manager is actually not working!

The change we need was suggested by JB Nizet on Jan 2:

Have you tried calling flush on both sessions before throwing the exception?

---

A better test:

First, add a flush function to each of your DAO's. This is what mine looks like:

public void flush() {
    sessionFactory.getCurrentSession().flush();
}

Yours will probably look like this:

public void flush() {
    getHibernateTemplate().flush();
}

Now, modify your test to flush each dao before the exception:

 @Transactional("txManager")
public class DaoHolder {

    @Transactional(value="txManager", readOnly=false, propagation=Propagation.REQUIRES_NEW, rollbackFor={Exception.class})
    private void runTransactionalMethod() throws Exception {
        dao1.insertRow();
        dao2.insertRow();

        dao1.flush();
        dao2.flush();

        throw new Exception();
    }
    //...
}

The result:

Only the datasource associated to txManager is rolled back. That makes sense, because txManager does not know about the other data source.

Summary:

In my case, I do not need to access 2 databases in one transaction, separate transactions is fine. So I simply defined a second transaction manager:

<bean id="txManager2" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory2"/>
</bean>

And referenced this by name in the Transactional annotation wherever I access the second database:

@Transactional(value="txManager2"...)

I can now get annotated transactions for my second database, but I still cannot get transactions across both databases...it seems that you will need a JtaTransactionManager for that.

Comments

[machinery]

thanks a lot for this very detailed explanation :) I'm going to have to have a look at my test again, but AFAIR I actually saw the rows appearing in the database and not just in Hibernate. I'll let you know of my findings. Greetings to the beautiful city of Austin (used to live there :))...

[Henry]

In your original test, the flush doesn't occur until after the exception. So the databases are either inserting & committing both rows successfully or failing before any inserts hit the database. The point of transactions & rollbacks is to undo uncommitted inserts that have hit the database.

[Henry]

(continued) The confusion is because hibernate doesn't immediately send the inserts to the database like you'd expect if you were using jdbc directly. An analogy is that you've written a check, but you never dropped it in the mail...so there's no need to go to the bank to put a stop payment on it. In order to test the bank's stop payment system, we actually need to drop the check in the mail, which is what the flush is doing.

[Dante]

Thanks a lot man! Perfect explanation. Had same situation with same questions. Now it is all perfectly clear!