Transaction Management across two data sources (ChainedTransactionManager) - SpringBoot

Question

Why Spring ChainedTransactionManager is deprecated? Do spring provides any alternative lib to support multiple transaction managers?

My use-case:- We are building a spring boot application that is connected to two data sources let's say (db1 and db2), which performs insert operation on both databases (db1 and db2). And our requirement is something like this : insert -> DB1 -> SUCCESSFUL insert -> DB2 -> ERROR ROLLBACK DB1

Currently, we are using ChaninedTransactionManager and it is working as expected, but I Can see that lib is deprecated? So, just wanted to make sure is it safe to use that, or does Spring provide any alternate lib which we can use as a replacement for this?

Answer 1

ChainedTransactionManager

The configured instances will start transactions in the order given and commit/rollback in reverse order, which means the PlatformTransactionManager most likely to break the transaction should be the last in the list configured.

If you chained transactions in this order : transaction1, transaction2

transaction1 begin
  transaction2 begin
  transaction2 commit -> error rollbacks, rollbacks transction1 too
transaction1 commit -> error, only rollbacks transaction1

The case insert -> DB1 -> SUCCESSFUL insert -> DB2 -> ERROR ROLLBACK DB1 is working.

But, if you had insert -> DB1 -> FAIL ROLLBAK DB1 -> DB2 -> SUCCESSFUL, the insert is commited for DB2 and not for DB1. More details in this article.

If your are OK with this, you can copy the class in your project et keep using it : https://github.com/spring-projects/spring-data-commons/issues/2232#issuecomment-1018473289

JtaTransactionManager with Atomikos

To have all transactions rollback if one fails, I changed my conf to use JtaTransactionManager (with spring boot and posgtres).

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>

application.properties

my.datasource.one.unique-resource-name=
my.datasource.one.xa-properties.url=jdbc:postgresql://
my.datasource.one.xa-data-source-class-name=org.postgresql.xa.PGXADataSource
my.datasource.one.xa-properties.user=
my.datasource.one.xa-properties.password=
my.datasource.one.max-pool-size=
my.datasource.one.min-pool-size=

my.datasource.two.unique-resource-name=
my.datasource.two.xa-properties.url=jdbc:postgresql://
my.datasource.two.xa-data-source-class-name=org.postgresql.xa.PGXADataSource
my.datasource.two.xa-properties.user=
my.datasource.two.xa-properties.password=
my.datasource.two.max-pool-size=
my.datasource.two.min-pool-size=

    @Bean
    @Primary
    @ConfigurationProperties("my.datasource.one")
    public DataSource dataSourceOne() {
        return new AtomikosDataSourceBean();
    }

    @Bean("entityManagerOne")
    @DependsOn("transactionManager")
    public LocalContainerEntityManagerFactoryBean entityManagerOne(@Autowired JpaVendorAdapter jpaVendorAdapter) {
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        properties.put("hibernate.default_schema", "public");
        properties.put("hibernate.ddl-auto", "none");

        LocalContainerEntityManagerFactoryBean entityManager = new LocalContainerEntityManagerFactoryBean();
        entityManager.setJtaDataSource(dataSourceOne());
        entityManager.setJpaVendorAdapter(jpaVendorAdapter);
        entityManager.setPackagesToScan("");
        entityManager.setPersistenceUnitName("");
        entityManager.setJpaPropertyMap(properties);

        return entityManager;
    }

    @Bean
    @Primary
    @ConfigurationProperties("my.datasource.two")
    public DataSource dataSourceTwo() {
        return new AtomikosDataSourceBean();
    }

    @Bean("entityManagerTwo")
    @DependsOn("transactionManager")
    public LocalContainerEntityManagerFactoryBean entityManagerTwo(@Autowired JpaVendorAdapter jpaVendorAdapter) {
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        properties.put("hibernate.default_schema", "public");
        properties.put("hibernate.ddl-auto", "none");

        LocalContainerEntityManagerFactoryBean entityManager = new LocalContainerEntityManagerFactoryBean();
        entityManager.setJtaDataSource(dataSourceTwo());
        entityManager.setJpaVendorAdapter(jpaVendorAdapter);
        entityManager.setPackagesToScan("");
        entityManager.setPersistenceUnitName("");
        entityManager.setJpaPropertyMap(properties);

        return entityManager;
    }

jta.properties

com.atomikos.icatch.enable_logging=false
com.atomikos.icatch.default_jta_timeout=60000000
com.atomikos.icatch.max_timeout=100000000
com.atomikos.icatch.threaded_2pc=true

    @Bean
    public JpaVendorAdapter jpaVendorAdapter(@Autowired Environment env) {
        HibernateJpaVendorAdapter hibernateJpaVendorAdapter = new HibernateJpaVendorAdapter();
        hibernateJpaVendorAdapter.setShowSql(false);
        return hibernateJpaVendorAdapter;
    }

    @Bean
    public UserTransaction userTransaction() throws SystemException {
        var userTransaction = new UserTransactionImp();
        userTransaction.setTransactionTimeout(60000);
        return userTransaction;
    }

    @Bean
    public TransactionManager atomikosTransactionManager() throws SystemException {
        var userTransactionManager = new UserTransactionManager();
        userTransactionManager.setForceShutdown(true);
        return userTransactionManager;
    }

    @Bean
    public PlatformTransactionManager transactionManager(@Autowired UserTransaction userTransaction, @Autowired TransactionManager atomikosTransactionManager) throws Throwable {
        return new JtaTransactionManager(userTransaction, atomikosTransactionManager);
    }

Enable prepared transactions for postgres

postgresql.conf

max_prepared_transactions = 100     # zero disables the feature

Sources that helped me to get this :

https://www.baeldung.com/java-atomikos

http://www.thedevpiece.com/configuring-multiple-datasources-using-springboot-and-atomikos/

https://github.com/YihuaWanglv/spring-boot-jta-atomikos-sample