How to Implement Read/Write Splitting in Spring Data JPA

Problem statement: SELECT queries are all hitting the primary database despite read replicas being available, because Spring Data JPA has no built-in mechanism to route readOnly transactions to replica endpoints — every query uses whichever DataSource the EntityManagerFactory was given at startup.

Symptom Identification

Watch for these signals in production before concluding that read traffic is misrouted:

  • pg_stat_activity on the primary shows a high ratio of SELECT statements against write-heavy workloads: SELECT query, count(*) FROM pg_stat_activity WHERE state = 'active' GROUP BY query ORDER BY count(*) DESC;
  • HikariCP pool metrics in Micrometer: hikaricp_connections_active{pool="writer"} stays pegged near maximum-pool-size while hikaricp_connections_active{pool="reader"} sits at zero.
  • Actuator health endpoint reports db.status=UP but response times degrade under read load: curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.active | jq '.measurements'
  • Primary CPU and IOPS spike during read-heavy operations (reporting dashboards, search, analytics) — replicas show flat utilisation.
  • Spring Boot startup logs contain only one HikariPool entry instead of two named pools (writer, reader).

Root Cause Analysis

Spring Data JPA selects a database connection exactly once per transaction, at the moment @Transactional opens. The EntityManagerFactory holds a single DataSource reference injected at application context startup. Without intervention, every transaction — read-only or not — draws from that one DataSource.

The readOnly = true flag on @Transactional tells Hibernate to skip dirty checking and sets Connection.setReadOnly(true) as a driver hint. It does not switch the DataSource. To change which database endpoint receives the connection, you must intercept the transaction before it opens and redirect it at the DataSource selection level.

Spring’s AbstractRoutingDataSource is designed for exactly this: it acts as a proxy that delegates getConnection() to one of several registered DataSource instances based on a lookup key. By storing the routing decision in a ThreadLocal and populating that ThreadLocal in an AOP aspect that runs before the transaction interceptor, you control which pool each operation uses.

The critical ordering constraint: Spring’s TransactionInterceptor has order Integer.MAX_VALUE - 1 by default. Your routing aspect must have a lower order value (higher precedence) so it fires first.

Routing Flow Diagram

Spring Data JPA Read/Write Routing Flow A sequence diagram showing a service method call passing through the RoutingAspect (which sets ThreadLocal READ or WRITE), then through the TransactionInterceptor, then through AbstractRoutingDataSource which selects either the writer HikariCP pool or the reader HikariCP pool. Service Method RoutingAspect Transaction Interceptor AbstractRoutingDataSource → writer / reader pool invoke readOnly=true? → set READ key else: WRITE key getConnection() determineCurrentLookupKey() reads ThreadLocal → picks pool writer pool reader pool WRITE READ @After → ThreadLocal.remove()

Step-by-Step Resolution

Step 1 — Configure dual HikariCP DataSource beans in application.yml

Declare separate property namespaces for writer and reader connections. Size the reader pool larger because read traffic typically outnumbers writes by 4:1 or more.

yaml
spring:
  datasource:
    writer:
      jdbc-url: jdbc:postgresql://db-primary.internal:5432/appdb
      username: ${DB_WRITER_USER}
      password: ${DB_WRITER_PASS}
      hikari:
        pool-name: writer
        maximum-pool-size: 20
        minimum-idle: 5
        connection-timeout: 3000
        validation-timeout: 2000
        idle-timeout: 300000
        max-lifetime: 1800000
        connection-test-query: SELECT 1
    reader:
      jdbc-url: jdbc:postgresql://db-replica-01.internal:5432/appdb
      username: ${DB_READER_USER}
      password: ${DB_READER_PASS}
      hikari:
        pool-name: reader
        maximum-pool-size: 40
        minimum-idle: 10
        connection-timeout: 2000
        validation-timeout: 1500
        idle-timeout: 300000
        max-lifetime: 1800000
        connection-test-query: SELECT 1

Verify: after startup, curl -s http://localhost:8080/actuator/metrics/hikaricp.connections | jq '.availableTags' should list both pool="writer" and pool="reader".

Step 2 — Implement AbstractRoutingDataSource with a ThreadLocal context holder

java
public enum RoutingKey { WRITE, READ }

public class RoutingContextHolder {
    private static final ThreadLocal<RoutingKey> CONTEXT =
        ThreadLocal.withInitial(() -> RoutingKey.WRITE);  // safe default

    public static void set(RoutingKey key) { CONTEXT.set(key); }
    public static RoutingKey get()         { return CONTEXT.get(); }
    public static void clear()             { CONTEXT.remove(); }  // prevents thread-pool leaks
}

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return RoutingContextHolder.get();
    }
}

