Master MySQL Transaction Isolation: From Dirty Reads to Phantom Reads with Live Examples

MySQL Transaction Isolation Levels Overview

During interviews many candidates are asked about the four InnoDB transaction isolation levels, the problems each can cause, and to give examples of dirty reads, non‑repeatable reads, and phantom reads.

Isolation Levels and Anomalies

Isolation Level      | Dirty Read | Non‑repeatable Read | Phantom Read
---------------------|-----------|---------------------|-------------
Read Uncommitted     | ✓         | ✓                   | ✓
Read Committed       | ×         | ✓                   | ✓
Repeatable Read      | ×         | ×                   | ✓
Serializable         | ×         | ×                   | ×

Definitions

Read Uncommitted allows a transaction to see changes made by other transactions before they are committed, leading to dirty reads.

Read Committed permits only committed changes to be visible, preventing dirty reads but still allowing non‑repeatable reads.

Repeatable Read (MySQL default) guarantees that rows read twice in the same transaction return the same data, but phantom reads can still occur.

Serializable provides the highest isolation by ordering transactions to avoid all three anomalies, at the cost of higher lock contention.

1. Read Uncommitted (Dirty Read)

Session A sets its isolation level to READ UNCOMMITTED. Session B updates a row (userId=2, age=120) without committing.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE userinfo SET age = 120 WHERE userId = 2; -- no COMMIT

Session A queries the table and sees the uncommitted value (age=120), demonstrating a dirty read. SELECT * FROM userinfo; After Session B rolls back, Session A queries again and the age returns to its original value, confirming the dirty read was temporary.

ROLLBACK;

2. Read Committed (Non‑repeatable Read)

Session A changes its isolation level to READ COMMITTED. Session B updates the same row (age=18) without committing.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE userinfo SET age = 18 WHERE userId = 2; -- no COMMIT

Session A reads the table and still sees the original age (30) because the update is not yet committed. SELECT * FROM userinfo; After Session B commits, Session A reads again and now sees the new age (18), illustrating a non‑repeatable read.

COMMIT;

3. Repeatable Read (Phantom Read Example)

Session A starts a transaction and queries for a row with userId = 3, finding none.

START TRANSACTION;
SELECT * FROM userinfo WHERE userId = 3;

Session B, in a separate transaction, inserts a row with userId = 3 and commits.

START TRANSACTION;
INSERT INTO userinfo (userId, userName, age) VALUES (3, 'Tony', 28);
COMMIT;

Session A, still in its original transaction, queries again for userId = 3 and still finds no row, because Repeatable Read prevents it from seeing the newly committed row – a phantom read. SELECT * FROM userinfo WHERE userId = 3; After Session A commits, the row becomes visible.

Conclusion

Read Uncommitted allows dirty reads; Read Committed eliminates dirty reads but permits non‑repeatable reads; Repeatable Read prevents non‑repeatable reads but can still suffer phantom reads; Serializable removes all three anomalies at the cost of performance. Understanding these behaviors helps design robust concurrent applications.