Master MySQL Transaction Isolation: From Dirty Reads to Serializable

Transaction and ACID

A transaction is the smallest logical unit of work in a database; it either commits completely or rolls back entirely. To guarantee reliability and consistency, a transaction must satisfy the ACID properties: Atomicity (implemented via Undo Log), Consistency , Isolation (the focus of this article), and Durability (implemented via Redo Log).

Isolation anomalies in concurrent transactions

Dirty read

Transaction A modifies data but does not commit. Transaction B reads the uncommitted value. If A later rolls back, B has read a stale, invalid value.

Txn A updates balance from 100 to 200 (uncommitted).

Txn B reads the balance and sees 200.

Txn A rolls back, restoring balance to 100.

Txn B continues operating on the erroneous 200.

Non‑repeatable read

Within a single transaction, two reads of the same row return different values because another committed transaction updated the row in between.

Txn A reads balance = 100.

Txn B commits an update setting balance = 150.

Txn A reads again and sees 150.

Phantom read

Two identical queries return a different number of rows because another transaction inserted or deleted rows that satisfy the query condition.

Txn A queries employees younger than 30, gets 10 rows.

Txn B inserts a new 25‑year‑old employee.

Txn A repeats the query and now gets 11 rows.

Difference: Non‑repeatable read concerns a single row’s value change; phantom read concerns the row count of a result set.

SQL standard isolation levels

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

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

Repeatable Read : Prevents dirty and non‑repeatable reads; phantom reads may still occur under the standard.

Serializable : Prevents all three phenomena.

Note: In MySQL InnoDB, the Repeatable Read level already avoids most phantom reads through Next‑Key Locking, which is why it is the default.

InnoDB MVCC and locking

InnoDB implements isolation using two core mechanisms:

Multi‑Version Concurrency Control (MVCC) : Each row stores three hidden fields: DB_TRX_ID – ID of the transaction that last modified the row. DB_ROLL_PTR – Pointer to the previous version in the Undo Log. DB_ROW_ID – Hidden primary‑key identifier.

Locking : Row‑level shared (S) and exclusive (X) locks, plus intention locks at the table level.

When a snapshot read ( SELECT) occurs, InnoDB creates a ReadView that contains: m_ids – IDs of active transactions at view creation. min_trx_id – Smallest active ID. max_trx_id – Next transaction ID to be assigned. creator_trx_id – ID of the transaction that created the view.

Visibility rules applied to a row version ( trx_id) are:

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

If trx_id ≥ max_trx_id, the version is invisible (created after the view).

If min_trx_id ≤ trx_id < max_trx_id, check m_ids:

Present → invisible (still active, not committed).

Absent → visible (committed).

If trx_id equals creator_trx_id, the version is visible (the transaction’s own changes).

Lock types:

Shared lock (S) : Allows concurrent reads but blocks exclusive locks.

Exclusive lock (X) : Blocks all other locks on the row.

Intention locks : Table‑level markers indicating a future row‑level lock, improving lock‑conflict detection.

Implementation of each isolation level in InnoDB

Read Uncommitted

Implementation: No MVCC snapshot, no read locks; transactions read the latest page version directly.

Problems: All three anomalies (dirty, non‑repeatable, phantom) can occur.

Typical use: Rare, only when data correctness is irrelevant.

Read Committed

Each SELECT generates a new ReadView, guaranteeing that only committed data is read (prevents dirty reads). Because the snapshot is statement‑level, the same transaction may see different data across statements, leading to non‑repeatable and phantom reads.

Read: New ReadView per statement.

Write: Row‑level exclusive lock and Undo Log.

Remaining issues: Non‑repeatable and phantom reads.

Repeatable Read

InnoDB creates a single ReadView at the first snapshot read of a transaction and reuses it for the rest of the transaction, ensuring that repeated reads return the same data. Writes use row‑level locks and Next‑Key Locks (record lock + gap lock) to prevent phantom reads.

Read: Transaction‑level snapshot (single ReadView).

Write: Row‑level lock + Next‑Key Lock.

Next‑Key Lock blocks inserts into the locked range, eliminating phantom reads.

Serializable

The strictest level forces every read to acquire a shared lock (implicit SELECT ... FOR SHARE), making all read‑write and write‑write conflicts block each other. This guarantees full isolation but severely reduces concurrency.

Implementation: All reads hold shared locks.

Typical use: Scenarios where absolute consistency outweighs performance, e.g., core financial accounting.

Practical guidance and choosing an isolation level

MySQL defaults to Repeatable Read because it balances consistency (prevents non‑repeatable and most phantom reads via MVCC and Next‑Key Lock) with acceptable performance.

When to prefer Read Committed :

Reduce lock conflicts and deadlocks (no gap locks).

Application logic tolerates reading the latest committed data on each statement.

Safe with binlog_format=ROW for replication.

When to stay with Repeatable Read :

Business logic requires strict repeatable reads (e.g., accounting, reporting).

Write concurrency is moderate and the extra locking overhead is acceptable.

How to view and set isolation levels:

Show 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 because they keep Undo Log versions alive, consume storage, and hold locks that block other transactions.

Conclusion

MySQL transaction isolation levels form a clear hierarchy: moving from Read Committed to Repeatable Read upgrades MVCC from per‑statement snapshots to a single transaction snapshot and introduces Next‑Key Locking to eliminate phantom reads. Understanding these mechanisms enables you to choose the level that best matches your application’s consistency and performance requirements.