skip to content

What does readOnly=true mean at the JDBC/connection level, and how is it used for replica routing?

level: seniorimportance: should knowfreq 40%

answer

  1. Connection.setReadOnly(true) = driver hint
  2. AbstractRoutingDataSource + isCurrentTransactionReadOnly()
  3. LazyConnectionDataSourceProxy so flag is known first
  4. Replica lag => read-your-writes risk
  5. DB may enforce on true read-only endpoint

basics

~20 s

At the JDBC level Spring may call Connection.setReadOnly(true). That is a driver hint some databases optimize and, more usefully, apps use it to route the connection to a read replica while write transactions go to the primary.

solid answer

~40 s

Beyond the Hibernate flush change, a readOnly transaction can affect the JDBC connection: Spring's transaction manager may invoke Connection.setReadOnly(true). By the JDBC spec this is a hint enabling driver optimizations; behavior is database-specific — many drivers ignore it, some optimize, and on a genuine read-only endpoint (a replica or hot standby) a write will actually fail. The more valuable use is replica routing: with an AbstractRoutingDataSource you inspect TransactionSynchronizationManager.isCurrentTransactionReadOnly() at connection-acquire time and return a replica DataSource for read-only transactions and the primary for read-write ones. That gives read/write splitting driven purely by the readOnly flag. Caveat: the routing decision must be made before/at connection acquisition, and lazy connection acquisition (LazyConnectionDataSourceProxy) is often needed so the readOnly flag is known before the physical connection is grabbed.

code

java · 17 lines
java
public class ReplicaRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
                ? "replica" : "primary";
    }
}

@Bean
DataSource dataSource(DataSource primary, DataSource replica) {
    ReplicaRoutingDataSource routing = new ReplicaRoutingDataSource();
    routing.setTargetDataSources(Map.of("primary", primary, "replica", replica));
    routing.setDefaultTargetDataSource(primary);
    // Wrap so the physical connection (and routing decision) is deferred
    // until the readOnly flag is established on the transaction.
    return new LazyConnectionDataSourceProxy(routing);
}

go deeper

for a junior

Aware Spring may call Connection.setReadOnly(true) as a hint.

for a middle

Knows setReadOnly is driver-specific and mostly advisory.

for a senior

Can design read/write splitting with AbstractRoutingDataSource + the lazy proxy.

for a principal

Weighs replication lag, consistency, and where enforcement truly belongs.

## The JDBC hint `java.sql.Connection.setReadOnly(boolean)` is defined by the JDBC spec as *a hint to the driver to enable database optimizations*. Spring's `DataSourceTransactionManager` (and the JDBC portion of `JpaTransactionManager`, via the dialect) calls `connection.setReadOnly(true)` when the transaction is read-only, then restores it afterward. Consequences are entirely database/driver dependent: - Many drivers do little or nothing. - Some enable buffer/plan optimizations. - On a true read-only endpoint (Postgres hot standby, a read replica, or a driver in read-only mode) a write may raise a SQL error — this is the DB enforcing, not Spring. ## Replica routing (read/write splitting) — the real payoff The readOnly flag is the standard signal for routing reads to replicas: 1. Extend `AbstractRoutingDataSource` and implement `determineCurrentLookupKey()` to return `"replica"` when `TransactionSynchronizationManager.isCurrentTransactionReadOnly()` is true, else `"primary"`. 2. Register the primary and replica `DataSource`s under those keys. 3. Now any `@Transactional(readOnly = true)` method transparently uses the replica; write transactions use the primary. ## The lazy-connection caveat `AbstractRoutingDataSource` chooses the target when a connection is requested. If a connection is acquired *before* Spring has set up the transaction's read-only synchronization, the routing key may be wrong. The common fix is `LazyConnectionDataSourceProxy`: it defers obtaining the physical connection until the first statement, by which time the transaction (and its readOnly flag) is fully established, so routing sees the correct value. ## Consistency gotchas with replicas Replicas are usually asynchronously replicated, so a read-only transaction routed to a replica may not see writes committed moments earlier on the primary (replication lag / read-your-writes violation). Design read paths to tolerate this or route consistency-critical reads to the primary. ## Summary - `readOnly` at JDBC = advisory `setReadOnly(true)`, DB-specific effect. - Its most valuable role is as the routing signal for read/write splitting via `AbstractRoutingDataSource`, typically paired with `LazyConnectionDataSourceProxy`.

  • Why is LazyConnectionDataSourceProxy usually needed with routing?
    AbstractRoutingDataSource picks the target when a connection is acquired. Without laziness the connection can be grabbed before the tx's readOnly flag is set, routing to the wrong DataSource. Lazy acquisition defers it until the flag is known.
  • Is Connection.setReadOnly(true) a guarantee that writes fail?
    No. Per the JDBC spec it is only a hint. Whether a write actually fails depends on the driver and database (e.g., it can fail on a Postgres hot standby), so you cannot rely on it as enforcement.

saying these in an interview costs you the question

  • Believing setReadOnly(true) always blocks writes
  • Routing without lazy connections and expecting correct replica selection
  • Ignoring replication lag / read-your-writes on replicas
  • Thinking Spring itself routes to replicas without any configuration

context

resolving links…