Dynamic DataSource Switching and Multi‑Database Transaction Management in Spring

Background : In systems with one master database and many application databases, two challenges arise: dynamic management and switching of multiple data sources, and ensuring data consistency (transactions) across them.

DataSource Switching Principle : By extending Spring's AbstractRoutingDataSource , the framework can select a target DataSource based on a thread‑local lookup key. The key is resolved from resolvedDataSources after afterPropertiesSet() populates the map, making runtime addition of new data sources impossible.

Configuration‑File Solution : Define a DynamicDataSource class extending AbstractRoutingDataSource and override determineCurrentLookupKey() . Configure multiple data sources in application.properties (e.g., master and second). Declare beans for each DataSource and a primary DynamicDataSource that injects targetDataSources and defaultTargetDataSource . Use an AOP aspect ( DataSourceAspect ) with a custom @SwitchDataSource annotation to set the lookup key in DataSourceContextHolder .

Database‑Table Solution : Store DataSource configurations in a database table, allowing runtime addition, deletion, and status monitoring. Define a DataSourceManager interface for CRUD operations on the table. Implement a custom DynamicDataSource that loads DataSources from the table at startup and on demand. Manage transactions manually by wrapping Connection objects in a ConnectionProxy that suppresses automatic commit / rollback and delegates real operations to realCommit() and realClose() .

Multi‑Database Transaction Handling : Explain Spring transaction concepts (begin, commit, rollback, suspend, resume) and their relation to JDBC. Introduce a custom MultiTransaction annotation to specify transaction manager, isolation level, read‑only flag, and target DataSource. Implement MultiTransactionAop that starts a transaction stack, switches DataSources, and commits or rolls back based on method execution. Maintain transaction context with TransactionHolder and TransactionContext to track nested transactions, connection proxies, and DataSource keys. Provide a determineTargetDataSource() override that returns the appropriate connection, using the custom transaction holder when present.

Summary : The article presents a complete solution for dynamic multi‑DataSource management and cross‑database transaction handling in a monolithic Spring application, emphasizing the limitations of AbstractRoutingDataSource and offering a database‑driven approach with custom AOP and transaction management. For micro‑service architectures, a distributed transaction framework (e.g., Seata) would be required.