Transaction management for multiple database Using

Question

Am coming up with a Spring & Hibernate application which has Inserts/Update to its own system database and it even has to Insert/update other systems with different database types.

How can i achieve the transaction management with Hibernate while performing with different databases.

-Bharat

Answer 1

You can specify two separate transaction managers.

    <bean id="transactionManager1"
    class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory1" />
    <qualifier value="account"/>
</bean>

<bean id="transactionManager2"
    class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory2" />
    <qualifier value="businessData"/>
</bean>

Once your set up is done you can use the qualifier to specify the transaction manager:

    public class TransactionalService {

@Transactional("account")
public void setSomethingInAccount() { ... }

@Transactional("businessData")
public void doSomethingInBusinessData() { ... }
}

Answer 2

Assuming you are looking for transactions involving multiple datasources, I highly recommend you give this series of posts by David Syer (spring lead) on your options:

http://www.javaworld.com/article/2077963/open-source-tools/distributed-transactions-in-spring--with-and-without-xa.html

For example, some databases even support transactions across databases schemas under certain circumstances without distributed transactions (see here).

There are other options other than XA 2-phase commit transaction managers (as they introduce significant overhead and complexity) but they involve different trade-offs.

Answer 3

This will need to be done by the container where your application runs (e.g. JBoss App Server) or you'll need to hook into some other transaction manager such as Bitronix. Out of the box in a JavaSE application, you'll only get a "best-effort" attempt at managing a transaction between multiple datasources.

You'll need to define connections to both databases using an XA driver and then make sure your transaction manager is leveraging a two-phase commit.

If you aren't familiar with Global transactions, start here: http://docs.spring.io/autorepo/docs/spring/4.2.x/spring-framework-reference/html/transaction.html#transaction-global