Why Your Read Returns Stale Data After a Write in MySQL Replication and How to Fix It

Write‑After‑Read Problem

In a typical one‑master‑two‑slave MySQL deployment, the master handles writes while slaves handle reads. When a client writes to the master and immediately reads from a slave, the read may return stale data or no data at all because the replication from master to slave has not yet completed.

MySQL Master‑Slave Replication Overview

The replication process involves four threads:

Master Client Thread : receives client requests, executes steps 1‑5, and notifies the Dump Thread after committing.

Master Dump Thread : reads the binary log (binlog) and sends the binlog data, file name, and position to the slave.

Slave IO Thread : receives the binlog data from the Dump Thread and appends it to the local relay log.

Slave SQL Thread : parses the relay log, converts it into executable SQL statements, runs them locally, and records the execution position.

By default MySQL uses asynchronous replication, which means the master does not wait for the slave to apply the changes. The path from a write on the master to the data becoming visible on a slave involves multiple network transmissions, disk I/O, and CPU work, often resulting in tens of milliseconds of lag under high concurrency.

Common Solutions to the Write‑After‑Read Issue

Force reads to go to the master.

Detect whether the master‑slave pair is lag‑free before reading.

Use GTID‑based waiting to ensure a specific transaction has been replicated.

Force Reads to the Master

This is the simplest approach: route any read that must see the latest state directly to the master, sacrificing read‑write separation and scalability. Most database middleware support this via hints. For example, Sharding‑JDBC allows the use of a Hint to force routing to the master.

Detect No Replication Lag

By querying SHOW SLAVE STATUS on a slave, you can examine fields such as Seconds_Behind_Master , Master_Log_File , Read_Master_Log_Pos , Relay_Master_Log_File , and Exec_Master_Log_Pos . If Seconds_Behind_Master is 0 or the master and relay positions match, the slave is considered up‑to‑date.

show slave status;
Master_Log_File: mysql-bin.001822
Read_Master_Log_Pos: 290072815
Seconds_Behind_Master: 2923
Relay_Master_Log_File: mysql-bin.001821
Exec_Master_Log_Pos: 256529431
...

However, this method can produce false positives (the slave reports no lag while the Dump Thread has not yet sent the latest binlog) and false negatives (the slave reports lag even though the specific transaction of interest has already been applied). To mitigate false positives, you can enable MySQL's semi‑synchronous replication mode.

Semi‑Synchronous Replication

In semi‑sync mode, after the master commits a transaction, the Dump Thread sends the binlog to the slave, the slave's IO Thread acknowledges receipt, and only then does the master notify the client of success. This guarantees that at least one slave has received the transaction before the client proceeds.

The drawback is that semi‑sync only waits for a single slave; in a one‑master‑multiple‑slave topology the guarantee does not extend to all slaves.

GTID‑Based Waiting

GTID (Global Transaction ID) uniquely identifies each transaction. MySQL provides a command that, on a slave, blocks until the specified GTID appears in the relay log (or times out). After a transaction commits, the client receives its GTID and can issue a WAIT_FOR_EXECUTED_GTID_SET on the target slave before performing the read.

If the wait succeeds, the read is safe; if it times out, the client can fall back to reading from the master or try another slave.

MariaDB's MaxScale implements this strategy. MaxScale is a proxy that can route queries based on rules and includes functions such as add_prefix_wait_gtid in its source files ( readwritesplit.hh and rwsplit_causal_reads.cc) to prepend the GTID‑wait command to reads.

Example transformation:

If WAIT_FOR_EXECUTED_GTID_SET fails, the original SQL is not executed on the slave; instead, it is sent to the master.

References

https://time.geekbang.org/column/article/77636

https://www.cnblogs.com/rickiyang/p/13856388.html

https://www.cnblogs.com/paul8339/p/7615310.html

https://github.com/mariadb-corporation/MaxScale