Why Can’t You Read Your Recent Write? Understanding Master‑Slave Lag and Fixes in MySQL

In many startup‑level MySQL deployments a single master with two slaves handles writes on the master and reads on the slaves, but immediate reads after a write can return stale data because replication lag means the slave has not yet received the latest binlog.

Write‑after‑read problem

The sequence is: the client writes to the master, then immediately reads from Slave A while the master‑to‑slave synchronization is still in progress, so the read sees the old state; later, after synchronization completes, reads from Slave B return the correct state.

MySQL master‑slave replication basics

Four threads participate in replication:

Client Thread on the master processes client requests and coordinates the replication steps.

Dump Thread on the master reads the binlog and streams the log file name, position and data to the slave.

IO Thread on the slave receives the binlog stream and writes it to the local relay log.

SQL Thread on the slave reads the relay log, converts entries to executable SQL, runs them, and records the execution position.

By default MySQL uses asynchronous replication, which involves many steps (network transfer, disk I/O, CPU execution) and can introduce tens to hundreds of milliseconds of delay under high load.

Common mitigation strategies

Force reads to the master

The simplest approach is to route reads that must see the latest data directly to the master, often via a hint in middleware such as Sharding‑JDBC: Hint This adds a routing hint to the SQL so the middleware sends the query to the master, eliminating stale reads but increasing read load on the master and reducing overall scalability.

Detect replica lag before reading

Use SHOW SLAVE STATUS to inspect fields such as Seconds_Behind_Master , Master_Log_File / Read_Master_Log_Pos versus Relay_Master_Log_File / Exec_Master_Log_Pos , and Auto_Position for GTID mode. If lag is detected, wait until the replica catches up before issuing the read. show slave status However, this method can produce false positives (the slave reports no lag while the master’s Dump Thread has not yet sent the latest binlog) and false negatives (lag exists but the specific transaction has already been applied).

Semi‑synchronous replication

Switching to semi‑sync mode makes the master wait for an ACK from at least one slave before confirming the transaction, ensuring that the binlog has been received by that slave. The ACK flow is:

Master’s Dump Thread sends binlog to the slave.

Slave’s IO Thread acknowledges receipt.

Master’s Dump Thread receives the ACK and notifies the Client Thread, which then returns success to the client.

Note that semi‑sync only guarantees acknowledgment from a single slave, so in a one‑master‑multiple‑slave topology it does not guarantee that all slaves are up‑to‑date.

GTID‑based waiting (GTID scheme)

GTID (Global Transaction ID) uniquely identifies each transaction. After a write, the client can issue a WAIT_FOR_EXECUTED_GTID_SET (or similar) on the target replica, which blocks until the replica has processed the specified GTID or a timeout occurs. If the wait times out, the client can fall back to reading from the master.

MariaDB’s MaxScale implements this approach in its read‑write split plugin, using functions such as add_prefix_wait_gtid to prepend the wait command to the original query.

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