PolarDB-X 1.0 automatically routes SQL statements across primary and read-only ApsaraDB RDS instances, transparent to the application layer. Because a millisecond-level replication lag exists between the primary and read-only instances, a query sent immediately after a write may return stale data if routed to a read-only instance. TDDL hints let you override the default routing on a per-statement basis, so you can force a specific query to the primary or a read-only instance.
This topic applies to PolarDB-X 1.0 versions 5.3 and later.
How routing works
Understanding the default routing behavior helps you decide when to use hints:
| SQL type | Default destination | Override with hint? |
|---|---|---|
INSERT, UPDATE, DELETE | Primary ApsaraDB RDS instance | No — always routes to primary |
SELECT inside a transaction | Primary ApsaraDB RDS instance | No — always routes to primary |
SELECT outside a transaction | Read-only ApsaraDB RDS instance (load-balanced) | Yes — use master() or slave() |
TDDL hints apply only to SELECT statements outside an open transaction. Write statements and any statement inside a transaction always execute on the primary instance, regardless of any hint.
Hint syntax
/*+TDDL: master() | slave() */Place the hint immediately after the first keyword in the SQL statement:
SELECT /*+TDDL:master()*/ * FROM table_name;
SELECT /*+TDDL:slave()*/ * FROM table_name;| Hint | Routes to | When to use |
|---|---|---|
master() | Primary ApsaraDB RDS instance | Read data that must reflect the latest writes |
slave() | A read-only ApsaraDB RDS instance (randomly selected by weight) | Offload read traffic from the primary instance |
Two hint formats are supported:
/*+TDDL:hint_command*/— standard MySQL comment format/!+TDDL:hint_command*/— alternative format
If you use the/*+TDDL:hint_command*/format from the mysql command-line client, add the-cflag when connecting. Without-c, the client strips comments before sending statements to the server, and the hints have no effect. For details, see mysql client options.
mysql -c -u <username> -pExamples
Force a read to the primary instance
Use master() when you need to read data immediately after a write—before replication has propagated the change to read-only instances.
INSERT INTO orders (user_id, amount) VALUES (42, 100.00);
-- Route to the primary instance to avoid replication lag
SELECT /*+TDDL:master()*/ * FROM orders WHERE user_id = 42;Without the master() hint, the SELECT would normally route to a read-only instance and might return stale data.
Route a read to a read-only instance
Use slave() to explicitly offload a query to a read-only instance.
SELECT /*+TDDL:slave()*/ * FROM products WHERE category = 'electronics';If multiple read-only instances are configured, PolarDB-X 1.0 selects one based on assigned weight. If no read-only instances are available, the query falls back to the primary instance—no error is returned.