Enterprise applications often require the separation of database workloads to improve performance and scalability. Read operations can be directed to replica databases while a primary database handles write operations. This article presents an implementation of read-write and read-only transaction routing using the Spring Framework. The configuration demonstrates how to automatically route transactions based on their read-only attribute.
1. Architecture Overview
The solution uses two data sources. One data source represents the primary database for write operations. The other data source represents a replica database for read operations. A routing data source determines which data source to use at runtime based on the transaction context. The Spring transaction manager provides the read-only flag that drives this routing logic.
2. Project Dependencies
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
The configuration includes Spring Data JPA for persistence and transaction management. The H2 database is used for demonstration.
3. Application Configuration
We define two separate datasources: one for writing and one for reading.
spring:
datasource:
write:
url: jdbc:h2:mem:write-db;DB_CLOSE_DELAY=-1
username: sa
password:
driver-class-name: org.h2.Driver
hikari:
pool-name: Write DB Hikari Pool
maximum-pool-size: 10
minimum-idle: 2
read:
url: jdbc:h2:mem:read-db;DB_CLOSE_DELAY=-1
username: sa
password:
driver-class-name: org.h2.Driver
hikari:
pool-name: Read-Only DB Hikari Pool
read-only: true
connection-timeout: 30000
maximum-pool-size: 10
minimum-idle: 2
data-source-properties:
cachePrepStmts: true
prepStmtCacheSize: 250
prepStmtCacheSqlLimit: 2048
useServerPrepStmts: true
jpa:
hibernate:
ddl-auto: update
show-sql: true
logging:
level:
com.zaxxer.hikari: DEBUG
com.zaxxer.hikari.HikariConfig: DEBUG
com.zaxxer.hikari.HikariDataSource: DEBUG
The configuration defines two separate data sources. The read-only data source is optimized with prepared statement caching to improve performance. Logging is enabled to expose detailed connection pool behavior and configuration during startup and runtime.
DataSource Type
We define an enum to represent routing keys.
public enum DataSourceType {
READ_ONLY,
READ_WRITE
}
This helps decide whether to route a query to the replica or the primary database.
4. Routing Data Source
This class contains the core routing logic.
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
boolean readOnly = TransactionSynchronizationManager
.isCurrentTransactionReadOnly();
return readOnly ? DataSourceType.READ_ONLY : DataSourceType.READ_WRITE;
}
}
This is the core of the routing logic, where Spring inspects the current transaction and determines whether it is marked as read-only; if it is, the request is routed to the replica database, otherwise it is directed to the primary database. Since this decision is made before a database connection is acquired, having the correct configuration in place is essential to ensure the routing behaves as expected.
5. DataSource Configuration
This code binds the configuration properties to Spring beans.
@Configuration
@EnableJpaRepositories(
basePackageClasses = ProductRepository.class,
entityManagerFactoryRef = "entityManagerFactory",
transactionManagerRef = "transactionManager"
)
public class DataSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.read")
public DataSourceProperties readDataSourceProperties() {
return new DataSourceProperties();
}
@Bean
@Primary
@ConfigurationProperties(prefix = "spring.datasource.write")
public DataSourceProperties writeDataSourceProperties() {
return new DataSourceProperties();
}
@Bean
public DataSource readDataSource() {
return readDataSourceProperties()
.initializeDataSourceBuilder()
.build();
}
@Bean
public DataSource writeDataSource() {
return writeDataSourceProperties()
.initializeDataSourceBuilder()
.build();
}
@Bean
public RoutingDataSource routingDataSource() {
RoutingDataSource routingDataSource = new RoutingDataSource();
Map<Object, Object> targets = new HashMap<>();
targets.put(DataSourceType.READ_WRITE, writeDataSource());
targets.put(DataSourceType.READ_ONLY, readDataSource());
routingDataSource.setTargetDataSources(targets);
routingDataSource.setDefaultTargetDataSource(writeDataSource());
return routingDataSource;
}
@Bean
@Primary
public DataSource dataSource() {
return new LazyConnectionDataSourceProxy(routingDataSource());
}
}
This configuration sets up the basic structure for routing by creating two separate DataSource instances. One is used for write operations and acts as the primary database, while the other is used for read operations and acts as the replica. Both data sources are registered inside a custom RoutingDataSource, which decides at runtime which database should handle the request based on the current transaction state.
To ensure this routing works correctly, the setup is wrapped with a LazyConnectionDataSourceProxy. This proxy delays the creation of the actual database connection until Spring has checked the transaction settings, especially whether it is read only or not. Without this delay, the connection could be created too early and the routing decision would not work as expected.
The @EnableJpaRepositories annotation ensures that ProductRepository is correctly bound to the custom persistence setup defined in the application. It specifies the repository package to scan and explicitly connects it to the custom EntityManagerFactory and TransactionManager beans. This guarantees that repository operations use the routing DataSource instead of any default Spring Boot configuration, allowing read and write decisions to be driven by transaction metadata.
Transaction Manager Configuration
@Configuration
@EnableTransactionManagement
public class DataSourceConfig {
@Bean
public PlatformTransactionManager transactionManager(LocalContainerEntityManagerFactoryBean emf) {
return new JpaTransactionManager(
Objects.requireNonNull(emf.getObject()));
}
}
The transaction manager is responsible for managing database transactions across both the primary and replica data sources. In this setup, we use JpaTransactionManager, which works with the EntityManagerFactory to ensure that Spring correctly handles commit and rollback operations.
EntityManagerFactory
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
EntityManagerFactoryBuilder builder) {
return builder
.dataSource(dataSource())
.packages(ProductRepository.class)
.build();
}
This configuration defines the Entity Manager Factory used by Hibernate to interact with the persistence layer. It is explicitly wired to use the routing DataSource so that every database operation is resolved dynamically at runtime.
By associating the factory with the routing DataSource rather than a single physical database connection, the application ensures that transaction metadata determines whether operations are directed to the read or write database.
Entity and Repository
@Entity
@Table(name = "product")
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private double price;
public Product() {}
public Product(String name, double price) {
this.name = name;
this.price = price;
}
// getters and setters
}
public interface ProductRepository extends JpaRepository<Product, Long> {
}
The entity represents the database table, and the repository provides CRUD operations automatically.
Service Layer
The following code shows how transaction routing operates during application execution.
@Service
public class ProductService {
private final ProductRepository repository;
public ProductService(ProductRepository repository) {
this.repository = repository;
}
@Transactional
public Product save(Product product) {
return repository.save(product);
}
@Transactional
public List<Product> findAllWrite() {
return repository.findAll();
}
@Transactional(readOnly = true)
public List<Product> findAllRead() {
return repository.findAll();
}
}
This is where routing decisions happen, as Spring checks the transaction settings to decide which database to use. When a transaction is marked as readOnly=true it goes to the replica database, and when it is not, it goes to the primary database, allowing Spring to automatically route queries based on the type of operation.
6. Running the Application
We use a CommandLineRunner to execute operations automatically when the application starts.
@SpringBootApplication
public class SpringTransactionRoutingDemoApplication implements CommandLineRunner {
@Autowired
private ProductService service;
public static void main(String[] args) {
SpringApplication.run(SpringTransactionRoutingDemoApplication.class, args);
}
@Override
public void run(String... args) {
IO.println("=== WRITE TRANSACTION ===");
Product saved = service.save(new Product("Phone", 500));
service.findAllWrite().forEach(p
-> IO.println("WRITE DB -> " + p.getName())
);
IO.println("=== READ TRANSACTION ===");
service.findAllRead().forEach(p
-> IO.println("READ DB -> " + p.getName())
);
}
}
When the application starts, the CommandLineRunner runs automatically and executes a few operations using the ProductService. First, it saves a new product, for example "Phone", 500, using a normal transactional method. Since this is a write operation, it is routed to the primary write database. After that, it calls findAllWrite(), which also runs as a write transaction and retrieves all products from the primary database, including the one that was just saved.
Next, the application calls findAllRead(), which is marked as readOnly=true, so it is routed to the replica read database. This method attempts to fetch products from the replica, which helps demonstrate how read-only transactions are separated from write operations and handled by a different data source.
Example Logs
With SQL logging enabled, you will see output similar to this:
=== WRITE TRANSACTION === Hibernate: insert into product (name,price,id) values (?,?,default) Hibernate: select p1_0.id,p1_0.name,p1_0.price from product p1_0 WRITE DB -> Phone === READ TRANSACTION === Hibernate: select p1_0.id,p1_0.name,p1_0.price from product p1_0
The insert query occurs when a new product is saved and it is executed on the primary database because it modifies data. The select query under the write transaction is triggered by findAllWrite() and it also runs on the primary database since it is not marked as read-only.
The select query under the read transaction is triggered by findAllRead() and it runs on the replica database because the transaction is marked as read-only. This shows how the same SQL query can be executed on different databases depending on the transaction type.
A key point to understand is that even though the SQL statements look identical, they are executed on different databases based on routing rules. Write operations always go to the primary database, while read-only operations go to the replica database. In real-world systems, the replica may not immediately reflect recent changes from the primary database due to replication delay, which is a normal behavior in distributed database setups.
7. Conclusion
In this article, we built a read–write routing system using Spring Boot, JPA, and AbstractRoutingDataSource. We configured separate datasources for read and write operations, used transaction metadata to drive routing decisions, and ensured proper connection handling using a lazy proxy. This pattern is widely used in scalable systems where separating read and write workloads improves performance and reliability.
8. Download the Source Code
This article explored read-write vs read-only transaction routing in Spring.
You can download the full source code of this example here: spring read write vs read only transaction routing
