Understanding MySQL Master‑Slave Replication and Transaction Isolation Levels

MySQL commonly uses read/write separation to improve performance: the master handles write operations (e.g., INSERT , UPDATE ) while slaves serve SELECT queries. The article begins by illustrating this architecture with a diagram.

When a write transaction succeeds on the master, the changes are first recorded in the master’s binary log (binlog) and then flushed to disk. The binlog, located under /var/lib/mysql/ (e.g., mysql-bin.00000x ), records all data‑modifying statements.

update user set age = 50 where id = 1;

The master also runs a binlog dump thread that streams new binlog events to connected slaves over a persistent TCP connection.

On the slave side, an I/O thread receives the binlog events and writes them into a relay log. A separate SQL thread then reads the relay log and replays the statements, ensuring the slave’s data eventually matches the master’s.

show full processlist;

This two‑stage approach (relay log then SQL execution) decouples the master’s write speed from the slave’s read speed, preventing the slave from being overwhelmed.

Because the replication steps (binlog dump, relay‑log write, SQL replay) take time, a lag—known as master‑slave delay—can occur. If a client reads from a slave before the delay has been resolved, it may see stale data. The delay can be inspected on the slave with:

show slave status \G

and the Seconds_Behind_Master field shows the lag in seconds.

The article then shifts to MySQL’s four transaction isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. It shows how each level influences what a concurrent transaction reads, using a concrete case where two threads operate on the same row.

begin;
update user set age = 100 where id = 1;
commit;

In Repeatable Read (the default), a transaction that reads a row will continue to see the same value for that row until it ends, even if the master has already committed a newer value that the slave later receives.

Combining replication delay with isolation levels explains why a slave may return the latest value while the master, still inside an uncommitted transaction, returns an older value.

Finally, the article summarizes the replication workflow in five steps and emphasizes that understanding both replication mechanics and isolation levels is essential for designing reliable, consistent database systems.