Spring transaction management to work with multiple data sources

This might be a repetitive question for you, but I couldn't find (atleast I couldn't understand) a satisfactory answer, hence asking again.

I am working with two data sources (MySQL and Oracle). Following is a flow of execution: Main method-A calls method-B (Which writes into Oracle DB) then it(Method-A) calls method-C (Which writes into mySQL DB) then it(Method-A) calls method-D(Which writes into Oracle DB).

If failure occurs at any of place, everything should be rolled back. Currently only changes in Oracle DB are getting rolled back & mySQL DB is not getting rolled back.

I have defined two transactional managers.

=========> First <=========

<tx:annotation-driven transaction-manager="txManager" mode='proxy' proxy-target-class='true’/>
<bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="SessionFactory" />
</bean>
<bean id=“SessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean” parent="AbstractSessionFactory" depends-on="AppConfigHelper”>
<property name="hibernateProperties”> 
...
ORACLE DB Properties
</property>
</bean>
<aop:aspectj-autoproxy/>

==============================
=========> Second <=========

<tx:annotation-driven transaction-manager="txManager2" mode='proxy' proxy-target-class='true'/>
<bean id="txManager2" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="SessionFactory2" />
    <qualifier value="CherryTransaction" />
</bean>
<aop:aspectj-autoproxy/>
<bean id="SessionFactory2" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean" parent="AbstractSessionFactory2" depends-on="AppConfigHelper">
    <property name="hibernateProperties">
    ...
    MYSQL DB Properties
    </property>
</bean>

==============================

On top of method-A I have used @Transactional annotation
On top of method-B I have used @Transactional annotation
On top of method-C I have used @Transactional("txManager2") annotation
On top of method-D I have used @Transactional annotation

Question Is:

Why is MySQL changes are not getting rolled back?
Is the only way to get this working is to use Global transaction management using JTA? (Its a legacy system, and this is the only place where I need to interact with two DBs)
Can you please point me to an example / tutorial where this kind case is handled?

Sincerely thanks for reading this!

Solution

For that to work, AFAIK you'd need to use JTA. Even that won't help if you're using a storage engine in MySQL that doesn't support transactions. With MySQL, only InnoDB and BDB storage engines support transactions.

If you are using MySQL with storage engine that supports transactions, you need to configure XA drivers for both Oracle and MySQL datasource and ensure that both datasources are enlisted in the transaction of your container. Spring then needs to participate in the same transaction. You can't use HibernateTransactionManager for this, but need the JtaTransactionManager, as explained in this thread.

So, what you need for this to work is

Use InnoDB or BDB storage engines on MySQL
Configure XA datasources in your application server instead of regular ones
Use JtaTransactionManager on Spring configuration