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