Read-Write and Read-Only Transaction Routing With Spring

1. Introduction

In this tutorial, we’ll implement transaction-based routing with Spring, sending write operations to a primary database and read-only operations to a replica. This is a common pattern in applications that use database replication to scale read speeds.

2. Why Route Transactions?

Without routing, every query hits the same database. This might become a bottleneck as traffic grows, so replication helps by maintaining replicas that mirror the primary database. This strategy increases read speeds, but the application still needs to know where to send each query. The approach we’ll use lets the transaction metadata decide automatically:
Using Spring’s AbstractRoutingDataSource, combined with the @Transactional annotation’s readOnly flag, we intercept every connection request and direct it to the suitable database.

3. Scenario Setup

Let’s start with a simple entity and a repository for the examples. This layer doesn’t need any special configuration.

3.1. Creating the Entity

Let’s start with an Order entity with basic properties. We explicitly set the generation strategy to IDENTITY so the database’s auto-increment column provides the ID:

@Entity
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String description;

    // default getters and setters and constructors
}

3.2. Creating the Repository

Next, we’ll create a Spring Data repository:

public interface OrderRepository
  extends JpaRepository<Order, Long> {
}

We now have everything we need to implement our routing datasource.

4. Implementing the Routing DataSource

We’ll extend AbstractRoutingDataSource to decide which DataSource to use based on the current transaction.

4.1. Defining the DataSource Types

First, let’s create an enum to represent our DataSource types when wiring up our configuration:

public enum DataSourceType {
    READ_WRITE, READ_ONLY
}

We’ll use READ_WRITE as the lookup key for the primary DataSource (where all inserts, updates, and deletes go) and READ_ONLY for the replica (used exclusively for select queries).

4.2. Creating the Routing Logic

Now, we’ll extend AbstractRoutingDataSource and override determineCurrentLookupKey(). This method checks whether the current transaction is read-only via TransactionSynchronizationManager:

public class TransactionRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        boolean readOnly = TransactionSynchronizationManager
          .isCurrentTransactionReadOnly();

        if (readOnly) {
            return DataSourceType.READ_ONLY;
        }

        return DataSourceType.READ_WRITE;
    }
}

Spring calls this method when a connection is needed, so we’ll use it for routing the request to either the primary or the replica.

5. Configuring the DataSources

We need to define the properties for both data sources, then wire them as beans. Note that this setup only controls which connection is used for a given transaction. Nothing in the routing layer prevents a write from being issued to the replica connection, since this enforcement is configured at the database level.

In the real world, that means the replica user is typically granted only SELECT privileges, or the replica database itself is configured to reject writes.

5.1. Defining the Application Properties

Let’s use the “spring.datasource” prefix to define connection properties in application.properties:

spring.datasource.readwrite.url=jdbc:h2:mem:primary;DB_CLOSE_DELAY=-1
spring.datasource.readwrite.username=sa
spring.datasource.readwrite.driverClassName=org.h2.Driver

spring.datasource.readonly.url=jdbc:h2:mem:replica;DB_CLOSE_DELAY=-1
spring.datasource.readonly.username=sa
spring.datasource.readonly.driverClassName=org.h2.Driver

Each data source points to its own in-memory H2 database. We set DB_CLOSE_DELAY to -1 so these aren’t dropped when the last connection closes.

5.2. Wiring the Configuration Beans

Because we’re defining a custom DataSource bean, Spring Boot’s JPA auto-configuration isn’t used. That means OrderRepository won’t have an EntityManagerFactory or TransactionManager unless we explicitly declare them. The @EnableJpaRepositories annotation lets us point the repository scanner at the beans we’ll define in this class:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  basePackageClasses = OrderRepository.class,
  entityManagerFactoryRef = "routingEntityManagerFactory",
  transactionManagerRef = "routingTransactionManager"
)
public class DataSourceConfiguration {

    // ...
}

The basePackageClasses attribute extracts the package name from the class we provide for component scanning. So that it can scan everything we need, OrderRepository and Order must be in the same package.

We start by creating both DataSource beans from their respective properties prefixes. First, reading “spring.datasource.readwrite.*” properties:

@Bean
@ConfigurationProperties("spring.datasource.readwrite")
public DataSourceProperties readWriteProperties() {
    return new DataSourceProperties();
}

Then, reading “spring.datasource.readonly.*” properties:

@Bean 
@ConfigurationProperties("spring.datasource.readonly") 
public DataSourceProperties readOnlyProperties() { 
    return new DataSourceProperties();
}

Next, we build the actual DataSource instances. First, for the read-write source:

@Bean
public DataSource readWriteDataSource() {
    return readWriteProperties()
      .initializeDataSourceBuilder()
      .build();
}

Then, for the read-only source:

@Bean
public DataSource readOnlyDataSource() {
    return readOnlyProperties()
      .initializeDataSourceBuilder()
      .build();
}

5.3. Configuring Our TransactionRoutingDataSource

Now, we wire the routing DataSource in a TransactionRoutingDataSource bean:

@Bean
public TransactionRoutingDataSource routingDataSource() {
    TransactionRoutingDataSource routingDataSource =
      new TransactionRoutingDataSource();

    // ...

    return routingDataSource;
}

We’ll register both targets in a map, which we’ll use when calling setTargetDataSources():

Map<Object, Object> dataSourceMap = new HashMap<>();
dataSourceMap.put(DataSourceType.READ_WRITE, readWriteDataSource());
dataSourceMap.put(DataSourceType.READ_ONLY, readOnlyDataSource());

