Why MySQL Defaults to Repeatable Read – Inside Transaction Isolation

What Is a Database Transaction?

A transaction is a finite sequence of database operations that must either all succeed or all fail, forming an indivisible unit of work.

If A transfers 100 yuan to B, both the debit and credit must happen together; otherwise the system rolls back the debit.

The Four ACID Properties

Atomicity : All operations succeed or none do.

Consistency : Data remains valid before and after the transaction.

Isolation : Concurrent transactions do not interfere with each other.

Durability : Once committed, changes persist permanently.

Isolation Levels and MySQL’s Default

MySQL supports four isolation levels:

Read Uncommitted : Allows dirty reads, non‑repeatable reads, and phantom reads.

Read Committed : Prevents dirty reads but still suffers non‑repeatable and phantom reads.

Repeatable Read (RR) : Prevents dirty and non‑repeatable reads; phantom reads may still occur.

Serializable : Executes transactions serially, eliminating all three anomalies at the cost of performance.

MySQL’s default isolation level is Repeatable Read (RR) .

Why MySQL Chooses RR as the Default

MySQL typically runs in a master‑slave cluster. The master writes data and replicates it to slaves via the binary log (binlog). Binlog can be in statement, row, or mixed format. Early MySQL versions used only statement format, which could cause data inconsistency under RC because the binlog records the original SQL statements without row‑level versioning.

When the binlog format is statement and the isolation level is RC, a transaction that updates a row may be replayed on the slave after another transaction has already modified the same row, leading to divergent data between master and slave. RR introduces gap locks and next‑key locks, ensuring that the same snapshot is used throughout the transaction, thus avoiding the inconsistency.

Why Some Large‑Scale Companies Prefer RC

High‑concurrency workloads often favor RC because:

RC only acquires row‑level locks on modified rows, reducing lock contention.

RC supports “semi‑consistent reads,” allowing a transaction to read the latest committed version of a row without waiting for locks, which improves throughput.

RC avoids the overhead of gap locks and next‑key locks required by RR, decreasing the chance of deadlocks.

Concurrency Anomalies by Isolation Level

Read Uncommitted : Dirty reads, non‑repeatable reads, phantom reads.

Read Committed : Non‑repeatable reads, phantom reads.

Repeatable Read : Phantom reads.

Serializable : No anomalies.

How MySQL Implements Isolation Levels

MySQL uses MVCC and locking mechanisms:

RU (Read Uncommitted) performs no locking, allowing dirty reads.

RC and RR rely on MVCC to provide snapshot visibility.

Serializable uses explicit locking (shared for reads, exclusive for writes) to enforce serial execution.

MVCC Fundamentals

MVCC (Multi‑Version Concurrency Control) stores multiple versions of a row. Each version carries a hidden trx_id and a roll_pointer. Queries see the version whose trx_id is less than the transaction’s snapshot limits.

Snapshot Read vs. Current Read

Snapshot Read : Reads the visible version without acquiring locks; typical SELECT statements.

Current Read : Reads the latest version with an explicit lock; used by SELECT … FOR UPDATE.

Implicit Fields

InnoDB rows contain hidden columns trx_id and roll_pointer. If a table lacks a primary key or non‑NULL unique key, a third hidden row_id is added.

Undo Log

The undo log records the before‑image of modified rows, enabling rollback and providing historical snapshots for MVCC.

Version Chain

Each row’s versions are linked via roll_pointer, forming a version chain that MVCC traverses to find the appropriate snapshot.

Transaction ID and Read View

When a transaction starts, it obtains a unique trx_id. A Read View captures the set of active transaction IDs ( m_ids) and the minimum/maximum IDs ( min_limit_id, max_limit_id). Visibility rules compare a row’s trx_id against these limits to decide if the row is visible.

MVCC Read Flow

Obtain the transaction’s trx_id.

Generate a Read View.

Fetch rows and compare each row’s trx_id with the Read View limits.

If a row is invisible, retrieve the appropriate version from the undo log.

Return the rows that satisfy the visibility rules.

InnoDB implements MVCC using the combination of Read View and Undo Log.

Analyzing RC’s Non‑Repeatable Read Issue

Example: Two transactions A and B operate on a table t. Under RC, A reads a row (trx_id = 100) and later B updates the same row (trx_id = 101). When A re‑queries after B commits, a new Read View includes the updated version, so A sees different data, demonstrating a non‑repeatable read.

Under RR, the same transaction would reuse a single snapshot, guaranteeing consistent reads.

Handling Large and Long Transactions

Split large transactions into smaller ones to reduce lock time.

Optimize queries (add indexes, rewrite SQL).

Avoid holding locks for extended periods; keep transactions short.

Use scheduled jobs for periodic processing.

Tune transaction log settings (write‑ahead logging, flush policies).

Scale hardware resources when necessary.

Optimizing MySQL Transaction Performance

Choose the appropriate storage engine (e.g., InnoDB).

Create effective indexes to speed up reads and writes.

Avoid unnecessary locking; consider optimistic locking.

Select the right isolation level for the workload.

Minimize the transaction scope.

Use autocommit for read‑only work.

Prevent long‑running transactions.

Upgrade hardware (CPU, memory, storage) as needed.

Consider distributed database architectures for extreme concurrency.

InnoDB Transaction Mechanism

Each transaction gets a unique trx_id.

MVCC provides snapshot isolation; only committed versions before the transaction start are visible.

Row‑level locks are applied on index entries during updates.

ACID guarantees are achieved via redo logs (for durability) and undo logs (for rollback).

Default isolation is Repeatable Read, implemented with locks and MVCC.