Dirty Read vs Non-Repeatable Read: What is the Difference?
Learn the difference between dirty reads and non-repeatable reads, which isolation levels prevent each, and see SQL examples.
Expected Interview Answer
A dirty read happens when a transaction reads data written by another transaction that has not yet committed, so it can read a value that later gets rolled back and never really existed; a non-repeatable read happens when a transaction re-reads the same row twice and gets two different values because another transaction committed a change in between.
Dirty reads occur only under weak isolation (Read Uncommitted) because the reader is allowed to see uncommitted, possibly-doomed writes from another transaction. Non-repeatable reads occur under a stronger level (Read Committed) that blocks dirty reads but still lets a second SELECT within the same transaction see a row that a concurrent, already-committed transaction has since modified. The fix for dirty reads is raising isolation to at least Read Committed; the fix for non-repeatable reads is raising isolation further to Repeatable Read or Snapshot, which locks or versions the rows a transaction has already read.
- Clarifies which isolation level stops which anomaly
- Prevents acting on data that may be rolled back
- Keeps a transaction’s own view of a row internally consistent
- Guides correct isolation-level choice for a workload
AI Mentor Explanation
A dirty read is like a commentator announcing a batter is out the instant the fielder appeals, before the third umpire has confirmed it — if the review overturns the appeal, the commentator reported something that never actually happened. A non-repeatable read is different: the commentator correctly reports the score as 120 for 2 at the start of an over, checks again two balls later after a confirmed boundary, and now reports 124 for 2 — both readings were true at the time, they just changed because a confirmed event happened in between.
Step-by-Step Explanation
Step 1
Identify the isolation level in play
Read Uncommitted permits dirty reads; Read Committed blocks them but still allows non-repeatable reads.
Step 2
Trace a dirty read
Transaction A reads a row Transaction B has modified but not committed; if B rolls back, A acted on phantom data.
Step 3
Trace a non-repeatable read
Transaction A reads a row twice; between the reads, Transaction B commits an update, so A’s two reads differ.
Step 4
Pick the fix
Raise isolation to Read Committed to stop dirty reads, or to Repeatable Read/Snapshot to stop non-repeatable reads too.
What Interviewer Expects
- Precise definition distinguishing uncommitted vs committed intervening writes
- Mapping each anomaly to the isolation level that permits or prevents it
- A concrete example for each anomaly
- Awareness of the trade-off between stronger isolation and concurrency/throughput
Common Mistakes
- Treating dirty read and non-repeatable read as the same phenomenon
- Forgetting that non-repeatable reads involve only committed data
- Not naming the isolation levels that prevent each anomaly
- Ignoring the performance cost of stricter isolation levels
Best Answer (HR Friendly)
“A dirty read means you saw data that another transaction had not actually finished committing, so it could vanish if that transaction rolls back. A non-repeatable read means you read the same row twice in one transaction and got two different, both-valid answers because someone else’s committed change landed in between. Higher isolation levels progressively rule each of these out, at the cost of more locking.”
Code Example
-- Session A
BEGIN TRANSACTION;
UPDATE Accounts SET balance = 900 WHERE account_id = 1;
-- not yet committed
-- Session B (isolation level READ UNCOMMITTED)
SELECT balance FROM Accounts WHERE account_id = 1;
-- reads 900, an uncommitted value
-- Session A
ROLLBACK;
-- Session B's 900 was a dirty read; the true value is unchanged-- Session B (isolation level READ COMMITTED)
BEGIN TRANSACTION;
SELECT balance FROM Accounts WHERE account_id = 1; -- returns 1000
-- Session A commits a change in between
UPDATE Accounts SET balance = 1100 WHERE account_id = 1;
COMMIT;
-- Session B, same transaction, reads again
SELECT balance FROM Accounts WHERE account_id = 1; -- returns 1100
COMMIT;Follow-up Questions
- Which isolation level prevents both dirty reads and non-repeatable reads?
- What is a phantom read and how does it differ from a non-repeatable read?
- How does Snapshot Isolation avoid non-repeatable reads without heavy locking?
- What is the performance cost of moving from Read Committed to Repeatable Read?
MCQ Practice
1. A dirty read occurs when a transaction reads:
A dirty read reads uncommitted data from another transaction, which may later be rolled back and never truly exist.
2. Which isolation level is the minimum needed to eliminate dirty reads?
Read Committed ensures a transaction only ever reads data that has already been committed, eliminating dirty reads.
3. A non-repeatable read specifically involves:
A non-repeatable read happens when a second read of the same row returns a different value because another transaction committed an update in between.
Flash Cards
What is a dirty read? — Reading another transaction’s uncommitted, possibly-doomed data.
What is a non-repeatable read? — Re-reading the same row and getting a different, already-committed value.
Which isolation level stops dirty reads? — Read Committed and above.
Which isolation level stops non-repeatable reads? — Repeatable Read or Snapshot Isolation and above.