routingDataSource.setTargetDataSources(dataSourceMap);
routingDataSource.setDefaultTargetDataSource(readWriteDataSource());

We also call setDefaultTargetDataSource(), which defines the fallback to use when, for example, code runs outside a transaction.

5.4. Defining a Lazy DataSource

Since JPA acquires a connection before the transaction synchronization sets the read-only flag, we need to wrap our routing DataSource in a LazyConnectionDataSourceProxy. This way, the actual connection is deferred until the first SQL statement:

@Bean
@Primary
public DataSource dataSource() {
    return new LazyConnectionDataSourceProxy(routingDataSource());
}

We mark this bean with @Primary so that when auto-wiring a DataSource, there’s no chance the non-lazy routingDataSource() is used. Then, we configure an EntityManagerFactory to use our lazy proxy. LocalContainerEntityManagerFactoryBean is the standard choice when managing the JPA configuration manually, as it integrates with Spring’s lifecycle:

@Bean
public LocalContainerEntityManagerFactoryBean routingEntityManagerFactory(
  EntityManagerFactoryBuilder builder) {
    return builder
      .dataSource(dataSource())
      .packages(OrderRepository.class)
      .build();
}

We also need a TransactionManager wired to our factory. We return a JpaTransactionManager so that @Transactional methods propagate the transaction manager’s readOnly flag to our routing logic:

@Bean
public PlatformTransactionManager routingTransactionManager(
  LocalContainerEntityManagerFactoryBean routingEntityManagerFactory) {
    return new JpaTransactionManager(
      Objects.requireNonNull(routingEntityManagerFactory.getObject()));
}

After all this configuration, we’re ready to get to the service layer.

6. Creating the Service Layer

We’ll create a service that uses the @Transactional annotation to control routing:

@Service
public class OrderService {

    @Autowired
    OrderRepository orderRepository;

    // ...
}

Methods annotated with readOnly = true are routed to the replica, while other transactions go to the primary DataSource:

@Transactional
public Order save(Order order) {
    return orderRepository.save(order);
}

@Transactional(readOnly = true)
public List<Order> findAllReadOnly() {
    return orderRepository.findAll();
}

@Transactional
public List<Order> findAllReadWrite() {
    return orderRepository.findAll();
}

In this example, we have a find method for each DataSource. This will be useful later in our tests.

7. Setting up the Tests

In a real-life scenario, a replica continuously syncs data from the primary. But to keep tests simple, we’ll keep them unsynchronized. We’ll use this isolation to assert that routing is working correctly.

So, in our case, if a read-only query returns data written by a read-write transaction, we know it’s hitting the primary rather than the replica.

Let’s set up our test class:

@SpringBootTest
class TransactionRoutingIntegrationTest {

    @Autowired
    OrderService orderService;

    // ...
}

First, we verify that a read-write transaction stays on the primary database, so a saved order is immediately visible:

@Test
void whenSaveAndReadWithReadWrite_thenFindsOrder() {
    Order saved = orderService.save(new Order("laptop"));

    List<Order> result = orderService.findAllReadWrite();

    assertThat(result)
      .anyMatch(o -> o.getId().equals(saved.getId()));
}

Then, we confirm that a read-only transaction is routed to the replica. Because the replica is a separate, unsynchronized database, it can’t access an order saved to the primary DataSource:

@Test
void whenSaveAndReadWithReadOnly_thenOrderNotFound() {
    Order saved = orderService.save(new Order("keyboard"));

    List result = orderService.findAllReadOnly();

    assertThat(result)
      .noneMatch(o -> o.getId().equals(saved.getId()));
}

We explicitly check that findAllReadOnly() doesn’t return the saved order’s ID, verifying that this specific order wasn’t routed to the replica.

8. Considerations

Let’s go over a few practical aspects to keep in mind when using this routing pattern.

8.1. Replication Lag

There’s a delay between a write to the primary and its propagation to the replica. The duration of the delay depends on our infrastructure.

So, for time-sensitive flows (such as writing an entity and immediately reading it back), routing the read to the replica may return stale data. In cases like this, it’s safer to run both operations within a single read-write transaction.

8.2. Nested Transactions

With Spring’s default propagation, a @Transactional(readOnly = true) method called from within a @Transactional(readOnly = false) method uses the existing transaction. This means only the first transaction created takes effect, ignoring any other flags in subsequent @Transactional methods, so the query still goes to the primary database. To actually route the inner call to the replica, we’d need Propagation.REQUIRES_NEW to suspend the outer transaction and start a read-only one.

Also note that if both methods are in the same bean, Spring won’t intercept the internal call.

8.3. Multiple Read Replicas

It’s also possible to use more than one read replica to distribute traffic. One approach is to extend our routing logic to rotate between them by using a list of read-only DataSources and selecting one using a round-robin implementation in determineCurrentLookupKey(), but that’s out of scope for this article.

A more robust approach is to delegate read-only connections to a load-balanced connection URL. There are many production-ready alternatives for this, like PgBouncer or ProxySQL, depending on our infrastructure.

9. Conclusion

In this article, we implemented transaction-based DataSource routing with Spring using AbstractRoutingDataSource. We saw how to separate read-write and read-only traffic, why LazyConnectionDataSourceProxy is required for correct routing, and how to verify the behavior with integration tests.