---

🟢 Junior Level

In MySQL, when using the standard and ubiquitous InnoDB storage engine, the default transaction isolation level is Repeatable Read.

The Core Concept in 30 Seconds

Under Repeatable Read, a transaction operates against an immutable data snapshot (Read View) created at the instant the first reading SQL statement (SELECT) executes.

Key practical characteristics:

1. Non-Repeatable Reads are completely eliminated: Regardless of how many times SELECT balance FROM accounts WHERE id = 1 is executed inside a transaction, the returned value remains identical, even if thousands of concurrent transactions modify and commit new balances in the interim.
2. Phantom Reads are prevented: Exceeding the baseline requirements of the ANSI SQL-92 standard, MySQL InnoDB blocks phantom rows: for standard queries via the frozen MVCC Read View, and for locking queries (SELECT ... FOR UPDATE, UPDATE, DELETE) via Next-Key Locks (index record and gap locks).

Execution Timeline

Transaction A (Repeatable Read)             Concurrent Transaction B
------------------------------             ------------------------
BEGIN;
SELECT balance FROM accounts 
WHERE id = 1; -- Returns 1000.00
                                           BEGIN;
                                           UPDATE accounts SET balance = 5000.00 WHERE id = 1;
                                           COMMIT; -- Formally committed!
SELECT balance FROM accounts 
WHERE id = 1; -- Still 1000.00! (B's committed update is invisible)
COMMIT;

Default Engine Comparison: MySQL InnoDB vs. PostgreSQL

Characteristic	MySQL (InnoDB)	PostgreSQL
Default Isolation Level	Repeatable Read	Read Committed
Snapshot Lifecycle	One snapshot per transaction (at first SELECT)	New snapshot per individual SQL statement
Phantom Read Protection	Prevented (MVCC + Next-Key Locking)	Allowed (on default Read Committed)
Deadlock Probability	Higher (due to index Gap Locks)	Lower (locks individual physical rows only)
MVCC Architecture	Undo Log (old versions stored in undo segments)	Heap Tuples (all versions in table pages)

Practical Spring Boot Example

In Spring Boot configured with a MySQL DataSource, @Transactional without an explicit isolation parameter executes under MySQL’s default Repeatable Read:

@Service
public class MysqlReportService {

    private final AccountRepository accountRepository;

    public MysqlReportService(AccountRepository accountRepository) {
        this.accountRepository = accountRepository;
    }

    @Transactional // In MySQL InnoDB: Repeatable Read
    public void generateConsistentAuditReport(Long accountId) {
        // Query 1: Establishes the transaction Read View
        BigDecimal firstBalance = accountRepository.findBalanceNative(accountId);

        // Simulate intensive external API calls and complex calculations
        simulateHeavyCalculation();

        // Query 2: Guaranteed to observe the exact same balance,
        // even if concurrent transactions committed updates in the meantime
        BigDecimal secondBalance = accountRepository.findBalanceNative(accountId);

        assert firstBalance.compareTo(secondBalance) == 0 : "Balances desynchronized!";
    }

    private void simulateHeavyCalculation() {
        try {
            Thread.sleep(1000);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}

---

🟡 Middle Level

Why MySQL Historically Chose Repeatable Read: The SBR Mandate

The primary reason MySQL adopted Repeatable Read as its default was Statement-Based Replication (SBR), which was the architectural standard across MySQL 3.x, 4.x, and 5.x.

Statement-Based Replication Desynchronization Risk:
Master:
  T1 (started at 10:00:00): UPDATE orders SET status = 'DONE' WHERE status = 'PENDING';
  T2 (started at 10:00:01): INSERT INTO orders (status) VALUES ('PENDING'); COMMIT;
  T1 (commits at 10:00:05): COMMIT;

Binary Log (Binlog) on Master:
  Events are written to binlog strictly UPON COMMIT:
  1. T2 (COMMITTED at 10:00:01)
  2. T1 (COMMITTED at 10:00:05)

Replica (Slave) Replays Binlog Statements in Order:
  1. INSERT INTO orders ... ('PENDING')
  2. UPDATE orders SET status = 'DONE' WHERE status = 'PENDING'
  -> On Replica: T2's new row is converted to 'DONE'!
  -> On Master:  T2's row remained 'PENDING' because T1 did not see T2's insert!
  -> DISASTER: Data divergence between Master and Replica!

To guarantee replica consistency under binlog_format = STATEMENT, InnoDB had to physically prevent concurrent transactions from inserting new rows into ranges scanned by active transactions. This was achieved by enforcing Repeatable Read with Next-Key Locks.

[!NOTE] Modern Industry Reality: Starting with MySQL 5.7 and 8.0, Row-Based Replication (binlog_format = ROW) became the default. Because physical row changes are replicated directly, replication divergence is eliminated. Consequently, highload engineering teams (Meta, Uber, GitHub) routinely switch production MySQL defaults to READ-COMMITTED to eliminate gap lock contention.

MVCC Internals in InnoDB: Clustered Index and the Undo Log

In contrast to PostgreSQL (which leaves multi-version tuples in the primary table), InnoDB implements MVCC through separate rollback segments:

1. Clustered Index (Primary B+Tree): Stores strictly the latest committed/in-flight version of every record.
2. Hidden System Columns: Every index row contains internal tracking fields:
   • DB_TRX_ID (6 bytes): Transaction ID of the last transaction that inserted or updated the row.
   • DB_ROLL_PTR (7 bytes): Rollback pointer pointing to the previous row version in the Undo Log.
   • DB_ROW_ID (6 bytes): Internal auto-increment row ID (created only if no primary key or unique index exists).
3. Undo Log Version Chain: When a transaction updates a row, the previous version is copied into the Undo Tablespace, and the new row’s DB_ROLL_PTR points to that undo record.

Clustered Index (Table Heap):
[ ID: 1 | balance: 1500 | DB_TRX_ID: 200 | DB_ROLL_PTR: &undo_1 ]
                                                   |
                                                   v
Undo Log (Undo Tablespace):
[ Undo Record undo_1: balance: 1200 | DB_TRX_ID: 150 | DB_ROLL_PTR: &undo_2 ]
                                                   |
                                                   v
[ Undo Record undo_2: balance: 1000 | DB_TRX_ID: 100 | DB_ROLL_PTR: NULL ]

When a SELECT query runs:

• The engine checks DB_TRX_ID against the transaction’s Read View.
• If the row is newer than the snapshot, InnoDB traverses backward along DB_ROLL_PTR through the undo log chain in RAM/buffer pool until it constructs the exact version visible to the snapshot.

---

🔴 Senior Level

The InnoDB ReadView Structure in Source Code

In MySQL’s storage engine core (storage/innobase/include/read0types.h), a ReadView contains the following critical fields:

class ReadView {
    trx_id_t m_low_limit_id;     // Upper bound: First transaction ID allocated AFTER this ReadView.
                                 // Any trx_id >= m_low_limit_id is completely INVISIBLE.
    trx_id_t m_up_limit_id;      // Lower bound: Smallest transaction ID among all active transactions.
                                 // Any trx_id < m_up_limit_id is guaranteed VISIBLE.
    trx_id_t m_creator_trx_id;   // Transaction ID that created this ReadView (its own writes are always visible).
    ids_t    m_ids;              // Set of active (uncommitted) transaction IDs at ReadView creation.
};

Visibility Evaluation Rules:

1. If trx_id == m_creator_trx_id $\rightarrow$ Visible (own modifications).
2. If trx_id < m_up_limit_id $\rightarrow$ Visible (committed prior to snapshot creation).
3. If trx_id >= m_low_limit_id $\rightarrow$ Invisible (began after snapshot creation).
4. If $m_{\text{up_limit_id}} \le \text{trx_id} < m_{\text{low_limit_id}}$: Check whether trx_id is present in array m_ids. If present, the transaction was uncommitted when the view formed $\rightarrow$ Invisible. If absent $\rightarrow$ Visible.

Next-Key Locking: Record Lock + Gap Lock

InnoDB uses three distinct row-level lock primitives:

1. Record Lock: Locks the specific index record.
2. Gap Lock: Locks the gap interval between index records, preventing other transactions from inserting into that space.
3. Next-Key Lock: A compound lock consisting of a Record Lock on the record plus a Gap Lock on the preceding interval: $(\text{previous_key}, \text{current_key}]$.

Index on column 'age': values [18, 25, 40]
Next-Key Intervals:
  (-∞, 18]
  (18, 25]
  (25, 40]
  (40, +∞) (Lock on the "supremum" pseudo-record)

For example:

-- Transaction T1 (Repeatable Read):
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;

InnoDB places Next-Key Locks on intervals (18, 25] and (25, 40]. If a concurrent transaction attempts:

-- Transaction T2:
INSERT INTO users (name, age) VALUES ('Bob', 22); -- BLOCKED! Waits on T1's gap lock

The Gap Lock Deadlock Dilemma & Highload Tuning

The primary drawback of Repeatable Read in high-throughput OLTP systems is frequent Deadlocks:

1. Gap locks do not conflict with other gap locks: Two concurrent transactions can both hold gap locks over interval $(10, 20)$ simultaneously.
2. Gap locks conflict with Insert Intention Locks: When both transactions attempt to insert into that same gap, each waits for the other’s gap lock to release, triggering a circular deadlock:

T1 holds Gap Lock on (10, 20)
T2 holds Gap Lock on (10, 20)
T1 executes INSERT (id = 15) -> Waits on T2's Gap Lock
T2 executes INSERT (id = 16) -> Waits on T1's Gap Lock
-> DEADLOCK! InnoDB aborts one transaction:
"Deadlock found when trying to get lock; try restarting transaction"

Production Recommendation for High-Concurrency Systems:

1. Enable row-based replication in my.cnf:

   binlog_format = ROW
   

2. Set the default isolation level to Read Committed:

   transaction-isolation = READ-COMMITTED
   

3. Under READ-COMMITTED, InnoDB completely disables gap locking for regular index scans and searches (retaining it only for foreign key and unique constraint checks). Deadlock occurrences drop by 90% to 99%, and write throughput climbs by 20% to 40%.

---

4 Tricky Questions

1. How can you trigger a phantom row using UPDATE under Repeatable Read in MySQL (the InnoDB “Phantom Trap”)?

Answer: While plain SELECT reads from the frozen MVCC snapshot, an UPDATE in InnoDB executes as a Current Read (inspecting latest committed physical data).

Consider the following sequence:

-- Transaction T1 (Repeatable Read):
BEGIN;
SELECT * FROM users WHERE status = 'NEW'; -- Returns 0 rows. ReadView created.

-- Concurrent Transaction T2:
INSERT INTO users (id, name, status) VALUES (10, 'Alice', 'NEW');
COMMIT;

-- Transaction T1 continues:
SELECT * FROM users WHERE status = 'NEW'; -- Still returns 0 rows (protected by ReadView).

-- Transaction T1 issues a bulk update:
UPDATE users SET status = 'ACTIVE' WHERE status = 'NEW';
-- Result: Rows matched: 1, Changed: 1!
-- The UPDATE performed a Current Read, discovered row 10, and updated its DB_TRX_ID to T1!

-- Transaction T1 re-executes SELECT:
SELECT * FROM users WHERE status = 'ACTIVE';
-- SUDDENLY row id = 10 APPEARS in T1's SELECT!

Why this happens: Because Transaction T1 mutated row 10 itself, the row’s DB_TRX_ID became $T_1$. Under MVCC visibility rules, every transaction is always allowed to see its own modifications. As a result, the newly inserted phantom row materialized inside T1.

---

2. What happens if you execute SET TRANSACTION ISOLATION LEVEL READ COMMITTED on a MySQL instance running binlog_format = STATEMENT?

Answer: MySQL raises an immediate runtime error upon attempting any DML write operation: ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.

Because READ COMMITTED disables gap locking, concurrent transactions on the master can commit statements in an interleaved order that cannot be deterministically replayed on replicas via SQL statements. MySQL’s storage engine enforces this safety check at the kernel level. Switching to READ COMMITTED requires setting binlog_format = ROW or MIXED.

---

3. How does MVCC storage differ between MySQL InnoDB and PostgreSQL, and how does it impact long-running transactions?

Answer:

• PostgreSQL: Uses an in-place append-only heap model. Old row versions (dead tuples) reside directly inside table pages alongside active rows. Long-running transactions prevent autovacuum from reclaiming dead tuples, causing severe table and index bloat.
• MySQL InnoDB: Uses Rollback Segments / Undo Logs. The clustered index stores strictly the latest version of the row, while previous versions are archived into the Undo Tablespace.
• Impact on long queries: In MySQL, long-running queries do not cause table bloat, but they prevent the background Purge Thread from freeing undo log pages. The Undo Tablespace expands significantly, and queries on heavily modified rows suffer high CPU/IO penalties because the engine must traverse long DB_ROLL_PTR pointer chains to reconstruct historical row states.

---

4. Why can SELECT * FROM orders WHERE status = 'PENDING' FOR UPDATE lock the ENTIRE table in MySQL InnoDB if status is not indexed?

Answer: When executing a locking query (FOR UPDATE), InnoDB must acquire Next-Key Locks on all scanned index records and their corresponding gaps:

1. If column status lacks an index, the query optimizer is forced to perform a Full Table Scan across the primary clustered index.
2. During the scan, InnoDB evaluates rows one by one, placing a Next-Key Lock on every single record in the clustered index and on every gap between them, up to the supremum pseudo-record.
3. As a result, the entire table is effectively locked against concurrent writes: no other transaction can perform INSERT, UPDATE, or DELETE on any row in the table, even if the target rows have a completely different status (e.g., 'COMPLETED').

---

🎯 Interview Cheat Sheet

• Default Isolation: MySQL InnoDB defaults to Repeatable Read.
• MVCC Implementation: Clustered Index (latest committed data) + Undo Log (historical versions linked via DB_ROLL_PTR and DB_TRX_ID).
• Next-Key Locks: Compound lock ($\text{Record Lock} + \text{Gap Lock}$). Prevents phantom inserts into index intervals.
• Historical Rationale: Enforced to guarantee replica consistency under Statement-Based Replication (SBR).
• InnoDB Phantom Trap: Executing UPDATE ... WHERE acts as a Current Read; modifying a concurrent insert sets its DB_TRX_ID to the current transaction, causing it to appear in subsequent SELECT queries.
• Highload Production Practice: Configure binlog_format = ROW and transaction-isolation = READ-COMMITTED to disable gap locks and reduce deadlocks by over 90%.

Related Topics

• What is Repeatable Read
• What is the Default Isolation Level in PostgreSQL
• What is Phantom Read
• What is Lost Update
• What is Transaction Propagation in Spring