Understanding MySQL Transaction Isolation Levels with Practical Examples

MySQL transactions follow the ACID properties, and isolation is one of the four key aspects. The SQL standard and MySQL define four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Lower levels reduce overhead but may allow anomalies like dirty data.

1. Read Uncommitted

Read Uncommitted is the lowest isolation level; changes made by a transaction are visible to others even before a COMMIT, leading to dirty reads. It is rarely used in production.

# Set isolation level to Read Uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE t_test SET name='Tom' WHERE id=1;
SELECT * FROM t_test WHERE id=1;  -- verify update
COMMIT;

Transaction 2, after setting the same isolation level, can read the uncommitted update:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM t_test WHERE id=1;

2. Read Committed

Read Committed prevents dirty reads but allows non‑repeatable reads: the same query can return different results if another transaction commits in between.

# Transaction 1 (Read Committed)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE t_test SET name='Tom' WHERE id=1;
SELECT * FROM t_test WHERE id=1;  -- before commit
SELECT SLEEP(5) FROM dual;        -- pause to let Transaction 2 run
COMMIT;

# Transaction 2 (Read Committed)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM t_test WHERE id=1;  -- sees original data
SELECT SLEEP(10) FROM dual;       -- wait for Transaction 1 to commit
SELECT * FROM t_test WHERE id=1;  -- sees updated data

3. Repeatable Read

Repeatable Read guarantees that multiple reads of the same row within a transaction return identical results, eliminating non‑repeatable reads but still allowing phantom (or “幻读”) rows.

# Transaction 1 (Repeatable Read)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
UPDATE t_test SET name='Tom' WHERE id=1;
SELECT * FROM t_test WHERE id=1;
SELECT SLEEP(5) FROM dual;
COMMIT;

# Transaction 2 (Repeatable Read)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM t_test WHERE id=1;  -- sees original data
SELECT SLEEP(10) FROM dual;
SELECT * FROM t_test WHERE id=1;  -- still sees original data

To illustrate phantom reads, Transaction 1 inserts a new row (id = 2) but does not commit. Transaction 2 cannot see the row, and attempts to insert the same id results in a duplicate‑key error, demonstrating the phantom‑read phenomenon.

# Transaction 1 (Repeatable Read, phantom scenario)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
INSERT INTO t_test VALUES (2, 'Jack');
SELECT * FROM t_test WHERE id=2;
SELECT SLEEP(5) FROM dual;
COMMIT;

# Transaction 2 (Repeatable Read)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM t_test WHERE id=2;  -- returns empty
INSERT INTO t_test VALUES (2, 'Jack');  -- duplicate key error

4. Serializable

Serializable is the strictest level; it forces transactions to execute sequentially, preventing phantom reads but incurring heavy locking and reduced performance.

# Transaction 1 (Serializable)
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
INSERT INTO t_test VALUES (2, 'Jack');
SELECT * FROM t_test WHERE id=2;
SELECT SLEEP(5) FROM dual;
COMMIT;

# Transaction 2 (Serializable)
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM t_test WHERE id=2;  -- blocks until Transaction 1 commits
COMMIT;

5. Summary

MySQL’s transaction isolation levels are crucial for ensuring data consistency and controlling concurrency. They are frequent interview topics. The examples above, based on MySQL 5.7 with the InnoDB engine, demonstrate how each level behaves, the anomalies they prevent, and their performance trade‑offs.