Why Big Tech Switches MySQL Isolation from RR to RC – Benefits & Risks

1. Difference Between RR and RC

RC (Read Committed) allows a transaction to see only data committed by other transactions, preventing dirty reads but still allowing non‑repeatable reads and phantom reads. RR (Repeatable Read) guarantees that repeated reads within a transaction return the same result, solving non‑repeatable reads by using gap locks (Gap Lock and Next‑Key Lock), though phantom reads can still occur in special cases.

RR isolation sets gap locks to reduce phantom reads, but certain scenarios may still produce them.

Data Visibility : RC may experience non‑repeatable reads; RR eliminates them.

Lock Mechanism : RC uses only row locks; RR combines row, gap, and next‑key locks.

Phantom Reads : RC can have phantom reads; RR tries to prevent them, though not always.

Performance : RC offers higher concurrency with fewer lock conflicts; RR provides stronger consistency at the cost of higher lock overhead.

Typical Scenarios : RC suits high‑throughput workloads like e‑commerce flash sales; RR fits core business systems such as finance and inventory.

2. MySQL Master‑Slave Replication

Replication works in three main steps:

The primary server writes all data‑changing SQL statements to the binary log (binlog).

The replica reads the binlog from the primary and stores the statements in its relay log.

The replica’s SQL thread replays the relay log, applying the statements to its own data.

The process can be visualized as follows:

Detailed steps (five stages):

The primary records update, insert, delete statements into the binlog.

The replica connects to the primary.

The primary creates a binlog dump thread to stream the binlog.

The replica’s I/O thread writes the received binlog into the relay log.

The replica’s SQL thread reads the relay log from the ExecMasterLog_Pos position and executes the events on the replica.

3. Three Binlog Formats

3.1 Statement Format

Each data‑changing SQL statement is recorded in the binlog.

Advantages : Smaller binlog size, lower I/O, higher performance.

Disadvantages : Requires additional context information to guarantee identical execution on the replica, especially for non‑deterministic statements.

3.2 Row Format

Only the row changes are stored, not the original SQL.

Advantages : Captures exact row modifications, avoids issues with stored procedures, functions, or triggers.

Disadvantages : Can generate large binlog files.

3.3 Mixed Format

A hybrid approach: most statements use the statement format, but when a statement cannot be safely replicated, MySQL falls back to row format.

4. Why MySQL’s Default Isolation Level Is RR

In early MySQL versions only the statement binlog format existed. Using RC could lead to data inconsistency between master and replica because the binlog only stored the SQL text.

-- Create test table
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance INT
);

-- Insert initial data
INSERT INTO accounts (id, balance) VALUES
(1, 20),
(2, 10);

Consider two concurrent transactions under RC:

Time  | Transaction A          | Transaction B
------------------------------------------------
1     | BEGIN;                 | 
2     | UPDATE accounts SET id=3 WHERE balance=20; |
3     |                        | BEGIN;
4     |                        | UPDATE accounts SET balance=20 WHERE balance=10;
5     |                        | COMMIT;
6     | COMMIT;                |

On the primary, the final state becomes (3,20) and (2,20). On the replica, because the binlog is statement‑based, the second transaction is replayed first, leading to (1,20) and (2,20), then the first transaction overwrites to (3,20) and (3,20), causing master‑slave divergence.

Thus, RC can produce inconsistent data between master and slave.

RR solves this by adding gap locks, which block conflicting transactions until the first transaction commits, ensuring consistency at the cost of higher lock contention.

5. Why Internet Companies Prefer RC

5.1 Improving Concurrency

RC does not use gap or next‑key locks; it only locks the rows being modified. This reduces lock overhead and dead‑lock probability, allowing higher throughput in high‑concurrency environments such as large‑scale e‑commerce platforms.

5.2 Caveats When Switching to RC

Switching to RC re‑introduces phantom‑read risks, which may be acceptable in many workloads but must be understood. Additionally, the binlog format must be set to ROW or MIXED (since MySQL 5.1) because statement‑based logging can’t reliably replicate under RC.