Register the routing bean and wire it as the @Primary DataSource:

java
@Configuration
@EnableJpaRepositories(basePackages = "com.app.repository")
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.writer.hikari")
    public HikariDataSource writerDataSource(
            @Value("${spring.datasource.writer.jdbc-url}") String url,
            @Value("${spring.datasource.writer.username}") String user,
            @Value("${spring.datasource.writer.password}") String pass) {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl(url); cfg.setUsername(user); cfg.setPassword(pass);
        return new HikariDataSource(cfg);
    }

    @Bean
    @ConfigurationProperties("spring.datasource.reader.hikari")
    public HikariDataSource readerDataSource(
            @Value("${spring.datasource.reader.jdbc-url}") String url,
            @Value("${spring.datasource.reader.username}") String user,
            @Value("${spring.datasource.reader.password}") String pass) {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl(url); cfg.setUsername(user); cfg.setPassword(pass);
        return new HikariDataSource(cfg);
    }

    @Bean
    @Primary
    public DataSource routingDataSource(
            HikariDataSource writerDataSource,
            HikariDataSource readerDataSource) {
        DynamicRoutingDataSource routing = new DynamicRoutingDataSource();
        Map<Object, Object> targets = new HashMap<>();
        targets.put(RoutingKey.WRITE, writerDataSource);
        targets.put(RoutingKey.READ,  readerDataSource);
        routing.setTargetDataSources(targets);
        routing.setDefaultTargetDataSource(writerDataSource); // failsafe
        routing.afterPropertiesSet();
        return routing;
    }
}

Verify: set a breakpoint in determineCurrentLookupKey() and confirm it is called before TransactionInterceptor.invoke() appears in the call stack.

Step 3 — Add AOP interceptor to populate the routing key

The aspect must execute before Spring opens a transaction, so use @Order(Ordered.HIGHEST_PRECEDENCE).

java
@Aspect
@Component
@Order(Ordered.HIGHEST_PRECEDENCE)
public class RoutingAspect {

    @Pointcut("@annotation(org.springframework.transaction.annotation.Transactional)")
    public void transactionalMethod() {}

    @Before("transactionalMethod()")
    public void setRoutingKey(JoinPoint jp) {
        MethodSignature sig = (MethodSignature) jp.getSignature();
        Transactional tx = sig.getMethod().getAnnotation(Transactional.class);
        RoutingContextHolder.set(
            (tx != null && tx.readOnly()) ? RoutingKey.READ : RoutingKey.WRITE
        );
    }

    @After("transactionalMethod()")
    public void clearRoutingKey() {
        RoutingContextHolder.clear();
    }

    @AfterThrowing(pointcut = "transactionalMethod()", throwing = "ex")
    public void clearRoutingKeyOnException(Throwable ex) {
        RoutingContextHolder.clear();
    }
}

Verify: add a log.debug("Routing key set to {}", RoutingContextHolder.get()) in setRoutingKey and confirm READ appears before replica-bound SELECT queries in your application logs.

Step 4 — Enforce transaction boundary and propagation rules

Mark service methods that only execute SELECT queries with readOnly = true and use Propagation.REQUIRES_NEW where a read must be fully isolated from any parent write transaction:

java
@Service
public class ProductCatalogService {

    // Reads route to replica pool
    @Transactional(readOnly = true, propagation = Propagation.REQUIRES_NEW)
    public List<Product> findByCategory(String category) {
        return productRepository.findAllByCategory(category);
    }

    // Writes route to writer pool — replica key is never set
    @Transactional
    public Product save(Product product) {
        return productRepository.save(product);
    }
}

Disable open-in-view and set flush mode globally to prevent Hibernate from acquiring a write connection during read-only transactions:

yaml
spring:
  jpa:
    open-in-view: false
    properties:
      hibernate:
        flushMode: COMMIT

Verify: with open-in-view: false, confirm that lazy-loaded relations throw LazyInitializationException when accessed outside a transaction — this is correct behaviour and confirms no implicit session is leaking through to the connection layer.

Step 5 — Add a safety-net filter to prevent thread-local leaks

Tomcat and Undertow reuse threads across requests. If an exception bypasses the AOP @After advice, the ThreadLocal retains a stale routing key and the next request on that thread will route incorrectly. Register a servlet filter as the outermost cleanup:

java
@Component
@Order(Ordered.HIGHEST_PRECEDENCE - 1)
public class RoutingContextCleanupFilter extends OncePerRequestFilter {
    @Override
    protected void doFilterInternal(HttpServletRequest req,
                                    HttpServletResponse res,
                                    FilterChain chain)
            throws ServletException, IOException {
        try {
            chain.doFilter(req, res);
        } finally {
            RoutingContextHolder.clear(); // unconditional cleanup
        }
    }
}

