Ensuring Read-After-Write Consistency Across Pooled Connections in PolarDB for MySQL

When building scalable applications with PolarDB for MySQL, combining a read/write-splitting cluster endpoint with a connection pool (such as HikariCP or Tomcat JDBC) is standard practice. PolarDB's built-in session consistency guarantees that a client reading after a write will always observe its own updates without lag.

However, there is a fundamental catch: database session consistency is scoped to the physical MySQL connection. If an application user commits a write via Connection A, returns it to the pool, and executes a dependent read via Connection B, PolarDB's proxy cannot natively correlate the two operations. Connection B could be routed to an asynchronous Read-Only (RO) node that is milliseconds behind, causing a stale read.

Here are the best architectural strategies to guarantee read-after-write consistency across pooled connections without pinning connections or introducing arbitrary delays.

The Core Issue: Proxy Sessions vs. Application Sessions

PolarDB tracks Log Sequence Numbers (LSNs) per client session on the cluster endpoint proxy. When Connection A commits, the proxy updates Connection A's tracked LSN. Subsequent reads on Connection A will either wait for the replica node to catch up to that LSN or route to the primary node. Because Connection B is an entirely separate database session, its tracked LSN does not reflect Connection A's commit, making it vulnerable to replica lag.

Solution 1: Route Dependent Reads to the Primary Using SQL Hints (Recommended)

The cleanest, most targeted approach is to instruct the PolarDB proxy to route only the latency-sensitive read to the primary node. This keeps all other general reads distributed across read-only nodes.

PolarDB for MySQL supports the /*FORCE_MASTER*/ hint to bypass read/write splitting for specific queries:

-- Physical connection B borrowed from pool
/*FORCE_MASTER*/ SELECT value FROM consistency_example WHERE id = 1;

Why this works best:

• Zero lag: The primary node always holds the most up-to-date committed data.
• Selective: Only queries that immediately follow writes incur load on the primary node. Unrelated background queries and reports continue to utilize read replicas.
• No schema changes: It requires minimal changes to your data access layer (e.g., using query interceptors or custom annotations in your ORM/repository).

Solution 2: Use PolarDB Global Consistency (Strict Consistency)

If you prefer an infrastructure-level guarantee without modifying SQL queries in your codebase, PolarDB provides Global Consistency across the entire cluster endpoint.

Unlike Session Consistency, Global Consistency leverages global timestamps and transactional state across all nodes. When an endpoint is configured for Global Consistency:

• The cluster endpoint ensures that read queries on read-only nodes wait until the replica node catches up to the timestamp of the latest committed global transaction.
• Connection B is guaranteed to see writes committed by Connection A, even on a read-only replica.

Cluster Endpoint Configuration:
Consistency Level -> Global Consistency (Strict Consistency)
Max Replication Delay -> e.g., 1000ms

Trade-offs: Global consistency can add slight read latency on read-only replicas when active replication catches up. If high write throughput causes replicas to lag, RO queries may experience slight throttling or fall back to the primary node.

Solution 3: Use Locking Reads (`LOCK IN SHARE MODE` or `FOR UPDATE`)

PolarDB's read/write-splitting proxy automatically routes any locking read or active transaction to the primary node. If you do not want to use proprietary hints like /*FORCE_MASTER*/, you can achieve the same behavior using standard ANSI SQL:

-- Routes automatically to the primary node
SELECT value FROM consistency_example WHERE id = 1 FOR SHARE;
-- Or:
SELECT value FROM consistency_example WHERE id = 1 LOCK IN SHARE MODE;

Caution: While this guarantees primary routing, locking reads acquire shared row locks in InnoDB, which can introduce lock contention under high concurrent load.

Solution 4: Maintain Separate Connection Pools

For large enterprise systems, standardizing access via a dual-pool pattern (CQRS pattern) is common:

• Write/Critical Pool: Connects directly to the Primary Node Endpoint (or cluster endpoint with routing forced to primary). Handlers executing creates, updates, and immediate post-write redirects use this pool.
• Read Pool: Connects to the Read/Write-splitting or Read-Only endpoint. Used for dashboards, browsing, searches, and background processes.

// Example using Spring RoutingDataSource
@Transactional(readOnly = false)
public void updateUser(User user) {
    userRepository.save(user);
    // Subsequent immediate read uses the primary datasource
    User updated = userRepository.findById(user.getId()); 
}

Summary: Which Strategy Should You Choose?

For most applications, attaching the /*FORCE_MASTER*/ hint to the single post-commit SELECT query provides the most predictable performance and guarantees instant read-after-write consistency across pooled connections.