Definition
- Read/write splitting – routes write operations to the primary database and read operations to a replica, offloading read-heavy traffic without changing repository code.
- AbstractRoutingDataSource – a Spring class that holds a map of target datasources and overrides
determineCurrentLookupKey()to select one per operation. - @Transactional(readOnly = true) – the cleanest trigger for routing; Spring exposes this flag via
TransactionSynchronizationManager, and the routing key returns REPLICA for read-only, PRIMARY otherwise. - Replication lag – replicas apply changes asynchronously, so a read right after a write may see stale data.
- Read-your-own-writes – must-be-fresh reads are routed back to the primary to avoid showing stale data after a mutation.
The National Bank has one main ledger (primary) for all changes and several photocopy desks (replicas) for quick enquiries. A smart receptionist (the routing datasource) looks at each request: "Are you changing money? Go to the main ledger. Just checking a balance? Go to a photocopy desk." But if you just made a change, the receptionist knows the photocopies may be a second behind, so for your immediate enquiry she sends you back to the main ledger so you always see your own change.
Understanding the Concepts
What is read/write splitting?
Read/write splitting is an architecture pattern where write operations (INSERT, UPDATE, DELETE) go to the primary database and read operations (SELECT) go to one or more replicas. This offloads the read-heavy traffic from the primary without changing any repository code. At the National Bank of India, balance checks and statements vastly outnumber transfers, so routing reads to replicas dramatically reduced primary load.
Application
│
├── Write (transfer) ──→ Primary DB
│
└── Read (balance) ──→ Replica DB
How does AbstractRoutingDataSource work?
Spring provides AbstractRoutingDataSource, which holds a map of target datasources keyed by a lookup value. You override determineCurrentLookupKey() to return the key for the current operation. The routing datasource then picks the matching target.
package com.bank.config;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.support.TransactionSynchronizationManager;
public class ReadWriteRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
boolean isReadOnly =
TransactionSynchronizationManager.isCurrentTransactionReadOnly();
return isReadOnly ? DataSourceType.REPLICA : DataSourceType.PRIMARY;
}
}
public enum DataSourceType {
PRIMARY, REPLICA
}
How do you configure the routing datasource?
Register two target datasources (PRIMARY and REPLICA) and wire them into the routing datasource. Set the default to PRIMARY so that any operation not explicitly marked read-only goes to the primary.
package com.bank.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DataSourceConfig {
@Bean
public DataSource routingDataSource(
DataSource primaryDataSource,
DataSource replicaDataSource) {
ReadWriteRoutingDataSource routing = new ReadWriteRoutingDataSource();
Map<Object, Object> targets = new HashMap<>();
targets.put(DataSourceType.PRIMARY, primaryDataSource);
targets.put(DataSourceType.REPLICA, replicaDataSource);
routing.setTargetDataSources(targets);
routing.setDefaultTargetDataSource(primaryDataSource);
// Wrap in LazyConnectionDataSourceProxy so the connection
// is acquired AFTER the readOnly flag is known
return new LazyConnectionDataSourceProxy(routing);
}
}
How do you trigger routing with @Transactional?
Annotate read service methods with @Transactional(readOnly = true) and write methods with plain @Transactional. The routing happens automatically because the routing datasource reads the readOnly flag.
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class AccountService {
// Reads go to replica
@Transactional(readOnly = true)
public BigDecimal getBalance(String accountId) {
return accountRepository.findBalance(accountId);
}
// Writes go to primary
@Transactional
public void transfer(String fromId, String toId, BigDecimal amount) {
accountRepository.debit(fromId, amount);
accountRepository.credit(toId, amount);
}
// Must-be-fresh read: routed to primary (NOT readOnly)
@Transactional
public BigDecimal getBalanceAfterTransfer(String accountId) {
return accountRepository.findBalance(accountId);
}
}
What is replication lag and why does it matter?
Replicas apply the primary's changes asynchronously. A read routed to a replica immediately after a write may miss that write. For most reads this is acceptable, but for must-be-fresh reads it produces stale data.
Timeline: T0: transfer() writes to primary T1: replica has NOT yet applied the change (lag = 200ms) T2: getBalance() reads from replica → shows OLD balance (stale) T3: replica catches up → now shows correct balance
How do you handle must-be-fresh reads?
Reads that must reflect a just-committed write are routed back to the primary by not marking them readOnly, or by using a session-consistency mechanism that pins reads to the primary for a short window after a write.
// Option 1: do not mark as readOnly (routes to primary)
@Transactional
public BigDecimal getBalanceAfterTransfer(String accountId) {
return accountRepository.findBalance(accountId);
}
// Option 2: ThreadLocal flag for read-your-own-writes
// Set after a write, cleared after the window expires
public class RoutingContext {
private static final ThreadLocal<Boolean> FORCE_PRIMARY = new ThreadLocal<>();
public static void forcePrimary() { FORCE_PRIMARY.set(Boolean.TRUE); }
public static void clear() { FORCE_PRIMARY.remove(); }
public static boolean isForcedPrimary() {
return Boolean.TRUE.equals(FORCE_PRIMARY.get());
}
}
What are the common mistakes?
The most common mistakes are forgetting to clear the ThreadLocal, routing writes to a replica, and not wrapping with LazyConnectionDataSourceProxy.
// WRONG: ThreadLocal not cleared after request
// The route leaks to the next request on the same thread
public class BadInterceptor implements HandlerInterceptor {
@Override
public boolean preHandle(HttpServletRequest req, ...) {
if (req.getMethod().equals("GET")) {
RoutingContext.forcePrimary();
}
return true;
// MISSING: afterCompletion to clear the context
}
}
// CORRECT: always clear in afterCompletion
public class GoodInterceptor implements HandlerInterceptor {
@Override
public void afterCompletion(HttpServletRequest req, ...) {
RoutingContext.clear();
}
}
What about monitoring and failover?
Monitor replication lag and have the routing fall back to the primary if a replica falls too far behind or becomes unavailable.
-- PostgreSQL: check replication lag on the replica
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
// Health check: if lag exceeds threshold, route all reads to primary
if (replicaLagMs > MAX_ACCEPTABLE_LAG_MS) {
RoutingContext.forcePrimary();
}
What should a senior engineer know?
- The datasource is chosen when the transaction begins and the connection is acquired. Wrap with
LazyConnectionDataSourceProxyso the route is resolved after the readOnly flag is set. - The default target must be PRIMARY so writes never accidentally run on a read-only replica.
- In a microservice with multiple replicas, use a load balancer (like PgBouncer or HAProxy) in front of the replicas.
- Consider using Spring's
@Transactional(readOnly = true)also for Hibernate optimization: it sets the flush mode to MANUAL, avoiding dirty checks on read-only operations. - For strong consistency, some databases (like Aurora) offer reader endpoints with session-level consistency guarantees.
What are common interview follow-up questions?
- How does this differ from database-level routing (e.g., ProxySQL)? (Application-level gives per-transaction control; proxy-level is transparent but less granular.)
- Can you use multiple replicas? (Yes, use a load-balanced replica datasource.)
- What happens if the replica is down? (Fall back to primary; the routing datasource should handle this gracefully.)
Visual Overview
Application Layer Routing
┌──────────────────────────────────────┐
│ AccountService │
│ │
│ transfer() → @Transactional│
│ getBalance() → readOnly=true │
│ getBalanceAfterTx() → @Transactional│
└──────────┬───────────────────────────┘
│
▼
┌──────────────────────────────┐
│ ReadWriteRoutingDataSource │
│ determineCurrentLookupKey() │
│ │
│ readOnly=true → REPLICA │
│ readOnly=false → PRIMARY │
└──────┬───────────────┬───────┘
│ │
▼ ▼
┌────────────┐ ┌─────────────┐
│ Primary │ │ Replica │
│ (writes) │ │ (reads) │
└────────────┘ └─────────────┘
│ ▲
│ async repl. │
└───────────────┘
Key Takeaway
Read/write splitting routes write operations to the primary database and read operations to a replica using a single logical datasource that dynamically chooses the physical connection. In Spring, the tool is AbstractRoutingDataSource, which holds a map of targets – PRIMARY and REPLICA – and overrides determineCurrentLookupKey to pick one based on the transaction's readOnly flag.
The cleanest trigger is @Transactional(readOnly = true). Spring exposes whether the current transaction is read-only through TransactionSynchronizationManager, so the routing key returns REPLICA for read-only transactions and PRIMARY otherwise. Wrapping the routing datasource in a LazyConnectionDataSourceProxy ensures the connection is acquired after the readOnly flag is set.
The critical watch-out is replication lag causing stale reads. Because replicas apply changes asynchronously, a read sent to a replica right after a write may miss that write. At the National Bank of India, Rajesh routes must-be-fresh reads (balance shown immediately after a transfer, confirmation screens) back to the primary by not marking them readOnly. Pankaj monitors replication lag and has the routing fall back to the primary if a replica drifts too far behind.
Remember one sentence: mark reads as readOnly so a routing datasource sends them to a replica, but send must-be-fresh reads back to the primary because of replication lag.
---