Understanding MySQL Transaction Isolation Levels with Real‑World Examples

Transaction isolation is one of the four ACID properties. MySQL defines four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Lower levels have less overhead but may cause dirty data.

We create a simple table for demonstration:

<code>CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;</code>

<code>INSERT INTO `t_test` VALUES (1, 'John');</code>

Now we illustrate each isolation level.

1. READ UNCOMMITTED

READ UNCOMMITTED allows a transaction to see uncommitted changes from other transactions (dirty read). Example:

<code># Set isolation level
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; -- sees updated value</code>

In a second transaction:

<code>SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM t_test WHERE id=1; -- also sees 'Tom' before commit</code>

In practice this level is rarely used.

2. READ COMMITTED

READ COMMITTED prevents dirty reads but allows non‑repeatable reads. Example:

<code># Transaction 1
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; -- sees 'Tom' (uncommitted)
COMMIT;</code>

<code># Transaction 2
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM t_test WHERE id=1; -- sees original 'John'
SLEEP(10);
SELECT * FROM t_test WHERE id=1; -- sees 'Tom' after commit</code>

This demonstrates non‑repeatable reads.

3. REPEATABLE READ

REPEATABLE READ guarantees that multiple reads of the same row within a transaction return the same result, eliminating non‑repeatable reads but not phantom reads.

<code># Transaction 1
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;
SLEEP(5);
COMMIT;</code>

<code># Transaction 2
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM t_test WHERE id=1; -- sees original 'John'
SLEEP(10);
SELECT * FROM t_test WHERE id=1; -- still sees 'John'
COMMIT;</code>

Phantom reads can still occur. Example with inserting a new row:

<code># Transaction 1
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
INSERT INTO t_test VALUES (2, 'Jack');
SLEEP(5);
COMMIT;</code>

<code># Transaction 2
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM t_test WHERE id=2; -- returns no rows
INSERT INTO t_test VALUES (2, 'Jack'); -- duplicate key error
COMMIT;</code>

This shows phantom‑read behavior.

4. SERIALIZABLE

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

<code># Transaction 1
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
INSERT INTO t_test VALUES (2, 'Jack');
SLEEP(5);
COMMIT;</code>

<code># Transaction 2
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM t_test WHERE id=2; -- blocks until Transaction 1 commits
COMMIT;</code>

In real applications this level is rarely used.

5. Summary

MySQL's transaction isolation levels are essential for data consistency and are common interview topics. The examples above, based on MySQL 5.7 with InnoDB, illustrate the behavior and trade‑offs of each level.