How to configure transaction management for working with 2 different db in Spring?

Question

I have 2 databases (MySql and HSQLDB). I configured 2 data sources and 2 EntityManagerFactory beans. I can also configure 2 correspondent JpaTransactionManager beans.

But I don't know how to specify which of them should be used to manage transactions for concrete service-class. I want to use @Transactional annotation for that purpose, but I actually can specify only one of txManagers:

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

What is the way out from this situation?

http://stackoverflow.com/questions/1961371/spring-multiple-transactional-datasource — Bozho, Dec 25 '09 at 20:18

score 19 · Answer 1 · answered Dec 25 '09 at 18:45

19

Declare your <tx:annotation-driven> without transaction-manager attribute, declare qualifiers for transaction managers like this:

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

Use this qualifier in @Transactional as a value to select one of transaction managers:

@Transactional("txManager1")

or, with more properties:

@Transactional(value = "txManager1", readOnly = true)

answered Dec 25 '09 at 18:45

axtavt

239,438
41
511
482

1

This solution works. However, if you do have a qualifier on the @Transactional annotation you must specify a transaction manager with the qualifier element configured. At first I thought that the default transaction manager would take over, but that was not the case for me. It complained that I didn't have a proper qualifier configured on my transaction manager bean. – Jorge May 12 '11 at 21:49
1

Please note that this applies to Spring 3 onwards – java25 Jul 02 '14 at 05:18

score 19 · Accepted Answer · answered Dec 25 '09 at 20:14

19

The javadoc for JpaTransactionManager has some advice on this:

This transaction manager is appropriate for applications that use a single JPA EntityManagerFactory for transactional data access. JTA (usually through JtaTransactionManager) is necessary for accessing multiple transactional resources within the same transaction. Note that you need to configure your JPA provider accordingly in order to make it participate in JTA transactions.

In other words, if you find yourself with multiple entity managers, with corresponding tx managers, then you should consider using a single JtaTransactionManager instead. The entity managers should be able to participate in JTA transactions, and this will give you full transactionality across both entity managers, without hacving to worry about which entity manager you're in at any one time.

Of course, JtaTransactionManager does require a full JTA-supporting application server, rather than a vanilla servlet engine like Tomcat.

answered Dec 25 '09 at 20:14

skaffman

398,947
96
818
769

I probably don't understand something, but I thought that transaction manager, used in Spring, is based on database transaction manager i.e. we should create as many tx-managers as many different databases we use. Is it wrong? – Roman Dec 25 '09 at 20:58
3

It's not quite that simple. Spring TX managers are just wrappers around other existing mechanisms such as JTA, JPA/Hibernate or JDBC transactions. JTA is not tied to one database, it's a distributed multi-database tx manager. Using Spring doesn't really change how the underlying mechanisms should be used, it just makes it easier. – skaffman Dec 25 '09 at 22:02
1

Thanks, this sounds a bit unexpected to me, I'll look for it in Sun documentation. – Roman Dec 26 '09 at 00:03
1

What happens if I annotate a method with 2 @Transactional() like :@Transactional(transactionManager = "customerTransactionManager"), @Transactional(transactionManager = "orderTransactionManager") ? I would like to know how to handle both db transactional in one method. – Radu Toader Sep 06 '16 at 08:07

score 13 · Answer 3 · answered Sep 21 '13 at 00:32

Since its after a longtime since the correct answers.

Skaffman may be correct in terms of usability of JpaTransactionManager for multiple databases.

But there is working solution for using 2 different databases with 2 different JpaTransactionManager.

  @Bean(name = "db2TransactionManager")
  public PlatformTransactionManager transactionManager2() throws NamingException {
    JpaTransactionManager txManager = new JpaTransactionManager(entityManagerFactory());
    return txManager;
  }

  @Bean
  @Primary
  public PlatformTransactionManager transactionManager() throws Exception {
     JpaTransactionManager txManager = new JpaTransactionManager(entityManagerFactory());
    txManager.setNestedTransactionAllowed(true);
    return txManager;
  }

@Primary should be used to specify for the ones where you don't specify qualifier name in @Transactional

score 6 · Answer 4 · answered Oct 21 '11 at 05:52

6

You have to specify two transaction managers for that in application-context.xml as below:

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

@Transactional attribute will now use its relevant transaction manager.

answered Oct 21 '11 at 05:52

Mital Pritmani

4,880
8
38
39

This worked for me and I didn't have to specify the qualifier on the txManager beans or in the @Transactional annotation. I was able to access two different session factories also. – jonnysamps Nov 10 '11 at 21:33
Ya, same with me. I also didn't have to specify qualifier with @Transactional annotation. – Mital Pritmani Nov 11 '11 at 05:57
2

Interesting, if I look at `AnnotationDrivenBeanDefinitionParser` which processes the `tx:annotation-driven` all is done once per context only! So the second line is totally useless. – Arne Burmeister Nov 22 '11 at 12:50
This worked for me! It's a pity that Spring official document doesn't mention this trick to set up multiple DataSourceTransactionManager. – tonga Dec 23 '13 at 20:54

How to configure transaction management for working with 2 different db in Spring?

4 Answers4

Linked