Why Big Tech Chooses RC Over MySQL’s Default RR Isolation Level

1. Difference Between RC and RR

MySQL supports four isolation levels; the two most relevant for replication are:

RC (Read Committed) : a transaction sees only rows committed by other transactions. It prevents dirty reads but allows non‑repeatable reads and phantom reads.

RR (Repeatable Read) : a transaction sees a consistent snapshot for its whole lifetime, eliminating non‑repeatable reads. Phantom reads are still possible, but MySQL mitigates them with gap and next‑key locks.

Key practical differences:

Data visibility : RC may see non‑repeatable reads; RR guarantees repeatable reads.

Locking : RC uses only row‑level locks; RR adds gap and next‑key locks, increasing lock overhead.

Phantom reads : Possible under RC; largely reduced under RR.

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

Typical workloads : RC is common in high‑throughput e‑commerce flash‑sale scenarios; RR is preferred for financial, inventory‑critical systems where consistency is paramount.

2. MySQL Master‑Slave Replication

Replication proceeds in three logical stages:

The primary (master) writes all DML statements to the binary log (binlog).

The replica’s I/O thread streams the binlog from the primary into a local relay log.

The replica’s SQL thread reads the relay log and replays the events, applying the changes to the replica’s data.

A more detailed five‑step breakdown is:

Primary records updates in the binlog.

Replica establishes a connection to the primary.

Primary creates a binlog‑dump thread that streams the log.

Replica’s I/O thread writes the streamed data to its relay log.

Replica’s SQL thread reads the relay log from ExecMasterLog_Pos and executes the events.

Illustration of the process:

3. Binlog Formats

Statement‑based replication (SBR) : records the original SQL statements.

Pros: Small log volume, lower I/O overhead.

Cons: Requires additional context (e.g., default database, user variables) to guarantee identical execution on the replica.

Row‑based replication (RBR) : records the actual row changes.

Pros: Captures exact data modifications, avoiding many edge‑case inconsistencies.

Cons: Can generate large logs because every changed row is logged.

Mixed (MBR) : MySQL chooses SBR or RBR per statement; simple statements use SBR, while statements that are unsafe for SBR (e.g., those involving non‑deterministic functions) use RBR.

4. Why MySQL’s Default Isolation Is RR

Early MySQL versions only supported statement‑based binlogs. Using RC with SBR could lead to data inconsistency on replicas because phantom reads might not be reproduced correctly. To guarantee consistency, MySQL set the default isolation level to REPEATABLE READ, which adds gap locks to prevent such anomalies.

Example demonstrating inconsistency under RC with statement‑based logging:

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

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

Two concurrent transactions executed under RC:

Transaction A: BEGIN; UPDATE accounts SET id=3 WHERE balance=20; COMMIT; Transaction B:

BEGIN; UPDATE accounts SET balance=20 WHERE balance=10; COMMIT;

On the primary, the final rows are (3, 20) and (2, 20). Because the binlog is statement‑based, the replica may apply Transaction B first and then Transaction A, resulting in rows (1, 20) and (2, 20) after B and finally (3, 20) and (3, 20) after A – a mismatch with the primary.

RR solves this by acquiring gap locks during Transaction A, causing Transaction B to wait until the lock is released, thus preserving consistency.

5. Why Large‑Scale Internet Companies Prefer RC

Switching to RC can improve throughput in high‑concurrency environments:

RC does not acquire gap or next‑key locks; it only locks rows that are being modified, reducing lock contention and dead‑lock probability.

RR’s additional locks increase the chance of deadlocks and raise latency under heavy load.

When adopting RC, two main issues must be addressed:

Phantom reads : RC does not prevent them. In many workloads the impact is acceptable or can be mitigated with application‑level logic (e.g., explicit range checks or version columns).

Binlog format : RC must be used with ROW or MIXED binlog formats; statement‑based logging is unsafe because it cannot reliably reproduce RC semantics on replicas. MySQL 5.1+ supports ROW and MIXED formats.

In summary, RC offers better performance for high‑concurrency services at the cost of weaker isolation guarantees, a trade‑off that many internet companies consider acceptable.