Springboot JPA Rollback distributed Transaction with multi databases

In this tutorial, we will discuss how to configure rollback transactions for multiple database connections, how to use @Transactional and some common pitfalls. For this article, we will talk about making distributed transactions using ChainedTransactionManager and we will use two databases (Oracle and SqlServer).

Transactional with multi-databases

For example, the sample spring-boot application connects two databases ( SQL Server and Oracle).

Connection Database 1: OracleDatabaseConfiguration

Database 1: Oracle database configuration with spring JPA annotation and this database It is a primary one for the project.

@Primary annotation is used to give a higher preference to a bean when there are multiple beans of the same type(DataSourceProperties). The @Primary annotation may be used on any class directly or indirectly annotated with @Component or on methods annotated with @Bean.

Connection Database 2: SQL_SERVER — SqlServerDatabaseConfiguration

Database 2: SQL Server database configuration with spring JPA annotation.

application.properties

##application.properties
##SQL Server
sqlserver.datasource.url=jdbc:sqlserver://127.0.0.1;databaseName=TEST
sqlserver.datasource.username=sa
sqlserver.datasource.password=YourStrongPassword$123$db
##Oracle
oracle.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:test
oracle.datasource.username=root
oracle.datasource.password=YourStrongPassword$123$db

TransactionManagerConfig

Now we have configured our application with two databases, there might be a scenario where we need to perform transactions on both databases simultaneously and If one database operation fails, we want to rollback both database operations. If we are using a single database, using “@Transactional” annotation is enough to handle the rollback. However, if we use this annotation for multi-databases, it will fail because now there are two different transactions. How can we fix this issue?

To resolve this, we need to use ChainedTransactionManager object. See the following classes

BackendService sample codes

The sample codes below are about to roll back the transactions with multi-databases and make a failure error exception in the method.

/**
 * @author Mak Sophea
 * @date : 1/15/2020
 **/
@Service
@Transactional
public class BackendService {

    //Category table is in SQL Server
    @Autowired
    private CategoryRepository categoryRepository;
 
    //Audit table is in Oracle Server
    @Autowired
    private AuditLogRepository auditLogRepository;

    @Transactional(value = "chainedTransactionManager")
    public void saveIntoDatabase() {
       
       //persist data Sql server
      categoryRepository.save(new record 1);
      //persist data Sql server
      categoryRepository.save(new record 2);
      //..more logic and then at somepoints we got Run time error...
      auditLogRepository.trackAuth(); //oracle database
    
     throws new Exception("sth went wrong, rollback transactions");
     ...
    }
}

Conclusion

I hope this article can help you and enlarge your knowledge.

If this article is helpful, please support to press on the clap 👏 button and help to share with other readers to reach this story as well.

Ref :

SpringBoot with spring data JPA using multi-data source databases MySql + SQLServer

📝 Save this story in Journal.

👩‍💻 Wake up every Sunday morning to the week’s most noteworthy stories in Tech waiting in your inbox. Read the Noteworthy in Tech newsletter.