Dynamic Multi‑DataSource Management and Transaction Handling in Spring

1. Background

In a scenario with one master database and N application databases, both the master and the application databases may be accessed simultaneously, requiring solutions for two problems: how to dynamically manage multiple data sources and switch between them, and how to guarantee data consistency (transactions) across multiple data sources.

2. DataSource Switching Principle

By extending Spring's AbstractRoutingDataSource , data source switching can be achieved. The class contains targetDataSources and defaultTargetDataSource for all configured data sources, and during bean initialization the afterPropertiesSet method copies these targets into resolvedDataSources and resolvedDefaultDataSource . The abstract method determineCurrentLookupKey() is overridden to return a lookup key stored in a thread‑local context, which is then used to fetch the appropriate DataSource from resolvedDataSources .

3. Configuration‑File Solution

The configuration‑file approach follows these steps:

Define a DynamicDataSource class that extends AbstractRoutingDataSource and overrides determineCurrentLookupKey() .

Configure multiple data sources in application.properties (e.g., a master datasource and a secondary datasource).

Declare beans for each datasource and a primary bean for DynamicDataSource that injects the target data sources.

@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);
    }
}

Business methods can switch data sources using an AOP annotation @SwitchDataSource defined as:

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

The DataSourceAspect intercepts methods annotated with @SwitchDataSource and updates the thread‑local DataSourceContextHolder with the specified key.

4. Database‑Table Solution

To avoid hard‑coding data sources in configuration files, a database table is used to store datasource definitions. The table contains fields such as URL, username, password, etc. A DataSourceManager interface provides CRUD operations for these records:

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 loads datasource configurations from the table at application startup using DataSourceBuilder and can add or remove datasources at runtime.

5. Multi‑Database Transaction Handling

Spring's AbstractRoutingDataSource only supports single‑database transactions. To handle transactions across multiple databases, a custom transaction manager is introduced:

Define a @MultiTransaction annotation to specify the target datasource, isolation level, and read‑only flag.

Implement MultiTransactionAop that creates a transaction stack, switches the datasource, and manages commit/rollback manually.

Wrap the JDBC Connection in a ConnectionProxy that disables automatic commit and close operations, exposing realCommit and realClose for explicit control.

public class ConnectionProxy implements Connection {
    private final Connection connection;
    public ConnectionProxy(Connection connection) { this.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 { connection.close(); }
    // other Connection methods delegate to the underlying connection
}

The transaction holder maintains thread‑local stacks for transaction IDs, datasource keys, and connection proxies, ensuring that nested transactions are correctly committed or rolled back only when the outermost transaction completes.

6. Summary

The article presents two solutions for multi‑datasource management in Spring: a configuration‑file based approach and a database‑table based approach, both using AbstractRoutingDataSource for dynamic switching. It also describes a custom multi‑database transaction mechanism that leverages AOP, thread‑local context, and a connection proxy to achieve atomic operations across several databases, suitable for monolithic applications.