Read/Write Splitting Router Lab (Interactive)
Route SQL between primary and replicas; break read-your-writes with lag, then fix it with pinning and transactions. Execute writes and reads against a lagging replica fleet in ProxySQL or application-level mode and count stale results served.
Read/Write Splitting Router Lab
Route SELECTs to replicas and writes to the Primary; test transaction pinning and replication-lag stickiness.
status = PENDING
uncommitted state invisible to replicas
status = PENDING
serves load-balanced SELECT traffic
status = PENDING
serves load-balanced SELECT traffic
Reads on Primary
0 (0%)
Reads offloaded
0
Stale reads served
0
Writes
0
Experiment: turn lag pinning OFF, write, then immediately read — the router hands you the pre-write replica value. BEGIN a transaction and watch every read, even SELECTs, get pinned to the Primary because replicas cannot observe uncommitted MVCC state. Proxy mode adds ~1ms SQL-parsing hop but gains automatic replica health rerouting.
How It Works Under the Hood
Read-write splitting sends mutations to the primary and SELECTs to load-balanced replicas, either through dual DataSources in the ORM or a wire-protocol proxy parsing each statement. Async replication lag opens a window where a replica cannot show the just-committed row, and any SELECT inside an open transaction must stay on the primary because replicas never observe uncommitted MVCC state. Sticky sessions pin recent writers to the primary; GTID or LSN waits verify replica position before serving.
Core Architectural Principles
- Reads inside BEGIN...COMMIT are pinned to the Primary to preserve transaction isolation.
- Sticky lag-pinning routes a user’s reads to the Primary for the configured window after their write.
- Proxy mode costs about one millisecond per hop but adds automatic replica health rerouting.
Always pair splitting with its two traps: transaction pinning and read-your-own-writes. Say: "Any SELECT in a transaction goes to the primary; after a user write I pin their reads to the primary for a few seconds or wait on the LSN." Compare app-level routing, zero hops but per-language code, versus ProxySQL, universal but one extra hop.
Linear read scale and primary protection versus stale-read consistency work that must be engineered per request.