Spring Boot: Transaction with two DB, rollback first DB if second DB fails

Question

I am building a Spring Boot application that has two datasources. I need to make an update in DB1 and then in DB2. But if DB2's update fails, DB1 update should rollback.

I have seen a post with the same problem but the ChainedTransactionManager implementation doesn't work for me.

My current implementation is:

I have 2 beans with different datasources for the transaction managers:

    <bean id="dataSourceTransactionManagerSP" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" primary="true">
        <constructor-arg ref="dataSourceSP"/>
    </bean>

    <bean id="dataSourceTransactionManagerBOL" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <constructor-arg ref="dataSourceBOL"/>
    </bean>

I have two transactional methods that make the updates. For some weird reason the @Transactional annotation didn't work in any method. But so far, these two methods works well within its datasource.

For first DB:

    @Override
    public Boolean updateDB1() {
        transactionTemplate.setTransactionManager(dataSourceTransactionManagerSP);
        return transactionTemplate.execute(status -> {
            boolean r1 = repository1.update1();
            boolean r2 = repository1.update2();
            return r1 && r2;
        });
    }

For second DB:

    @Override
    public Boolean updateDB2() {
        transactionTemplate.setTransactionManager(dataSourceTransactionManagerBOL);
        return transactionTemplate.execute(status -> {
            boolean r1 = repository2.update1();
            boolean r2 = repository2.update2();
            return r1 && r2;
        });
    }

But now I need a method that calls updateDB1 and updateDB2 and if DB2 fails, DB1 rollbacks.

    @Override
    public Boolean updateBoth() {

        return transactionTemplate.execute(status -> {
            boolean r1 = updateDB1();
            boolean r2 = updateDB2();
            return r1 && r2;
        });
    }

I know that this won't work because I need to specify the TransactionManager, but this is the problem, I have two transaction managers.

score 1 · Accepted Answer · answered May 24 '19 at 06:23

1

I think you should do the control of rolling back DB1 in the same method updateBoth. In spring Doc you can find this example:

transactionTemplate.execute(new TransactionCallbackWithoutResult() {

  protected void doInTransactionWithoutResult(TransactionStatus status) {
    try {
      updateOperation1();this is your updateDB1()
      updateOperation2();-->this is your updateDB2()
    } catch (SomeBusinessExeption ex) {
      status.setRollbackOnly();
    }
  }
});

I would make updateDB2() launch some custom Exception that could be caught in the catch and then make status roll back. In this case, given that is DB1 the one that you want to roll back manually, the transaction manager to use is the dataSourceTransactionManagerSP (DB1). You can control the Transaction of DB2 with @Transactional("dataSourceTransactionManagerBOL")

answered May 24 '19 at 06:23

Jose Luis

254
2
11

Thanks! Could you please share the Spring Doc where you found this? – Ramon Paris May 24 '19 at 14:41
Sure, here you are: https://docs.spring.io/spring/docs/3.0.0.M3/reference/html/ch11s06.html – Jose Luis May 25 '19 at 08:10
Your answer helped me to find the solution :). At the end I created a new Transaction template that works only with DB1, created the transaction and inside this, another transaction template that works for DB2. If DB2 fails, i throw a exception catched by the first transactionTemplate and the rollback works. Thanks a lot! – Ramon Paris May 28 '19 at 21:45

Spring Boot: Transaction with two DB, rollback first DB if second DB fails

1 Answers1