Understanding MySQL Transaction Isolation Levels and Their Practical Implications

1. Four Transaction Isolation Levels

1.1 READ UNCOMMITTED

READ UNCOMMITTED (also called "read uncommitted") allows a transaction to see data that other transactions have not yet committed. Because the uncommitted data may be rolled back, the data read under this level is called "dirty data" and the problem is known as a dirty read.

1.2 READ COMMITTED

READ COMMITTED (also called "read committed") permits a transaction to read only data that has been committed by other transactions, thus avoiding dirty reads. However, because a transaction can see changes committed by others during its execution, the same query may return different results at different times, which is called a non‑repeatable read.

1.3 REPEATABLE READ

REPEATABLE READ is the default isolation level in MySQL. It prevents non‑repeatable reads but still allows phantom reads, where a later query returns rows that were not present in an earlier query.

Note: The focus of phantom reads differs from non‑repeatable reads; non‑repeatable reads involve changes to existing rows, while phantom reads involve rows being added or deleted, causing the number of rows returned to change.

1.4 SERIALIZABLE

SERIALIZABLE is the highest isolation level. It forces transactions to be ordered so that they do not conflict, eliminating dirty reads, non‑repeatable reads, and phantom reads, but it incurs a performance penalty and is rarely used in practice.

1.5 Summary Table

The following table shows the relationship between each isolation level and the three classic concurrency problems.

Transaction Isolation Level

Dirty Read

Non‑repeatable Read

Phantom Read

READ UNCOMMITTED

✓

✓

✓

READ COMMITTED

✗

✓

✓

REPEATABLE READ

✗

✗

✓

SERIALIZABLE

✗

✗

✗

2. Problems in Concurrent Transactions

2.1 Dirty Read

A transaction reads data that another transaction has written but not yet committed; if the other transaction rolls back, the first transaction has read non‑existent (dirty) data.

2.2 Non‑repeatable Read

Within the same transaction, the same query returns different results at different times because another transaction has modified the data between the two reads.

2.3 Phantom Read

A transaction executes the same query twice and the second execution returns rows that were not present in the first execution, typically because another transaction inserted or deleted rows.

3. Isolation Level in Practice

3.1 Query Current Isolation Level

To view the global and session isolation levels, run:

select @@global.tx_isolation, @@tx_isolation;

The result is shown in the accompanying screenshot.

3.2 Set Isolation Level

Each client connection can set its own isolation level with the following SQL:

set session transaction isolation level [LEVEL];

Available levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

3.3 Dirty Read Demonstration

First create a test table:

-- 创建一个城市表
DROP TABLE IF EXISTS city;
CREATE TABLE city(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(250) NOT NULL
);

Execution steps:

Client A: set session transaction isolation level read uncommitted;

Client A: start transaction;

Client B: start transaction;

Client B: insert into city(name) values('西安');

Client A: select * from city; (sees the uncommitted row)

Client B: rollback;

Client A: select * from city; (row disappears, confirming a dirty read)

The screenshots illustrate that Client A reads the uncommitted row and later sees it disappear after Client B rolls back.

3.4 Non‑repeatable Read Demonstration

Steps:

Client A: set session transaction isolation level read committed;

Client A: start transaction;

Client A: select * from city where id=1;

Client B: start transaction;

Client B: update city set name='长安' where id=1;

Client B: commit;

Client A: select * from city where id=1; (returns a different value)

The result screenshots show that the same query returns different data, demonstrating a non‑repeatable read.

3.5 Phantom Read Demonstration

Steps:

Client A: set session transaction isolation level repeatable read;

Client A: start transaction;

Client A: select * from city where id<5; (returns 1 row)

Client B: start transaction;

Client B: insert into city(id,name) values(2,'北京');

Client B: commit;

Client A: update city set name='京城' where id=2;

Client A: select * from city where id<5; (now returns 2 rows)

The screenshots show that the second query returns an additional row – a phantom row – confirming that phantom reads can occur under REPEATABLE READ.

Conclusion

MySQL provides four transaction isolation levels: READ UNCOMMITTED (prone to dirty reads, non‑repeatable reads, and phantom reads), READ COMMITTED (prone to non‑repeatable reads and phantom reads), REPEATABLE READ (prone to phantom reads), and SERIALIZABLE (eliminates all three). Understanding these levels helps developers choose the appropriate isolation for consistency and performance requirements.

Author Introduction

Wang Lei ("Lei Ge"), with 13 years of programming and framework experience, former 360 technology expert, B‑Station lecturer, CSDN author, Alibaba Cloud expert blogger, and instructor on multiple platforms.