Spring Boot Dynamic DataSource: Routing, Transaction Timing Pitfalls & Multi-Tenant Patterns
Deep dive into Spring's AbstractRoutingDataSource internals, the critical transaction-routing timing bug that breaks read-write separation, LazyConnectionDataSourceProxy fix, AOP ordering rules, replication lag handling, and multi-tenant schema vs database isolation trade-offs with concrete code and production metrics.
1. Three-Layer Architecture
Spring's dynamic data source uses three collaborating layers (under 100 lines total):
Mapping table : key → real DataSource dictionary, assembled at startup.
Router : extend AbstractRoutingDataSource, implement determineCurrentLookupKey() to return current key.
Context : ThreadLocal holds the key for the current thread; business code or AOP sets it before method entry.
2. Minimal Working Implementation (Four Steps)
// 1. Enum defines data source keys
public enum DBType { MASTER, SLAVE, REPORT }
// 2. Routing context: ThreadLocal holds key
public class DBContext {
private static final ThreadLocal<DBType> HOLDER = new ThreadLocal<>();
public static void use(DBType t) { HOLDER.set(t); }
public static DBType get() { return HOLDER.get() == null ? DBType.MASTER : HOLDER.get(); }
public static void clear() { HOLDER.remove(); }
}
// 3. Routing DataSource: only returns the key
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override protected Object determineCurrentLookupKey() { return DBContext.get(); }
}
// 4. Assembly: key → real DataSource map
@Bean public DataSource dataSource(MasterProps m, SlaveProps s) {
DynamicDataSource ds = new DynamicDataSource();
Map<Object, Object> targets = new HashMap<>();
targets.put(DBType.MASTER, m.build());
targets.put(DBType.SLAVE, s.build());
ds.setTargetDataSources(targets);
ds.setDefaultTargetDataSource(m.build());
return ds;
}Convention : get() falls back to MASTER on null — any request without an explicit key (including non-business threads) hits the master; better to over-read master than accidentally write to replica.
3. Core Flow: One getConnection Routing Journey
1. MyBatis/JPA needs to execute SQL → asks DataSource
2. Gets DynamicDataSource (router)
3. Internally calls determineTargetDataSource()
→ uses determineCurrentLookupKey() return value as key
4. Looks up real DataSource from map → obtains connection
5. SQL runs on real connection
6. Request ends → finally block clears context
Key fact: routing happens at the moment of "getting a connection".
The key at that instant decides which database the connection comes from.4. Core Source Code: Routing Decision Point
// AbstractRoutingDataSource (Spring source)
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource; // fallback to default
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource");
}
return dataSource;
}Three critical details: resolvedDataSources is frozen in afterPropertiesSet() — runtime changes to targetDataSources require calling afterPropertiesSet() again to take effect. lenientFallback defaults to true; missing key silently falls back to default. Production should set false to surface errors instead of quietly writing to the wrong DB.
Key matching uses Map.get 's equals — mixing enums and strings is the most common cause of silent fallback.
5. Upgrading Routing Decision: Read-Only Transaction Awareness
@Override
protected Object determineCurrentLookupKey() {
// Spring marks read-only transactions
if (TransactionSynchronizationManager.isCurrentTransactionReadOnly()) {
return DBType.SLAVE;
}
return DBContext.get();
}Now @Transactional(readOnly = true) automatically routes to replica with zero business-code intrusion. However, this does not solve the next problem — transaction timing.
6. Transaction & Routing Timing: The Biggest Pitfall
Two facts collide:
Routing occurs when the connection is obtained. @Transactional obtains a connection via the transaction manager at method entry and binds it to the thread.
Result:
Transaction timing pitfall (deduction):
1. @Transactional starts → transaction manager getConnection() → connection bound to thread
2. Changing routing key inside the method body has NO effect:
the connection was fixed at transaction start
3. You think you're reading from replica, but the entire transaction runs on master
// Fix: lazy connection proxy
@Bean
public DataSource dataSource(DynamicDataSource inner) {
// Transaction start only "reserves" a connection,
// first actual SQL execution triggers routing
return new LazyConnectionDataSourceProxy(inner);
}LazyConnectionDataSourceProxydefers "get connection" until the first SQL executes, giving AOP a time window to set the key. It is the standard pairing with the router; without it, read-write separation in transactional scenarios is essentially decorative.
7. AOP Aspect: Unified Routing Entry Point
@Aspect
@Component
@Order(Ordered.HIGHEST_PRECEDENCE)
public class DBRouteAspect {
// Order MUST be highest precedence:
// routing key setup must happen BEFORE transaction AOP
@Around("@annotation(readOnly)")
public Object route(ProceedingJoinPoint pjp, ReadOnly readOnly) throws Throwable {
DBContext.use(DBType.SLAVE);
try { return pjp.proceed(); }
finally { DBContext.clear(); } // thread reuse demands cleanup
}
}Self-check checklist : Is aspect Order highest precedence? Is DataSource wrapped with LazyConnectionDataSourceProxy? Does finally call clear()? Missing any one causes silent, random routing failures.
8. Read-Write Separation: Three Engineering Problems
Problem 1: Master-Replica Lag
Write to master, immediate read hits replica before sync → stale data (e.g., order placed but detail page shows "out of stock").
// Write then immediate read: force master
public OrderVO create(OrderCreateDTO dto) {
Order o = orderRepo.save(toOrder(dto));
DBContext.use(DBType.MASTER); // fallback
try { return queryVO(o.getId()); } finally { DBContext.clear(); }
}
// More engineering-grade: time window
// After write, record timestamp in ThreadLocal;
// reads within N seconds forced to masterProblem 2: Which Reads Can Go to Replica
Only reads tolerating second-level lag qualify: list pages, reports, historical queries. Decision reads (pre-order inventory check, pre-payment status check) must hit master — "read stale data then write" costs far more than "read slightly slower".
Problem 3: Read-Only Connection Correctness
spring:
datasource:
master:
jdbc-url: jdbc:mysql://m1:3306/order
username: writer
slave:
jdbc-url: jdbc:mysql://s1:3306/order
username: reader
# Replica account granted ONLY SELECT in MySQL,
# application-layer accidental writes blocked at DB levelPermission guard + routing guard + time-window guard — reliability never relies on a single point.
9. From Routing to Multi-Tenant
Multi-tenant simply changes the routing key from "read/write" to "tenant", but when tenant count grows from 2 to N, a hidden cost surfaces:
// Tenant context: interceptor parses from request header
public class TenantInterceptor implements HandlerInterceptor {
@Override public boolean preHandle(HttpServletRequest req, HttpServletResponse resp, Object handler) {
String tenant = req.getHeader("X-Tenant-Id");
if (tenant == null) throw new BizException("NO_TENANT", "Missing tenant id");
TenantContext.use(tenant);
return true;
}
// afterCompletion MUST clear
}
// Connection pool capacity math (multi-tenant biggest trap):
// Each tenant gets own HikariDataSource, pool size 10, 50 tenants = up to 500 connections
// directly blows DB max_connections
// Fix: schema mode instead of database mode
// One real DataSource + switch search_path
public class TenantAwareDataSource extends AbstractDataSource {
@Override public Connection getConnection() throws SQLException {
Connection conn = target.getConnection();
// Same DB, switch to tenant's schema
conn.createStatement().execute("SET search_path TO \"" + TenantContext.get() + "\"");
return conn;
}
}Choosing between modes : Database mode (one DataSource per tenant) — strongest isolation, cost grows linearly with tenants, suits few high-value tenants. Schema mode (one DB, many schemas) — shared pool, low cost, suits massive small tenants. Schema name MUST pass whitelist validation — concatenating directly into SQL opens an injection vector at the tenant's doorstep.
10. Real-World: Order System Read-Write Separation Refactor
Profile traffic : Slow SQL log classification — list/detail/report reads 87%, decision reads 9%, writes 4%.
Define annotation : @ReadOnly only on latency-tolerant query methods; decision reads explicitly use master; Service-layer AOP sets key uniformly.
Add proxy : LazyConnectionDataSourceProxy + highest-precedence aspect + finally cleanup — all three checks pass.
Validate : Master QPS dropped from 4200 to 610 (-85%); report queries moved to dedicated DB, master P99 fell from 180ms to 42ms. Caught one "forgot to update aspect Order" bug during canary — master/replica QPS comparison on monitoring revealed it instantly.
11. Eight High-Frequency Pitfalls & Troubleshooting Guide
Routing fails inside transaction — missing LazyConnectionDataSourceProxy; connection fixed at transaction start.
Aspect Order not high enough — key set after transaction begins, too late.
Async threads lose key — ThreadLocal doesn't cross threads; @Async /thread pools need propagation or explicit set (reuse TaskDecorator pattern).
Context not cleared — thread-pool reuse leaks previous request's key to next, causing "random wrong DB" routing.
Enum and string keys mixed — Map.get always misses, silent fallback to default, all writes go to master undetected.
Dynamic DataSource addition ineffective — changed targetDataSources but forgot afterPropertiesSet(); resolvedDataSources not refreshed.
Replica account writable — routing bug writes through replica causing master-replica divergence; replica account SELECT -only is last line of defense.
Schema name concatenated without validation — multi-tenant SQL injection entry point; schema must be whitelisted.
Three-Step Troubleshooting
Routing wrong? Three-step locate:
1. Log: print key and stack trace in determineCurrentLookupKey, see who set it
2. Check timing: key set BEFORE or AFTER transaction start? (Is Lazy proxy present?)
3. Check cleanup: context cleared after request? Thread-pool reuse most often missed12. Learning Path & Interview Questions
Four-Stage Roadmap
Stage 1 (Can use) : Build MASTER/SLAVE routing, @ReadOnly annotation switches DB, understand ThreadLocal context.
Stage 2 (Know internals) : AbstractRoutingDataSource source, transaction start timing, LazyConnectionDataSourceProxy lazy connection acquisition.
Stage 3 (Can extend) : Runtime dynamic DataSource add/remove, read-write separation time-window fallback, multi-tenant two-mode selection and implementation.
Stage 4 (Systematic) : ShardingSphere routing system, unitized deployment, data-access layer collaboration with K8s multi-cluster.
12 High-Frequency Interview Questions
How does AbstractRoutingDataSource routing work?
Why must routing key be set before transaction starts?
What problem does LazyConnectionDataSourceProxy solve?
How does read-only transaction auto-route to replica?
Why must ThreadLocal context be cleared? What happens if not?
How to handle master-replica lag? Write-then-immediate-read?
Correct way to dynamically add/remove DataSources?
What is lenientFallback? Enable or disable in production?
How to choose between multi-tenant database mode and schema mode?
Why does connection pool blow up in multi-tenant?
How to propagate routing context in @Async scenarios?
Difference between dynamic DataSource routing and ShardingSphere routing?
13. One-Page Cheat Sheet
Dynamic DataSource · One-Page Cheat Sheet
──────────────────────────────
Three layers: Map + Router + ThreadLocal
Routing happens at getConnection moment
Transaction starts = getConnection → key must be set first
Lazy proxy defers getConnection to first SQL
Aspect Order HIGHEST_PRECEDENCE + finally clear
Key miss defaults to master (rather read master than write replica)
Write-then-read: explicit master / time window
Decision reads ALWAYS master
Multi-tenant: few+high-value → DB mode, many+small → schema mode (whitelist!)
Dynamic add/remove DataSource MUST call afterPropertiesSet()Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
