Spring @Transactional with a transaction across multiple data sources

Question

I have to update two data sources as part of one transaction. That is -

1. I do an update in DB1.
2. Then, I do another update in DB2.

If update in DB2 fails, I want to roll back both DB1 and DB2 to roll back. Can this be accomplished using @Transactional ?

Here is a sample code -

@Transactional(value="db01TransactionManager")
public void updateDb01() {
    Entity01 entity01 = repository01.findOne(1234);
    entity01.setName("Name");
    repository01.save(entity01);

    //Calling method to update DB02
    updateDb02();
}

@Transactional(value="db02TransactionManager")
public void updateDb02() {
    Entity02 entity02 = repository02.findOne(1234);
    entity02.setName("Name");
    repository02.save(entity02);

    //Added this to force a roll back for testing
    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}

My problem is that, the setRollbackOnly() in updateDb02 rolls back only the the Db01 transaction.

Tui

You need a single transactionmanager with XA support.. – Bluestocking

Answer 1

I've solved this problem using ChainedTransactionManager - http://docs.spring.io/spring-data/commons/docs/1.6.2.RELEASE/api/org/springframework/data/transaction/ChainedTransactionManager.html

Spring Boot Configuration:

    @Bean(name = "chainedTransactionManager")
    public ChainedTransactionManager transactionManager(@Qualifier("primaryDs") PlatformTransactionManager ds1,
                                                    @Qualifier("secondaryDs") PlatformTransactionManager ds2) {
         return new ChainedTransactionManager(ds1, ds2);
    }

And then you can use it as follows:

@Transactional(value="chainedTransactionManager")
public void updateDb01() {
    Entity01 entity01 = repository01.findOne(1234);
    entity01.setName("Name");
    repository01.save(entity01);

    //Calling method to update DB02
    updateDb02();
}

public void updateDb02() {
    Entity02 entity02 = repository02.findOne(1234);
    entity02.setName("Name");
    repository02.save(entity02);

    //Added this to force a roll back for testing
    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}

Burr

nothing but ChainedTransactionManager worked for me. I had to deal with 4 databases and this is the only thing that could help me – Cantilever

For my future self and others: if you want @Transactional to use ChainedTransactionManager by default, add @Primary on the ChainedTransactionManager bean. – Activity

exactly my problem, exactly the right solution to apply... THANKS! – Segarra

Here's a post I wrote illustrating how to set up a Chained Transaction Manager with some more elaborate code samples. This might help explain how all the components work together to produce this here result: metamorphant.de/blog/posts/… – Myriagram

Please note that ChainedTransactionManager has been deprecated due to its unexpected behavior. please refer to this ticket for further information. – Sulfamerazine

Answer 2

The best way is to create a third method, that will be annotated as @Transactional.

@Transactional(readOnly = false)
public void updateCommon(){
  upbateDb01();
  upbateDb02();
}

According to a spring documentation, transaction control starts when the firts annotation appears,so in this case a single transaction will start when updateCommon will be invoked. UPDATE But this will work if you use CrudRepository or something like that.

In case of multiple datasources you may try to use a Global transaction management conception. Here is a sample from a spring documentation:

@Inject private PlatformTransactionManager txManager; 

TransactionTemplate template  = new TransactionTemplate(this.txManager); 
template.execute( new TransactionCallback<Object>(){ 
  public void doInTransaction(TransactionStatus status){ 
   // work done here will be wrapped by a transaction and committed. 
   // the transaction will be rolled back if 
   // status.setRollbackOnly(true) is called or an exception is thrown 
  } 
});

And here is a link: http://spring.io/blog/2011/08/15/configuring-spring-and-jta-without-full-java-ee/ I have never use it for my own, so I didn't explore this topic deeply. Hope it will help

Seize

My issue is that because I have multiple data sources, it will start one transactionManager with this @Transactional and only that can take part in the transaction. So, with your solution, I will still run into the issue where only one transactionManager will be applicable to all my database updates – Tui

Damn, you're right, somehow I missed it( I will update my answer according to the write conditions – Seize

Answer 3

Figured it out.

The methods have to be in different beans to be able to use different transaction managers.

Tui

If there are in different beans, I guess you would not hit the problem itself. – Djambi

Answer 4

I believe you have defined your txns like below.

@Bean(name="db01TransactionManager") 
@Autowired
DataSourceTransactionManager tm1(@Qualifier ("datasource1") DataSource datasource) {
    DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);
    return txm;
}

@Bean(name="db02TransactionManager") 
@Autowired
DataSourceTransactionManager tm2(@Qualifier ("datasource2") DataSource datasource) {
    DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);
    return txm;
}

Now simplest way is to try , catch and rollback for both of the transaction. But if you still want to delegate, there is an option as given below.

Create your own and override rollback methods and use it.

@Bean(name=“allTransactionManager") 
@Autowired
DataSourceTransactionManager tm2(@Qualifier ("datasource1”) DataSource datasource1, @Qualifier ("datasource2") DataSource datasource2) {

    DataSourceTransactionManager txm  = new MyDataSourceTransactionManager(datasource1,datasouce2);
        return txm;
}

And define your own transactionmanager as.

MyDataSourceTransactionManager extends DataSourceTransactionManager{
DataSourceTransactionManager tm1; 
DataSourceTransactionManager tm2; 

MyDataSourceTransactionManager(Datasource ds1,Datasource d2){
  tm1 = new DataSourceTransactionManager(DataSource);
  tm2 =new DataSourceTransactionManager(DataSource);
}
// override and for roll back, rollback for both of tm1 and tm2. Thus all actions are delegated in this class

}

Then use this for dao layers whereever you want to work synchronously.

 @Transactional("allTransactionManager")

So now we have your own transaction managers, which is capable of rollbacking or commiting together for both type of transactions.

Ulcerate

Well it's looking good. TS @Do Will, please give us a feedback if you will try this. – Seize