Spring boot multiple database transaction with Routing Datasource and JdbcTemplate

I need to do a transactional operation across two different Databases. I can find multiple examples for JPA multiple datasource transactions but couldn't find any for JdbcTemplate. Anyhow, I tried the same with some alterations using ChainedTransactionManager. Here is the sample code

// This gives the Datasource list in cluster 1. Similarly done for cluster 2 too.
    @Bean(name = "loadCluster1Bean")
    public List<DataSource> loadCluster1() {
        List<DataSource> dataSources = new ArrayList<>();
        try {
            // Do Hikari Configs and Get the DataSources
            dataSources = getDatasources()
        } catch (Exception e) {
          ...LOG
        }
        return dataSources;
    }

Here we create the datasourceTransaction Manager with the list of data sources.

    @Bean(name = "cluster1TransactionManager")
    @DependsOn({"loadCluster1Bean"})
    List<DataSourceTransactionManager> tm1(@Qualifier("loadCluster1Bean") List<DataSource> dataSources) {
        List<DataSourceTransactionManager> dataSourceTransactionManagers = new ArrayList<>();
        for (DataSource dataSource : dataSources) {
            dataSourceTransactionManagers.add(new DataSourceTransactionManager(dataSource));
        }
        return dataSourceTransactionManagers;
    } 

    @Bean(name = "cluster2TransactionManager")
    @DependsOn({"loadCluster2Bean"})
    List<DataSourceTransactionManager> tm2(@Qualifier("loadCluster2Bean") List<DataSource> dataSources) {
        List<DataSourceTransactionManager> dataSourceTransactionManagers = new ArrayList<>();
        for (DataSource dataSource : dataSources) {
            dataSourceTransactionManagers.add(new DataSourceTransactionManager(dataSource));
        }
        return dataSourceTransactionManagers;
    }

Below is the chainedTransactionManager

@Configuration
@ComponentScan
public class TransactionManagerConfig {
    @Bean(name = "chainedTransactionManager")
    public ChainedTransactionManager transactionManager(
            @Qualifier("cluster1TransactionManager") List<DataSourceTransactionManager> cluster1TransactionManagers,
            @Qualifier("cluster2TransactionManager") List<DataSourceTransactionManager> cluster2TransactionManagers) {

        List<DataSourceTransactionManager> transactionManagers = new ArrayList<>();
        transactionManagers.addAll(cluster1TransactionManagers);
        transactionManagers.addAll(cluster2TransactionManagers);

        return new ChainedTransactionManager(transactionManagers.toArray(new DataSourceTransactionManager[transactionManagers.size()]));
    }
}

Then I add the chainedTransactionManager as below to the Service layer (pseudocode)

@Service
public class ServiceClass {

    @Transactional(value = "chainedTransactionManager")
    public void addData(data, dbName) {
        for(cluster : clusters) {   //Same data needs to be inserted into multiple clusters. This is where the transactional requirement comes. 
            DBConnectionContextHolder.setDatabaseInstance(cluster, dbName);
            Dao.addData(data);
        }
    }  
    
}

Below I have added the DBConnectionContextHolder class and AbstractRoutingDataSource class.

public class DBConnectionContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    private static Set<String> enabledDatabases = new HashSet<>();

    private DBConnectionContextHolder() {
    }

    public static boolean setDatabaseInstance(String dbName, String cluster) {}
        contextHolder.set(cluster+"_"+dbName);
        return true;
    }

    public static void setEnabledDatabases(Set<String> enabledDatabases) {
        DBConnectionContextHolder.enabledDatabases = enabledDatabases;
    }

    public static String getDatabase() {
        return contextHolder.get();
    }

}

public class RoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DBConnectionContextHolder.getDatabase();
    }
}

    private static final RoutingDataSource updatedRoutingDataSource = new RoutingDataSource();

             //targetDataSource is a map which contains key: db-cluster name, value: HikariDataSource

updatedRoutingDataSource.setTargetDataSources(targetDataSources);
updatedRoutingDataSource.afterPropertiesSet();

But when I run the code it doesn't work :/ Seems the connection is not getting updated to the next cluster.

org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO BLAH (A, B, C, D, E, F, G, H, I) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)]; Duplicate entry 'abababab-1122233-2020-08-18 00:00:00-2020-09-30 00:00:00-1599064' for key 'idx_abcde'; nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'abababab-1122233-2020-08-18 00:00:00-2020-09-30 00:00:00-1599064' for key 'idx_abcde'

Without @Transactional it works and data gets added into both clusters. But yes, if one cluster fails it doesn't rollback.

I'd really appreciate it if anyone could help to get it to the working condition :) Thanks.