Why MySQL Defaults to REPEATABLE READ and How to Explain It in an Interview

Concurrency phenomena

Using an orders table, three classic anomalies are illustrated:

Dirty read : Transaction A sees rows written by Transaction B before B commits, which may later be rolled back.

Non‑repeatable read : The same row returns different values in two reads because another transaction committed an update.

Phantom read : A query returns a different result set after another transaction inserts or deletes rows that satisfy the same condition.

Isolation levels define what version of data a transaction may see and whether it can block others’ modifications , not merely whether concurrency exists.

READ COMMITTED (RC) semantics

RC guarantees that a transaction reads only committed data, but each ordinary read may obtain a fresh snapshot ( Read View), so results can change within the same transaction.

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE user_id = 1001 AND amount >= 10;  -- returns 2
-- Session B inserts a new row and COMMIT
SELECT COUNT(*) FROM orders WHERE user_id = 1001 AND amount >= 10;  -- may return 3
COMMIT;

Thus RC prevents dirty reads but does not guarantee repeatable results.

Why InnoDB defaults to REPEATABLE READ (RR)

RR creates a single Read View for the whole transaction. All ordinary (snapshot) reads reuse this view, so the transaction sees a consistent picture of the data even if other transactions commit changes after the view is built. This is ideal for batch reporting, long‑running read‑only transactions, or any workload where the result set must not drift (e.g., counting rows should stay at 100 throughout the transaction).

RR is not universally superior. Trade‑offs include:

Range locks may increase wait time.

Long‑running transactions keep undo records longer, enlarging the undo history.

If the business requires always reading the latest committed data, RC feels more natural.

High‑concurrency OLTP can also run on RC when application‑level concurrency control is sufficient.

Phantom reads under RR

Phantom protection applies only to snapshot reads (plain SELECT) because they reuse the same Read View. Queries that acquire locks— SELECT ... FOR UPDATE, UPDATE, DELETE —are “current reads”. They see the latest version and can observe newly inserted rows if the lock range does not cover them.

RR does not mean “no query ever sees a new row”; it means snapshot reads keep a stable view while current reads use locks to protect the accessed range.

MVCC essentials

Two structures are crucial:

Version chain : Columns DB_TRX_ID and DB_ROLL_PTR link old row versions in the undo log.

Read View : Determines which version of a row a transaction can see.

Typical causal chain:

Transaction A creates a Read View
 → Transaction B modifies a row and COMMITs
 → Transaction A issues another SELECT
 → A’s Read View hides B’s new version
 → Undo log supplies the visible old version

MVCC solves read‑consistency; preventing oversell, duplicate submissions, or other write‑conflicts still requires atomic SQL statements, unique constraints, and proper transaction boundaries.

Choosing an isolation level

READ COMMITTED – best for workloads that need the freshest committed data on every read (e.g., lock‑sensitive OLTP). Main cost: query results may change within a transaction.

REPEATABLE READ – best when a stable, consistent view is required across multiple reads (e.g., accounting reports, batch aggregation). Main cost: range‑lock wait and longer undo‑log pressure for long transactions.

SERIALIZABLE – provides the strongest consistency by preventing any concurrent write that could affect the read set. Main cost: significantly higher read‑write conflict and waiting, suitable only for low‑concurrency scenarios.

Snapshot read vs. current read

Ordinary SELECT is a snapshot read: it uses the transaction’s Read View plus the undo chain to present a historical version, and the view remains unchanged for the transaction’s lifetime (unless the transaction itself writes). SELECT ... FOR UPDATE, UPDATE, and DELETE are current reads: they acquire row or next‑key locks, see the latest committed version, and can block other transactions from inserting into the locked range.

RR is not “all queries never see new rows”; it is “snapshot reads keep a consistent view, while current reads use locks to protect the range they operate on”.

Interview‑style template (60 seconds)

SQL defines four isolation levels that correspond to dirty reads, non‑repeatable reads, and phantom reads. MySQL InnoDB defaults to REPEATABLE READ. Distinguish:

Snapshot reads ( SELECT) → MVCC → stable Read View → consistent view.

Current reads ( SELECT ... FOR UPDATE, UPDATE, DELETE) → acquire locks → see latest committed data.

Choose RR when you need a stable view across multiple reads; choose RC when you need the freshest data and can tolerate changing results. SERIALIZABLE adds full serializability at the cost of higher contention.