Spring multiple transaction managers, one transaction

Question

I have a difficult situation when I have to use two different databases, there I use 2 different transaction managers. Is there a way in Spring to link these transaction managers to work in a single transaction? In the event of an exception at the second data source, the changes should be discarded.

<bean id="baseTransactionProxy" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean" abstract="true"> <property name="transactionManager" ref="transactionManager" /> <property name="transactionAttributes"> <props> <prop key="insert*">PROPAGATION_REQUIRED</prop> <prop key="update*">PROPAGATION_REQUIRED</prop> <prop key="save*">PROPAGATION_REQUIRED</prop> <prop key="remove*">PROPAGATION_REQUIRED</prop> <prop key="*">PROPAGATION_REQUIRED,readOnly</prop> </props> </property> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="oracle.jdbc.OracleDriver" /> <property name="url" value="jdbc:oracle:thin:@dummyHost:1521:dummySID" /> <property name="username" value="owner" /> <property name="password" value="password" /> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="oracle.jdbc.OracleDriver" /> <property name="url" value="jdbc:oracle:thin:@dummyHost2:1521:dummySID2" /> <property name="username" value="owner" /> <property name="password" value="password" /> </bean> <bean id="transactionManager2" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource2" /> </bean> 

Answer 1

You need a global transaction manager that supports two-phase commit (XA). Several independent and free are available. I used Bitronix in a Spring project, but there are Atomicos and possibly others. See http://en.wikipedia.org/wiki/Java_Transaction_API#Opensource_JTA_implementations

Answer 2

You can use the Spring JtaTransactionManager to make sure that both databases are running with the same transaction manager.

Note. You will need to choose a basic implementation, which can be either containerized, for example: WebLogic, WebSphere and OC4J, etc. Or standalone, even open source: for example. Atomikos .

HOWEVER

XA transaction management complicates the situation (configuration / performance / problem solving / maintenance / etc.). And in many cases it can be avoided by clever patterns.

To get a clear idea of ​​whether you need to use an XA transaction manager (e.g. distributed), check out this fantastic Spring article by Dave Sier's own: Distributed Transactions in Spring, with and without XA

Answer 3

You can use abstractRoutingDataSource to route through multiple data sources, but if you have requirements like one rollback affecting another, you will need a JtaTransactionManager for distributed txn control.