Master Dynamic Multi‑DataSource Switching and Transaction Management in Spring

Background

In a system with one master database and multiple application databases, both the master and the application databases may be accessed simultaneously, requiring dynamic management of many data sources and ensuring transaction consistency across them.

Data‑Source Switching Principle

Spring’s AbstractRoutingDataSource can be extended to switch data sources at runtime. The class holds targetDataSources and defaultTargetDataSource. During bean initialization, afterPropertiesSet() copies these definitions into resolvedDataSources. The abstract method determineCurrentLookupKey() returns a lookup key (usually stored in a thread‑local context) which is used to fetch the appropriate DataSource from resolvedDataSources.

Configuration‑File Solution

Steps:

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

Configure multiple data sources in application.yml (or properties) and inject them as beans.

In a @Configuration class, create beans for each physical data source and a primary DynamicDataSource that receives targetDataSources and defaultTargetDataSource.

When business code calls getConnection() on the routing data source, Spring invokes determineTargetDataSource() which returns the selected DataSource.

@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 and let DataSourceAspect set the thread‑local key before the method executes.

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

Limitation: data sources defined in configuration cannot be added or removed at runtime.

Database‑Table Solution

To allow dynamic addition/removal, store data‑source definitions 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<DataSource> all();
}

The custom DynamicDataSource implements DataSourceManager, loads entries from the table into the routing map, and works with the same AOP mechanism as the configuration‑file approach.

Multi‑Database Transaction Handling

Spring’s native transaction manager works with a single data source. To achieve atomicity across several databases, a custom transaction framework is built.

Understanding Spring Transactions

Spring defines transaction lifecycle methods (begin, commit, rollback, suspend, resume) on top of JDBC actions ( setAutoCommit(), commit(), rollback()). The framework stores the current Connection in DataSourceTransactionObject during a transaction.

Custom Multi‑Transaction Annotation

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface MultiTransaction {
    String transactionManager() default "multiTransactionManager";
    IsolationLevel isolationLevel() default IsolationLevel.DEFAULT;
    String datasourceId() default "default";
    boolean readOnly() default false;
}

Connection Proxy

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 rollback() throws SQLException { if (!connection.isClosed()) connection.rollback(); }
    // other methods delegate to 'connection'
}

Transaction Context

A TransactionHolder keeps a stack of transaction IDs, associated ConnectionProxy objects, and the current datasource key. When a method annotated with @MultiTransaction starts, a new ID is generated and pushed onto the stack; on exit the stack is popped and the outer transaction resumes.

AOP Implementation

@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);
        // switch datasource, start transaction stack, invoke method
        // commit on success, rollback on exception, finally pop stack
        return null; // simplified for brevity
    }
}

The framework commits only when the outermost transaction finishes; inner transactions merely record their actions. This approach works for monolithic applications where all databases run in the same JVM. In a micro‑service architecture, a distributed transaction solution such as Seata would be required.

Conclusion

The article presents two ways to manage multiple data sources in Spring: a static configuration‑file method and a dynamic database‑table method, and shows how to build a custom multi‑database transaction manager to keep data consistent across several databases.