When read/write splitting is enabled, read requests are distributed to read-only instances that replicate data asynchronously from the primary instance. This means a read immediately after a write can return stale data. Consistency levels control how the database proxy balances read freshness against performance.
ApsaraDB RDS for MySQL supports three consistency levels for database proxy endpoints in read/write splitting mode: eventual consistency, session consistency, and global consistency.
The default consistency level is eventual consistency. If your application requires read-after-write guarantees, explicitly set a higher consistency level.
How it works
The database proxy uses binary log (binlog) replication to keep read-only instances in sync with the primary instance. Each committed transaction generates a binlog event that read-only instances apply. The data position on each read-only instance is tracked using a global transaction identifier (GTID) — specifically the Executed_Gtid_Set variable, which records the set of transactions applied on that instance.
For session consistency and global consistency, the proxy uses GTID tracking to route reads only to read-only instances that have already applied the required transactions. If no read-only instance meets the GTID requirement within the read consistency timeout period (default: 10 ms), the request falls back to the primary instance.
Consistency levels
Eventual consistency (default)
Read requests go directly to read-only instances if binary log replication is uninterrupted. Because binlog replication is asynchronous, a read-only instance may not yet reflect the latest writes from the primary instance.
Example: Given the following statements in a session:
SET AUTOCOMMIT = 1;
INSERT INTO t1(id, price) VALUES (111, 96);
UPDATE t1 SET price = 100 WHERE id = 111;
SELECT price FROM t1;The SELECT may return 96 instead of 100 if replication lag means the update is not yet visible on the read-only instance. The query does not wait.
When to use: Your workload is insensitive to read-after-write consistency, and maximizing read/write splitting effectiveness is the priority.
Session consistency
The database proxy records the GTID of the most recently committed transaction in the current session. Before routing a read, it checks that the target read-only instance has applied transactions up to that GTID. This ensures that within a session, reads always reflect the session's own writes.
Example: Given the same statements above, the SELECT waits until the read-only instance applies the UPDATE, then returns 100.
When to use: Consistency dependencies exist within a session. This level has a moderate performance impact and suits most production workloads.
Global consistency
The database proxy records the GTID of the most recently committed transaction across the entire cluster before each read. Reads are only routed to read-only instances that have applied all transactions up to that point — ensuring every session sees a consistent view of the database.
Example: Every SELECT waits for the required data changes to be visible on the read-only instance before executing. Wait time varies with replication lag.
If replication falls behind, more reads are redirected to the primary instance, which increases primary load and latency.
When to use: Consistency dependencies exist across sessions, and your workload has significantly more reads than writes.
Choose a consistency level
For most workloads, use session consistency. It provides within-session read-after-write consistency with minimal overhead.
| Consistency level | Read/write splitting performance impact | Consistency strength | Consistency scope |
|---|---|---|---|
| Eventual consistency | None | Low | Final result consistency |
| Session consistency | Medium | Medium | Within a session |
| Global consistency | High | High | Across all sessions |
Use global consistency only if your application requires cross-session read-after-write guarantees and has significantly more reads than writes. Global consistency adds latency to every read operation.
Prerequisites
Before you begin, make sure that:
The database proxy is enabled and running version 2.25.1 or later. See Enable the database proxy feature and Release notes for the database proxy version.
The database proxy endpoint attribute is set to Read/Write (Read/Write Splitting). See Configure the read and write attributes and the read weight.
The RDS instance runs MySQL 5.7 (minor engine version 20210630 or later) or MySQL 8.0 (minor engine version 20210930 or later).
Usage notes
For session consistency and global consistency, the proxy waits for read-only instances to catch up to the required GTID position before routing a read. This increases read latency. The maximum wait time equals the read consistency timeout period (default: 10 ms).
When global consistency is used and replication lag occurs, the proxy redirects more reads to the primary instance, further increasing its load and latency.
Configure the consistency level
Go to the Instances page. In the top navigation bar, select the region where your RDS instance resides, then click the instance ID.
In the left-side navigation pane, click Database Proxy.
In the Connection Information section, find the database proxy endpoint and click Modify Configuration in the Actions column.
Select a consistency level.
For eventual consistency and session consistency, you can set a latency threshold. The default is 30 seconds. If a read-only instance exceeds this threshold, the proxy stops routing reads to it.
For session consistency and global consistency, you can set a read consistency timeout period. The default is 10 ms.
Appendix: Implementation of session consistency and global consistency
To implement session consistency and global consistency, the database proxy monitors the Executed_Gtid_Set value on each read-only instance — the set of transactions that instance has applied.
Before routing each read, the proxy determines the required GTID and selects a read-only instance that has already reached that position. If no instance has caught up within the read consistency timeout period, the request falls back to the primary instance.
The required GTID differs by consistency level:
Session consistency: The GTID of the most recently committed transaction in the current session.
Global consistency: The GTID of the most recently committed transaction across the entire cluster.
API reference
| Operation | Description |
|---|---|
| DescribeDBProxy | Queries detailed database proxy settings for an RDS instance. |
| DescribeDBProxyEndpoint | Queries information about a database proxy endpoint. |
| ModifyDBProxyEndpoint | Modifies connection settings for a database proxy endpoint. |