Dynamic Multi-DataSource Switching & Transaction Management in Spring

1. Background

A primary database and N application databases need to be accessed simultaneously, raising two problems: how to dynamically manage and switch multiple data sources, and how to guarantee data consistency (transactions) across them.

2. Data Source Switching Principle

By extending Spring's AbstractRoutingDataSource, data source switching can be achieved. The class contains targetDataSources and defaultTargetDataSource for all configured sources, and resolvedDataSources / resolvedDefaultDataSource are populated during afterPropertiesSet(). The abstract method determineCurrentLookupKey() is overridden to return a lookup key (usually stored in thread context) that selects the appropriate DataSource from resolvedDataSources.

3. Configuration‑File Solution

Steps:

Define a DynamicDataSource class extending AbstractRoutingDataSource and override determineCurrentLookupKey().

Inject multiple data sources via targetDataSources and defaultTargetDataSource, populating them in afterPropertiesSet().

When getConnection() is called, determineTargetDataSource() returns the selected DataSource and its getConnection() is used.

Configuration example:

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.mysql.jdbc.Driver
# master datasource
spring.datasource.druid.master.url=jdbcUrl
spring.datasource.druid.master.username=***
spring.datasource.druid.master.password=***
# secondary datasource
spring.datasource.druid.second.url=jdbcUrl
spring.datasource.druid.second.username=***
spring.datasource.druid.second.password=***

Java configuration:

@Configuration
public class DynamicDataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.druid.master")
    public DataSource firstDataSource(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.second")
    public DataSource secondDataSource(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DynamicDataSource dataSource(DataSource firstDataSource, DataSource secondDataSource){
        Map<Object, Object> targetDataSources = new HashMap<>(5);
        targetDataSources.put(DataSourceNames.FIRST, firstDataSource);
        targetDataSources.put(DataSourceNames.SECOND, secondDataSource);
        return new DynamicDataSource(firstDataSource, targetDataSources);
    }
}

AOP can simplify usage: annotate business methods with @SwitchDataSource to trigger the lookup key change.

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface SwitchDataSource {
    String value();
}

4. Database‑Table Solution

To avoid hard‑coding data sources, store their configurations in a database table. The table holds typical DataSource parameters (URL, username, password, etc.). A DataSourceManager interface provides CRUD operations on these records.

public interface DataSourceManager {
    void put(String key, DataSource ds);
    DataSource get(String key);
    Boolean hasDataSource(String key);
    void remove(String key);
    void closeDataSource(String key);
    Collection<DataSource> all();
}

Implementation loads the table at startup, creates DataSource objects via DataSourceBuilder, and caches them. Switching logic remains the same as the configuration‑file approach, but the source of targetDataSources is dynamic.

5. Multi‑Database Transaction Handling

Spring's AbstractRoutingDataSource supports only single‑database transactions. To achieve consistency across multiple databases, a custom transaction manager wraps Connection objects, suppressing their commit and close methods while exposing realCommit and realClose for manual control.

public class ConnectionProxy implements Connection {
    private final Connection connection;
    @Override public void commit() throws SQLException { /* no‑op */ }
    public void realCommit() throws SQLException { connection.commit(); }
    @Override public void close() throws SQLException { /* no‑op */ }
    public void realClose() throws SQLException { if(!connection.getAutoCommit()){ connection.setAutoCommit(true); } connection.close(); }
    @Override public void rollback() throws SQLException { if(!connection.isClosed()) connection.rollback(); }
    // other methods delegate to 'connection'
}

A TransactionHolder maintains per‑thread stacks for transaction IDs, lookup keys, and associated ConnectionProxy instances, enabling nested transactions. The AOP aspect MultiTransactionAop starts a transaction, switches the data source, and on method exit either commits or rolls back the whole transaction stack.

@Aspect
@Component
@Order(99999)
public class MultiTransactionAop {
    @Pointcut("@annotation(com.github.mtxn.transaction.annotation.MultiTransaction)")
    public void pointcut() {}

    @Around("pointcut()")
    public Object aroundTransaction(ProceedingJoinPoint point) throws Throwable {
        Method method = ((MethodSignature) point.getSignature()).getMethod();
        MultiTransaction mt = method.getAnnotation(MultiTransaction.class);
        if(mt == null) return point.proceed();
        String prevKey = DataSourceContextHolder.getKey();
        MultiTransactionManager mgr = Application.resolve(mt.transactionManager());
        if(mgr.switchDataSource(point, method, mt)) return point.proceed();
        TransactionHolder holder = mgr.startTransaction(prevKey, mt.isolationLevel(), mt.readOnly(), mgr);
        Object result;
        try {
            result = point.proceed();
            mgr.commit();
        } catch (Throwable ex) {
            mgr.rollback();
            throw ex;
        } finally {
            String transId = mgr.getTrans().getExecuteStack().pop();
            holder.getDatasourceKeyStack().pop();
            DataSourceContextHolder.setKey(holder.getDatasourceKeyStack().peek());
            mgr.close(transId);
        }
        return result;
    }
}

The custom manager ensures that all involved connections are committed or rolled back together, avoiding the inconsistency that arises when using Propagation.REQUIRES_NEW across databases.

6. Summary

The article presents two main strategies for multi‑data‑source management in Spring: a static configuration‑file method and a dynamic database‑table method, both built on AbstractRoutingDataSource. It also introduces a custom multi‑transaction framework that wraps connections, maintains transaction stacks, and guarantees atomicity across multiple databases. This approach is suitable for monolithic applications; distributed systems should adopt dedicated distributed‑transaction solutions such as Seata.