How to Tackle MySQL Master‑Slave Replication Lag and Prevent Dirty Reads

Master‑Slave Replication Lag

During high‑traffic events like ticket sales, users often see "sold out" even though seats appear available, a problem caused by database master‑slave replication lag.

1. What It Is: Lag and Dirty Data

Modern internet applications use read‑write separation: writes go to the master , while most reads are routed to slaves to handle high concurrency.

Write operation : all data modifications (orders, payments) are performed on the master database.

Read operation : the majority of queries (e.g., checking remaining tickets) are sent to slave databases.

The master’s changes are propagated to slaves via a replication mechanism. Because this process is not instantaneous, a time gap—known as master‑slave replication lag —appears.

When lag occurs, the master may have updated data (e.g., ticket inventory reduced to 1), but the slave still shows the old value (e.g., 2). This results in dirty reads of stale data.

2. Why It Happens: The Replication Pipeline

MySQL’s default replication is asynchronous. The data flow consists of three steps:

Master writes changes to the binary log (binlog).

Slave I/O thread connects to the master, pulls the binlog, and writes it to its relay log.

Slave SQL thread reads the relay log and replays the SQL statements, applying the changes.

Any network hiccup, high slave load, or large transaction can delay one of these steps, creating the lag.

3. How to Mitigate: Three Main Approaches

Semi‑Synchronous Replication

This compromise requires the master to wait until at least one slave acknowledges receipt of the binlog before confirming the write to the client.

Pros : greatly improves data consistency and reduces the risk of data loss if the master fails.

Cons : adds write latency because the master must wait for the slave’s acknowledgment.

Force Reads from Master

For operations that demand strong consistency (e.g., financial balance checks, inventory verification before order), route the read request directly to the master, sacrificing some read‑write separation performance.

Applicable scenarios : high‑value transactions where stale data is unacceptable.

Implementation : use middleware or routing logic in code to tag specific reads as “read‑from‑master”.

Cache‑Based Solutions (Delay Double Delete)

When a cache (e.g., Redis) is used, replication lag can pollute the cache with stale data. The classic “delay double delete” pattern solves this:

Update the master.

Delete the related cache entry.

After a delay longer than the average replication lag, delete the cache entry again.

The second deletion acts like a timed bomb that removes any stale data written to the cache during the lag window.

4. Pros, Cons and Scenario Analysis

There is no silver bullet; each solution involves trade‑offs between consistency, performance impact, and implementation complexity. Choose the method that best fits your workload’s tolerance for stale data.

5. Summary: High‑Scoring Interview Answer

Discuss scenarios first—no single solution fits all cases.

For strong consistency (e.g., financial transactions), use forced reads from the master.

For read‑heavy, write‑light workloads, combine read‑write separation with caching and apply the delay‑double‑delete strategy as a safety net.

At the database layer, enable semi‑synchronous replication to improve overall reliability.

Ultimately, the goal is to balance constraints and select the most appropriate approach.