Spring @Transactional on one service method spanning over two Hibernate transaction managers

I was wondering if it is possible to use two transaction manager in one service methods. Because due to the limitation of client’s mysql db configuration, we have got 2 different datasources within one database, i.e., one user/pwd/url per schema. Thats why i have to configured two transaction managers. Now I got problem when it comes to the service implementation. See the following code:

public class DemoService{
    ...
    @Transactional(value = "t1")
    public doOne(){
        doTwo();
    }

    @Transactional(value = "t2")
    public doTwo(){

    }
    ...
}

if I using this code pattern, i always got the exception

org.hibernate.HibernateException: No Session found for current thread

If i run the two methods seperately, it workd fine. Did i miss something? Or there is other work around here? Any advice would be appreciated.

btw: some of my configuration

    <bean id="sessionFactorySso" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="mappingLocations">
        <list>
            <value>classpath*:sso.vo/*.hbm.xml</value>
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="generateDdl">true</prop>
            <prop key="hibernate.dialect">${dialect} </prop>
        </props>
    </property>
    <property name="dataSource" ref="dataSourceSso"/>
</bean>

<bean id="dataSourceSso" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${driver}"/>
    <property name="jdbcUrl" value="${sso.url}"/>
    <property name="user" value="${sso.username}"/>
    <property name="password" value="${sso.password}"/>
         <!-- these are C3P0 properties -->
    <property name="acquireIncrement" value="2" />
    <property name="minPoolSize" value="1" />
    <property name="maxPoolSize" value="2" />
    <property name="automaticTestTable" value="test_c3p0" />
    <property name="idleConnectionTestPeriod" value="300" />
    <property name="testConnectionOnCheckin" value="true" />
    <property name="testConnectionOnCheckout" value="true" />
    <property name="autoCommitOnClose" value="true" />
    <property name="checkoutTimeout" value="1000" />
    <property name="breakAfterAcquireFailure" value="false" />
    <property name="maxIdleTime" value="0" />
</bean>

<bean id="transactionManagerSso" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactorySso"/>
    <qualifier value="sso" />
</bean>

<tx:annotation-driven transaction-manager="transactionManagerSso" />

Answer

Because you want to enlist two data sources in one transaction you need XA(Global) Transaction.

Therefore you need to:

1. Set the Spring JTA transaction manager
2. You Hibernate properties should use the JTA platform settings
3. Your data source connections should be XA complaint
4. You need an application server JTA transaction manager or a stand-alone tarnsaction manager (Bitronix, Atomikos, JOTM)
5. You will need two session factory configurations, one for each individual data source.
6. And you won’t have two transaction managers: t1 and t2, but instead you will enlist two transactional XA data sources that will be automatically enlisted in the same global transaction, meaning you will have two XA connections being enlisted in the same global transaction. The XA transaction will use the 2PC protocol to commit both resources upon commit time.

Checkout this Bitronix Hibernate example.