When autocommit is disabled (for example, via SET autocommit=0), the database proxy routes all queries — including reads — through the primary instance, concentrating load there. Transaction splitting offloads read requests that occur before the first write in a transaction to read-only instances automatically, without requiring application code or configuration changes.
Prerequisites
Before you begin, ensure that you have:
The database proxy feature enabled on your ApsaraDB RDS for MySQL instance. For details, see Enable the database proxy feature
How it works
By default, the database proxy sends all requests in a transaction to the primary instance to preserve accuracy. When autocommit is disabled, every query runs inside an implicit transaction, which concentrates all load on the primary instance.
Transaction splitting changes this behavior: the proxy starts a transaction on the primary instance only when a write operation occurs. All read requests before the first write are routed to read-only instances through a load balancer. If the default isolation level READ COMMITTED is used and the autocommit mode of transactions is disabled, the system starts a transaction only for write operations.
Support for explicit transactions (started with BEGIN TRANSACTION or START TRANSACTION) depends on when your instance was created:
Created on or after July 31, 2024: both explicit and implicit transactions can be split.
Created before July 31, 2024: only implicit transactions can be split.
Routing rules
Routed to read-only instances:
SELECT statements that occur before the first write in a transaction
Routed to the primary instance:
The first write request in a transaction (this is when the transaction starts on the primary)
All subsequent requests in an open write transaction (SELECT, UPDATE, INSERT, DELETE, COMMIT)
The following table shows the routing sequence for a typical transaction with autocommit disabled and an implicitly started transaction:
| SQL statement | Routed to | Remarks |
|---|---|---|
| SELECT | Read-only RDS instance | Read request before the first write |
| SELECT | Read-only RDS instance | Read request before the first write |
| UPDATE, INSERT, or DELETE | Primary RDS instance | First write — transaction starts here |
| SELECT | Primary RDS instance | Read request after the first write |
| UPDATE, INSERT, or DELETE | Primary RDS instance | Write request |
| SELECT | Primary RDS instance | Read request after the first write |
| COMMIT | Primary RDS instance | Transaction commit |
Enable or disable transaction splitting
Transaction splitting does not guarantee global consistency. Verify that your workload can tolerate this trade-off before enabling the feature.
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 proxy endpoint to modify and click Modify Configuration in the Actions column.
In the dialog box, click Enable or Disable next to Transaction Splitting.
The new setting applies only to new connections.
API reference
| Operation | Description |
|---|---|
| DescribeDBProxy | Queries database proxy details |
| DescribeDBProxyEndpoint | Queries database proxy endpoint information |
| ModifyDBProxyEndpoint | Modifies database proxy endpoint settings |