Understanding MySQL Transaction Isolation Levels: Dirty Read, Non-Repeatable Read, and Phantom Read

MySQL defines four transaction isolation levels, which determine how concurrent transactions may encounter problems such as dirty reads, non‑repeatable reads, and phantom reads.

Dirty Read

Consider two transactions, A and B. Transaction A first queries the row with id=1 and reads name=Ada, age=16 . Transaction B then updates the same row, setting age=18 , but does not commit. When Transaction A queries the same row again, it sees the age changed to 18. Because the change was made by another uncommitted transaction, the inconsistency is called a dirty read.

Non-Repeatable Read

Again using two transactions, Transaction A reads the row with id=1 . Transaction B then updates that row and commits the change. When Transaction A reads the same row again, it sees the new value (e.g., age 18 instead of 16). The difference between the two reads, caused by another transaction’s committed update, is known as a non‑repeatable read.

Phantom Read

In Transaction A a range query returns only one row that satisfies the condition. Transaction B inserts a new row that also satisfies the same condition and commits. When Transaction A repeats the range query, it now sees an additional row. This phenomenon, where new rows appear in subsequent reads, is called a phantom read.

Summary

Non‑repeatable reads involve updates or deletions, while phantom reads involve inserts. All three issues—dirty read, non‑repeatable read, and phantom read—are consistency problems in database reads, where a transaction observes different data in successive reads.