Dynamic Multi‑DataSource Management and Transaction Handling in Spring

1. Background

A primary database and multiple application databases need to be accessed simultaneously, raising two problems: how to dynamically manage and switch among many 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 , which are populated during bean initialization via afterPropertiesSet . The abstract method determineCurrentLookupKey returns a lookup key (usually stored in thread context) to select the appropriate DataSource from resolvedDataSources .

3. Configuration‑File Solution

Steps:

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

Configure multiple data sources in application.yml (e.g., spring.datasource.druid.master , spring.datasource.druid.second ).

Declare beans for each data source and a primary DynamicDataSource that injects them into targetDataSources and defaultTargetDataSource .

Use the @SwitchDataSource annotation (via DataSourceAspect ) to switch the lookup key at runtime.

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.mysql.jdbc.Driver
# 主数据源
spring.datasource.druid.master.url=jdbcUrl
spring.datasource.druid.master.username=***
spring.datasource.druid.master.password=***
# 其他数据源
spring.datasource.druid.second.url=jdbcUrl
spring.datasource.druid.second.username=***
spring.datasource.druid.second.password=***

@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
targetDataSources = new HashMap<>(5);
        targetDataSources.put(DataSourceNames.FIRST, firstDataSource);
        targetDataSources.put(DataSourceNames.SECOND, secondDataSource);
        return new DynamicDataSource(firstDataSource, targetDataSources);
    }
}

3.1 AOP Handling

Define SwitchDataSource annotation and a DataSourceAspect that intercepts methods, sets the lookup key in DataSourceContextHolder , and restores it after execution.

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

4. Database‑Table Solution

To avoid hard‑coding data sources, store their configurations in a database table and load them at startup. Define a DataSourceManager interface for CRUD operations on the table.

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

The custom DynamicDataSource implements this interface, allowing runtime addition and removal of data sources.

6. Multi‑Database Transaction Handling

6.1 Understanding Transactions

Spring transactions involve begin, commit, rollback, suspend, and resume semantics. Only setAutoCommit() , commit() , and rollback() exist at the JDBC level; other actions are framework abstractions.

6.2 Custom Transaction Management

Implement a ConnectionProxy that overrides commit and rollback to defer actual execution until the outermost transaction decides. Use a TransactionHolder to maintain a stack of transaction IDs, connection proxies, and lookup keys per thread.

public class ConnectionProxy implements Connection {
    private final Connection connection;
    public ConnectionProxy(Connection connection) { this.connection = connection; }
    @Override public void commit() throws SQLException { /* defer */ }
    public void realCommit() throws SQLException { connection.commit(); }
    @Override public void rollback() throws SQLException { if(!connection.isClosed()) connection.rollback(); }
    // other methods delegate to 'connection'
}

The MultiTransactionAop aspect starts a transaction stack, switches the data source, and on method exit either commits or rolls back, finally popping the stack and restoring the previous lookup key.

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

7. Summary

The article presents two solutions for managing multiple data sources in Spring: a configuration‑file approach suitable for static environments, and a database‑table approach that enables dynamic addition and removal of data sources, together with a custom transaction framework that ensures consistency across databases in a monolithic application.