What are dirty reads, non-repeatable reads and phantom reads?
SHORT ANSWER
A dirty read sees another transaction's uncommitted data. A non-repeatable read gets a different value for the same row on a second read. A phantom read gets a different set of rows for the same query.
Explain it simply
You are checking a seat chart on IRCTC while others are booking at the same time.
- Dirty read: you see a seat marked "booked", but the booking is later cancelled before payment. You acted on something that never really happened.
- Non-repeatable read: you read seat 12 as free, and when you read it again a minute later in the same session it is booked.
- Phantom read: you ask "list all free seats in coach B" and get 10. Asking again gives 9, because a new row (booking) appeared or disappeared.
Example
sql
-- Transaction A -- Transaction B
BEGIN;
SELECT balance FROM acc WHERE id=1; -- 500
BEGIN;
UPDATE acc SET balance=400 WHERE id=1;
COMMIT;
SELECT balance FROM acc WHERE id=1; -- 400, non-repeatable read
COMMIT;If B had not yet committed and A saw 400, that would be a dirty read.
Difference between the last two: non-repeatable involves an existing row changing (UPDATE), phantom involves rows entering or leaving the result (INSERT or DELETE).
What to say in the interview
"Dirty read means uncommitted data, non-repeatable read means a changed value of the same row, and phantom read means a changed set of rows. Isolation levels progressively prevent them."
Common mistakes
- Mixing up non-repeatable reads (row updated) with phantoms (rows added or removed).