Master MySQL Transaction Isolation: Uncover ACID, MVCC, and Locking Secrets

As a long‑time MySQL practitioner, this article dives into the core of database systems—transaction isolation levels. Understanding these levels is essential for interview preparation and for building stable, consistent, high‑performance applications.

What Is a Transaction? What Is ACID?

A transaction is the smallest logical unit of work in a database; all its operations either succeed together or fail together. For example, a bank transfer must debit account A and credit account B as a single atomic operation.

Atomicity : The transaction is indivisible. InnoDB uses an Undo Log to roll back changes if the transaction fails.

Consistency : A transaction moves the database from one consistent state to another. The total amount of money before and after a transfer remains unchanged.

Isolation : The focus of this article. It ensures concurrent transactions do not interfere with each other, defining the strictness of “isolation”.

Durability : Once committed, changes are permanent. InnoDB uses a Redo Log to guarantee durability even after a crash.

Isolation challenges : Full isolation (serializable) is safe but severely limits concurrency, so databases provide multiple isolation levels to balance performance and consistency.

Three Problems Caused by Uncontrolled Concurrency

Dirty Read

A transaction reads data modified by another transaction that has not yet committed. If the other transaction rolls back, the first transaction has read invalid data.

Example :

Transaction A updates an account balance from 100 to 200 but does not commit.

Transaction B reads the balance and sees 200.

Transaction A rolls back, restoring the balance to 100.

Transaction B continues operating on the wrong “200” balance.

Non‑Repeatable Read

The same transaction reads the same row twice and gets different results because another committed transaction modified the row in between.

Example :

Transaction A reads balance = 100.

Transaction B commits an update, changing balance to 150.

Transaction A reads again and sees 150.

Phantom Read

The same transaction executes the same query twice and gets a different number of rows because another transaction inserted or deleted rows that satisfy the query condition.

Example :

Transaction A queries employees younger than 30 and gets 10 rows.

Transaction B inserts a new 25‑year‑old employee and commits.

Transaction A queries again and gets 11 rows.

SQL Standard Isolation Levels

Read Uncommitted : Allows dirty reads (❌).

Read Committed : Prevents dirty reads (✅) but allows non‑repeatable and phantom reads (❌).

Repeatable Read : Prevents dirty and non‑repeatable reads (✅) but may still allow phantom reads (❌).

Serializable : Prevents all three anomalies (✅).

Note : In InnoDB, the Repeatable Read level uses Next‑Key Locking to avoid most phantom reads, which is why it is the default.

Deep Dive into InnoDB MVCC and Lock Mechanisms

MySQL’s service layer handles transaction management, ensuring the four ACID properties. The main functions are:

Transaction isolation levels (four levels).

Lock management (shared, exclusive, intention, row, table locks).

Undo Log for rollback.

Redo Log for durability.

Two core techniques enable the isolation levels:

Multi‑Version Concurrency Control (MVCC)

Locking

MVCC: Snapshot vs. Current

MVCC keeps multiple versions of each row. InnoDB adds three hidden fields to every record: DB_TRX_ID: Transaction ID of the last update. DB_ROLL_PTR: Pointer to the previous version in the Undo Log. DB_ROW_ID: Hidden primary key if the table lacks a primary key.

The key structure is ReadView , which records: m_ids: List of active transaction IDs when the view was created. min_trx_id: Minimum active transaction ID. max_trx_id: Next transaction ID to be assigned. creator_trx_id: ID of the transaction that created the view.

Using ReadView, InnoDB decides whether a row version is visible to the current transaction.

Data visibility rules :

If trx_id < min_trx_id, the version is committed before the view and is visible.

If trx_id ≥ max_trx_id, the version started after the view and is invisible.

If min_trx_id ≤ trx_id < max_trx_id, check whether trx_id is in m_ids.

If present, the transaction is still active → invisible.

If absent, the transaction has committed → visible.

If trx_id equals creator_trx_id, the row was modified by the current transaction → visible.

Lock Mechanisms

While MVCC solves most read‑concurrency issues, writes still require locks.

Shared lock (S) : Allows multiple transactions to read a row.

Exclusive lock (X) : Allows a transaction to update or delete a row; no other locks are permitted.

InnoDB also uses Intention Locks at the table level to indicate the type of row lock a transaction intends to acquire, improving lock‑conflict detection.

Detailed Level Analysis and InnoDB Implementation

1. Read Uncommitted

Implementation : Almost no read locks; reads see the latest page version, ignoring MVCC and Undo Log.

Problems : All concurrency anomalies appear.

Use case : Rare; only when data consistency is irrelevant.

2. Read Committed (RC)

How it avoids dirty reads : A transaction can only see data committed by others.

InnoDB implementation : Each SELECT creates a new ReadView; writes use row‑level exclusive locks and record Undo Log entries.

Remaining issues : Non‑repeatable reads and phantom reads can still occur because each statement gets a fresh snapshot.

Example of RC MVCC:

BEGIN; -- T1
UPDATE account SET balance = 200 WHERE id=1; -- T2
SELECT balance FROM account; -- T3 (ReadView1 sees 100)
COMMIT; -- T4
SELECT balance FROM account; -- T5 (ReadView2 sees 200)

3. Repeatable Read (RR)

Avoiding non‑repeatable reads : The first SELECT creates a ReadView that is reused for the whole transaction.

Write and current read : Use row locks and Next‑Key Locks to prevent other transactions from modifying the locked range, thus avoiding phantom reads.

Next‑Key Lock : Combines a record lock with a gap lock, protecting both the row and the surrounding gap.

Example of RR with Next‑Key Lock:

SELECT * FROM employees WHERE age = 25 FOR UPDATE; -- locks the index record and the gap
INSERT INTO employees (age) VALUES (25); -- blocked until the lock is released

4. Serializable

Implementation : All reads acquire an implicit shared lock (SELECT … FOR SHARE), making the level the strictest and causing the most blocking.

Use case : Scenarios demanding absolute consistency, such as core financial accounting systems.

Practical Guidance and Choosing an Isolation Level

Default in MySQL : Repeatable Read, because it balances consistency (prevents non‑repeatable and phantom reads) with good concurrency thanks to MVCC and Next‑Key Lock.

When to prefer RC : High‑write workloads where reducing lock contention and deadlocks is more important than strict repeatable reads.

How to view and set levels :

Check current session level: SELECT @@transaction_isolation; Set global level (requires restart): SET GLOBAL transaction_isolation = 'READ-COMMITTED'; Set session level: SET SESSION transaction_isolation = 'REPEATABLE-READ'; Avoid long‑running transactions : They keep Undo Log chains alive, consume storage, and hold locks that block other transactions.

Conclusion

MySQL’s transaction isolation levels form a well‑designed hierarchy that balances isolation and performance. From Read Committed to Repeatable Read, the system evolves from per‑statement snapshots to a transaction‑wide snapshot, and from simple row locks to sophisticated Next‑Key Locks. Understanding these mechanisms empowers you to choose the right level for your workload and troubleshoot concurrency issues confidently.