How Do RC and RR Isolation Levels Differ in MySQL Snapshot Reads?

1 Introduction

In the previous article we introduced the four isolation levels defined by the SQL‑92 standard and discussed whether dirty reads, non‑repeatable reads, and phantom reads can be prevented under each level:

Isolation Level | Dirty Read | Non‑repeatable Read | Phantom Read
Read Uncommitted | ✔ | ✔ | ×
Read Committed   | × | ✔ | ×
Repeatable Read  | × | × | ✔
Serializable     | × | × | ×

For the Read Committed (RC) and Repeatable Read (RR) isolation levels, MySQL uses the Snapshot Read mechanism to achieve high concurrency.

2 Differences in Snapshot Reads between RC and RR

2.1 What is Snapshot Read?

In InnoDB, a snapshot read creates a consistent view of the data at the start of a transaction (or at a specific point in time). MySQL implements this using Multi‑Version Concurrency Control (MVCC), storing version information for each row and providing a non‑locking consistent read.

2.2 Why RU and Serializable Do Not Use Snapshot Read

Serializable executes transactions strictly one after another, so MVCC is unnecessary.

Read Uncommitted (RU) allows reading uncommitted changes, making versioning irrelevant.

2.3 Read Committed (RC)

One of the isolation levels, abbreviated RC.

Eliminates dirty reads; only committed data is visible.

May still suffer from phantom reads, where consecutive SELECTs can return different result sets.

2.4 Repeatable Read (RR)

One of the isolation levels, abbreviated RR.

Prevents both dirty reads and phantom reads; consecutive SELECTs within the same transaction return identical result sets.

2.5 Differences in Snapshot Read Across Isolation Levels

2.5.1 Case Study 1

Transaction execution order:

Time | Transaction A | Transaction B
T1   | BEGIN          |
T2   |                | BEGIN
T3   |                | SELECT balance FROM account WHERE customer_id=123456;
T4   | UPDATE account SET balance=balance+1000 WHERE customer_id=123456; (uncommitted)
T5   |                | SELECT balance FROM account WHERE customer_id=123456;
T6   | COMMIT         |
T7   |                | SELECT balance FROM account WHERE customer_id=123456;

Repeatable Read T3 reads 500. T5 reads 500 because A has not committed. T7 reads 500 as the read view was established at the first read.

Read Committed T3 reads 500. T5 reads 500 (A still uncommitted). T7 reads 1500 because A has committed and the latest committed value is returned.

2.5.2 Case Study 2

Transaction execution order:

Time | Transaction A | Transaction B
T1   | BEGIN (balance=500) |
T2   |                | BEGIN
T3   |                | UPDATE account SET balance=balance+1000 WHERE customer_id=123456; (uncommitted)
T4   |                | COMMIT
T5   | SELECT balance FROM account WHERE customer_id=123456; |

Repeatable Read: The only read occurs after A commits, so the result is 1500.

Read Committed: The read also occurs after the commit, so the result is 1500.

2.6 Summary of Differences

First, a transaction can always read rows it has written (UPDATE/INSERT/DELETE). For other transactions, the behavior depends on the isolation level.

In RC mode, each read creates a new read view, always returning the latest committed data. In RR mode, the first read establishes a read view that persists for the entire transaction, guaranteeing that subsequent reads see the same snapshot.

RR: The read view is created at the first read and remains unchanged, ensuring repeatable reads.

RC: A new read view is created for each read, allowing the transaction to see the most recent committed values.