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.

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();
}

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

Answer 3

Figured it out.

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

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.