Why Some Companies Switch MySQL from Repeatable Read to Read Committed

Background

MySQL’s default transaction isolation level is Repeatable Read (RR) because MySQL replicates data using binary logs (binlog) and early versions only supported the STATEMENT binlog format, which records raw SQL statements. When transactions are replayed out of order on a replica, data inconsistencies can occur.

Why RR Is the Default

RR adds a record lock together with a gap lock during updates, preventing out‑of‑order execution from causing divergence between master and replica.

Detecting the Isolation Level

You can check the current isolation level with the command: select @@tx_isolation; In a real incident, a production system experienced a deadlock because the isolation level had been changed from the default RR to Read Committed (RC) .

Consistency Reads

Consistency (or snapshot) reads use a historical version of a row. Only READ COMMITTED and REPEATABLE READ provide this behavior. In RC each read creates a fresh snapshot, always returning the latest row version. In RR a snapshot is created on the first SELECT in a transaction and remains unchanged for the rest of that transaction.

Lock Mechanisms

MySQL employs three lock types:

Record Lock : locks the index record itself.

Gap Lock : locks the gap between index records.

Next‑Key Lock : a combination of record and gap locks, covering a left‑open, right‑closed range.

Record Lock locks the index record. Gap Lock locks the gap between index records. Next‑Key Lock locks both the record and the gap.

RC only acquires Record Locks, while RR also acquires Gap and Next‑Key Locks to prevent phantom reads.

Replication and Binlog Formats

MySQL supports three binlog formats: STATEMENT, ROW, and MIXED. RC requires the ROW format; if MIXED is selected, MySQL automatically switches to row‑based logging for RC transactions. RR works with all three formats.

Why Internet Companies Prefer RC

High‑traffic services need maximum concurrency. RC improves concurrency because it does not add Gap or Next‑Key Locks, reducing lock granularity. Additionally, RC supports “semi‑consistent reads,” allowing InnoDB to return the most recent committed version of a locked row, which further reduces lock contention.

Reducing Deadlocks

RR’s extra Gap and Next‑Key Locks enlarge the lock footprint, increasing the chance of deadlocks. A deadlock occurs when two transactions each hold a lock the other needs, causing them to wait indefinitely.

Deadlock: one transaction locks table A then accesses table B, while another locks table B then tries to access table A, leading to a circular wait.

Conclusion

The article outlines the differences between RR and RC in MySQL, covering lock behavior, replication requirements, consistency reads, and practical reasons why many large‑scale internet companies switch to RC to achieve higher concurrency and fewer deadlocks, while acknowledging the need to handle phantom reads and binlog format constraints.