Demonstrating MySQL Transaction Isolation Levels with Practical Examples

Transaction isolation is one of the four ACID properties of relational databases. MySQL supports four isolation levels defined by the SQL standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Lower levels reduce overhead but may allow dirty data.

To illustrate these levels, a minimal table t_test is created and a single row is inserted for testing:

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

INSERT INTO `t_test` VALUES ('1', 'John');

1. READ UNCOMMITTED

This is the lowest isolation level; uncommitted changes are visible to other transactions (dirty reads). Example:

# 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;  -- sees the uncommitted update

Another transaction can read the same uncommitted row:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM t_test WHERE id=1;  -- reads 'Tom' before commit

2. READ COMMITTED

This level prevents dirty reads but allows non‑repeatable reads. Using SLEEP to control timing, the first transaction updates a row without committing; the second transaction reads the old value, then after the first commits it reads the new value.

# 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 updated row
SELECT SLEEP(5) FROM dual;  -- pause for Transaction 2
COMMIT;

# Transaction 2
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM t_test WHERE id=1;  -- reads original 'John'
SELECT SLEEP(10) FROM dual;  -- wait for commit
SELECT * FROM t_test WHERE id=1;  -- now reads 'Tom'

3. REPEATABLE READ

MySQL's default level, it guarantees that repeated reads within the same transaction return the same result, eliminating non‑repeatable reads but not phantom reads.

# 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;  -- sees 'Tom'
SELECT SLEEP(5) FROM dual;
COMMIT;

# Transaction 2
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM t_test WHERE id=1;  -- always sees original 'John' until commit
SELECT SLEEP(10) FROM dual;
SELECT * FROM t_test WHERE id=1;  -- still sees 'John' because repeatable read prevents seeing the new row

Phantom read demonstration: Transaction 1 inserts a new row (id=2) but does not commit; Transaction 2 cannot see the row, and an attempt to insert the same id results in a duplicate‑key error, illustrating the phantom‑read problem.

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

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

4. SERIALIZABLE

The highest isolation level; transactions are executed as if they were serialized, preventing phantom reads but incurring heavy locking and reduced concurrency.

# 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
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM t_test WHERE id=2;  -- blocks until Transaction 1 commits
COMMIT;

In summary, MySQL's transaction isolation levels are crucial for data consistency and are common interview topics. The article demonstrated each level with concrete SQL scripts on MySQL 5.7 using the InnoDB engine.