Non Repeatable Read

What It Is

A non-repeatable read occurs when a transaction reads the same data item twice within its own lifetime, and gets two different values, because another transaction committed a change to that item in between the two reads. Crucially — unlike a dirty read — the value seen the second time came from a transaction that did commit; there's nothing "dirty" about either read individually. The problem is purely that T1's own view of the data changed mid-transaction, which it never expects if it's supposed to be looking at one consistent snapshot.

Concrete Example — Bank Balance

Account A starts at ₹1000. T1 is a long-running transaction that reads A twice (e.g., to compare a value before and after some other check). T2 deposits ₹500 and commits in between T1's two reads.

Time  Operation             Effect
1     R1(A)                 T1 reads A = 1000   (first read)
2     R2(A)                 T2 reads A = 1000
3     A = 1000 + 500
4     W2(A)                 A is now 1500
5     COMMIT2               T2 commits -- this is a real, permanent, committed change
6     R1(A)                 T1 reads A = 1500 AGAIN, within the SAME transaction   (second read)

T1 read A as 1000 at step 1 and as 1500 at step 6 — two different values for the same item, in the same transaction, with no write to A by T1 itself in between. From T1's point of view, the ground shifted under it mid-transaction, even though nothing "wrong" happened at the database level — T2's update was perfectly legitimate and committed.

Why It's Not a Dirty Read

This is a common point of confusion. In a dirty read, T2 would be reading data from T1 before T1 committed (or vice versa) — the danger is the source transaction might abort. Here, T2's write and commit happened cleanly and completely between T1's two reads; T1 never saw an uncommitted value. The anomaly is entirely about T1's own two reads disagreeing with each other, not about anyone reading data that could later vanish.

Why It Can Still Be a Problem

A transaction that reads the same value twice usually does so because it assumes that value is stable for the duration of its own logic (e.g., "check the balance, do some computation, check the balance again to confirm nothing else changed before finalizing"). If the value can silently shift between those two checks due to another committed transaction, T1's internal logic — built on an assumption of a stable snapshot — can produce an inconsistent or incorrect result, even though every individual read/write/commit involved was itself perfectly valid.

How It's Prevented

Non-repeatable reads are prevented by an isolation level that holds read locks on a data item for the entire duration of the reading transaction (not released immediately after each read), so no other transaction can write to (and commit a change to) that item until the reader finishes. This is a stronger guarantee than merely avoiding dirty reads — Chapter 25 formalizes this as the REPEATABLE READ isolation level and above.

Edge Cases

• If T1 only reads A once in its lifetime, non-repeatable read cannot occur by definition — it specifically requires two reads of the same item within one transaction.
• Non-repeatable read is about an existing row's value changing; it is explicitly distinct from a phantom read (23.13), where the set of rows matching a query condition changes because of a new row being inserted (or an old one removed) — not because an existing row's value was updated.

Key Takeaways / Interview Angle

• Q: What are the two hallmarks of a non-repeatable read? (1) The same transaction reads the same data item twice, and (2) another transaction's committed write lands in between the two reads, causing the values to differ.
• Q: How do you distinguish dirty read from non-repeatable read in an interview? Dirty read = reading a value from an uncommitted transaction (danger: it might abort). Non-repeatable read = reading a value that changed because of a committed transaction, observed via two reads within one transaction.