CODEWITHSUNDEEP
javaspringtransactional
Rajvidya Chandele
Rajvidya Chandele

Reputation: 195

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>

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

Question Is:

  1. Why is MySQL changes are not getting rolled back?
  2. 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)
  3. Can you please point me to an example / tutorial where this kind case is handled?

Sincerely thanks for reading this!

Upvotes: 4

Views: 5251

Answers (1)

eis
eis

Reputation: 53482

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

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

Upvotes: 1

Related Questions