Dynamic Multi-DataSource Management and Transaction Handling in Spring

In a scenario with one master database and multiple application databases, two main challenges arise: dynamic management and switching of multiple data sources, and guaranteeing data consistency (transactions) across them.

Spring's AbstractRoutingDataSource can be extended to achieve data‑source switching. The class uses targetDataSources and defaultTargetDataSource to hold all configured data sources, and the abstract method determineCurrentLookupKey (returning a lookup key stored in thread‑local context) to select the appropriate DataSource from a Map<Object, DataSource> .

Configuration‑file solution involves defining a DynamicDataSource class that extends AbstractRoutingDataSource , overriding determineCurrentLookupKey , and configuring beans for each data source in application.yml (or properties ). The beans are injected into targetDataSources and the default source via afterPropertiesSet() . An AOP aspect DataSourceAspect intercepts methods annotated with @SwitchDataSource to set the lookup key.

However, this approach cannot add or remove data sources at runtime. To overcome this, a database‑table solution stores data‑source definitions in a table, loads them at startup, and manages them through a custom DataSourceManager interface:

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

The custom DynamicDataSource implements AbstractDataSource and uses the manager to load, add, or remove data sources dynamically.

For multi‑database transaction consistency, a custom MultiTransaction annotation is introduced, together with a ConnectionProxy that suppresses automatic commit and close calls, exposing realCommit and realClose for manual control:

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

A TransactionHolder maintains transaction state per thread, including stacks for nested transactions and data‑source keys. The MultiTransactionAop aspect starts a transaction stack, switches data sources, and ensures that commit or rollback is performed only when the outermost transaction completes:

@Around("pointcut()")
public Object aroundTransaction(ProceedingJoinPoint point) throws Throwable {
    // obtain annotation, switch data source, start transaction stack
    try {
        Object result = point.proceed();
        multiTransactionManager.commit();
        return result;
    } catch (Throwable ex) {
        multiTransactionManager.rollback();
        throw ex;
    } finally {
        // pop stacks, restore previous data source, close if outermost
    }
}

The article concludes that this dynamic data‑source and custom transaction management works well for monolithic applications, while distributed systems should adopt XA or frameworks like Seata for cross‑service transactions.

Overall, the solution provides a flexible way to add, remove, and switch data sources at runtime and to manage multi‑database transactions without relying on heavyweight distributed transaction protocols.