Master MySQL Transaction Isolation: From Dirty Reads to Serializable

MySQL Transactions

Only the InnoDB storage engine supports transactions in MySQL. A transaction is a group of statements that either all succeed or all are rolled back, providing the ACID properties (Atomicity, Consistency, Isolation, Durability). This summary concentrates on the Isolation aspect.

Isolation Anomalies

Dirty read

Reading data that another transaction has modified but not yet committed. The read value may later be rolled back, so the reader sees non‑existent data.

Non‑repeatable read

Reading the same rows at different times within a single transaction yields different values because another transaction has committed updates.

Phantom read

Re‑executing a query within a transaction and seeing newly inserted rows that were not present before the first execution.

SQL Isolation Levels

READ UNCOMMITTED – Allows dirty reads; provides the lowest isolation and highest performance.

READ COMMITTED – Prevents dirty reads but allows non‑repeatable reads and phantom reads.

REPEATABLE READ – Prevents dirty reads and non‑repeatable reads; MySQL’s default. Phantom reads are prevented by the InnoDB implementation (next‑key locks).

SERIALIZABLE – Prevents all three anomalies but incurs the greatest performance cost because transactions are executed sequentially.

Checking and Setting the Isolation Level

Show the current isolation level (MySQL 5.7.20+):

# MySQL 5.7.20 and later
show variables like 'transaction_isolation';
SELECT @@transaction_isolation;
SELECT @@tx_isolation;

Change the level (SESSION or GLOBAL):

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

GLOBAL changes affect only new client connections; existing sessions must reconnect to see the new setting.

Executing Transactions in MySQL

A transaction starts with BEGIN or START TRANSACTION, runs a series of statements, and ends with COMMIT (or ROLLBACK). The first statement after BEGIN actually marks the start of the transaction.

BEGIN;
SELECT * FROM user;
COMMIT; -- or ROLLBACK

Active transactions can be inspected with:

SELECT * FROM information_schema.innodb_trx;

Practical Experiments for Each Isolation Level

READ UNCOMMITTED

Set the level globally, open two sessions, update a row in session A, and query the same row in session B before A commits. Session B sees the uncommitted value (dirty read), demonstrating the risk.

READ COMMITTED

After switching to READ COMMITTED, session B cannot see the uncommitted update; it only sees the new value after session A commits, eliminating dirty reads but still allowing non‑repeatable reads.

REPEATABLE READ

With REPEATABLE READ, session B sees the same data throughout the transaction, even after session A commits, preventing non‑repeatable reads. New rows inserted by another transaction can still appear (phantom reads), but InnoDB resolves this with next‑key locks.

SERIALIZABLE

This level forces transactions to execute sequentially, eliminating dirty reads, non‑repeatable reads, and phantom reads, but at a high performance cost.

How MySQL Implements Isolation

MySQL uses a combination of row‑level locking and Multi‑Version Concurrency Control (MVCC). Each row version stores a row_trx_id indicating the transaction that created it. A snapshot (consistent view) determines which versions are visible to a transaction.

Visibility rules for a snapshot:

Rows created by the current transaction are visible.

Uncommitted rows from other transactions are invisible.

Rows committed after the snapshot was taken are invisible.

Rows committed before the snapshot are visible.

READ COMMITTED creates a new snapshot for every statement; REPEATABLE READ creates a single snapshot when the transaction starts.

Concurrent Write Handling

When two transactions update the same row, the first transaction acquires a row lock that is released only after it commits. The second transaction waits for the lock and may time out if the first transaction runs too long.

-- Indexed primary key (fast lock acquisition)
UPDATE user SET age = 11 WHERE id = 1;

-- No index on the search column (full‑table scan, then lock rows)
UPDATE user SET age = 11 WHERE age = 10;

Phantom Read Prevention

In REPEATABLE READ, InnoDB prevents phantom reads by using next‑key locks (row lock + gap lock). For indexed columns, gaps between index entries are locked, blocking other sessions from inserting rows that would fall into the original query range.

If the column is not indexed, MySQL locks the whole table, which can severely impact performance on large tables.

Summary

InnoDB is the only MySQL engine that supports transactions. The default isolation level is REPEATABLE READ, which, thanks to MVCC and next‑key locks, prevents dirty reads, non‑repeatable reads, and phantom reads. READ UNCOMMITTED offers the best performance but no isolation, while SERIALIZABLE provides full isolation at the cost of sequential execution. Understanding these levels and their underlying mechanisms helps you choose the appropriate trade‑off for your application.