Configuration Snippet

Complete minimal DataSourceConfig with all critical parameters annotated:

java
@Bean
@Primary
public DataSource routingDataSource(
        HikariDataSource writerDataSource,
        HikariDataSource readerDataSource) {

    DynamicRoutingDataSource routing = new DynamicRoutingDataSource();

    Map<Object, Object> targets = new HashMap<>();
    targets.put(RoutingKey.WRITE, writerDataSource);
    targets.put(RoutingKey.READ,  readerDataSource);

    routing.setTargetDataSources(targets);

    // Default is writer: ensures safe behaviour if ThreadLocal is not set
    // (e.g. background jobs that do not go through @Transactional)
    routing.setDefaultTargetDataSource(writerDataSource);

    // Must call afterPropertiesSet() explicitly when building the bean
    // programmatically — Spring does not call it for you here
    routing.afterPropertiesSet();

    return routing;
}

And the companion application.yml property for leak detection on both pools:

yaml
spring:
  datasource:
    writer:
      hikari:
        leak-detection-threshold: 30000   # log warning if connection held > 30s
    reader:
      hikari:
        leak-detection-threshold: 30000

Verification and Rollback

Verify routing is working:

sql
-- Run on the replica host; active connections from your app should appear here
SELECT client_addr, usename, application_name, state, query
FROM pg_stat_activity
WHERE datname = 'appdb'
  AND client_addr = '<app-server-ip>'
ORDER BY state;

Also check Micrometer:

code
curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.active \
  | jq '.measurements[] | select(.statistic=="VALUE")'

Both pool="writer" and pool="reader" should show non-zero active connections when the application is under mixed read/write load.

Rollback procedure if the split causes regressions:

  1. Set SPRING_PROFILES_ACTIVE=no-routing and add a profile-conditional @Bean that returns a single writer DataSource as @Primary, bypassing DynamicRoutingDataSource.
  2. Remove @EnableAspectJAutoProxy from DataSourceConfig or deactivate the aspect via profile.
  3. Restart: systemctl restart app-service or kubectl rollout restart deployment/app.
  4. Confirm with pg_stat_activity on the primary that all connections originate from the app again.

Edge Cases and Gotchas

Nested transactions with mixed propagation. If a @Transactional(readOnly = true) method calls an inner @Transactional method (without REQUIRES_NEW), Spring reuses the existing connection — the inner method inherits the READ routing key even if it performs a write. The fix: annotate any write-capable inner method with Propagation.REQUIRES_NEW. Note that REQUIRES_NEW suspends the outer transaction, so use it only where the isolation is genuinely required.

Write-then-read consistency after a write. After a save() completes, a subsequent findById() inside the same request may route to the replica and return stale data if replication lag exceeds a few milliseconds. For user-facing “read your own writes” scenarios, either force the confirmation read onto the writer by calling RoutingContextHolder.set(RoutingKey.WRITE) explicitly, or track the write timestamp and fall back to the primary when the replica’s pg_last_xact_replay_timestamp() is behind — a pattern covered in detecting and handling replication lag.

Multiple replica endpoints. AbstractRoutingDataSource maps one key to one DataSource. For round-robin across multiple replicas, wrap the reader pool registration in a LazyConnectionDataSourceProxy backed by a small load-balancer shim, or use a JDBC URL that includes the replica list (e.g. PostgreSQL’s targetServerType=preferSecondary with multiple hosts). Alternatively, push multi-replica distribution to the proxy layer as described in implementing read/write splitting at the proxy layer — the two approaches are complementary.

FAQ

Why do read queries still hit the primary after enabling AbstractRoutingDataSource?

The most common cause is aspect ordering: Spring’s transaction interceptor opens a connection before your routing aspect sets the ThreadLocal key, so the connection binds to the default (writer) DataSource. Set @Order(Ordered.HIGHEST_PRECEDENCE) on your routing aspect to ensure the key is set before any transaction begins.

Does readOnly=true in @Transactional automatically route to the replica?

No — Spring sets readOnly as a hint to the JDBC driver and disables dirty checking, but it does not change which DataSource is selected. You must explicitly inspect the readOnly flag in an AOP aspect and set RoutingKey.READ in your ThreadLocal context holder before the transaction binds a connection.

How do I prevent stale replica reads for critical writes?

Force the immediately following read onto the primary by temporarily overriding the routing key. In a service that writes then reads-to-confirm, call RoutingContextHolder.set(RoutingKey.WRITE) for the confirmation query, or use Propagation.REQUIRES_NEW so the read participates in a fresh write-bound transaction.

← Back to Implementing Read/Write Splitting at the Proxy Layer