Nonrepeatable Reads Problem
What you'll learn: You'll understand how the same query can return different results within a single transaction when other transactions modify data concurrently.
What Are Nonrepeatable Reads?
A nonrepeatable read occurs when a transaction reads the same row twice and gets different values because another transaction modified (and committed) that row in between the two reads.
This happens when you're using the Read Committed isolation level, which prevents dirty reads but doesn't lock rows you've already read. Other transactions can still update those rows after your first read.
Real-World Analogy
Imagine you're preparing a financial report. You check an account balance at 10:00 AM and write it down: 500 into that account. When you check the balance again at 10:15 AM to verify your numbers, it now shows $1,500. Your read was "nonrepeatable" — the same data changed mid-task.
Example Scenario
Transaction A: Transaction B:
------------- -------------
BEGIN;
SELECT balance FROM accounts WHERE id = 5;
-- Returns 1000
BEGIN;
UPDATE accounts SET balance = 1500 WHERE id = 5;
COMMIT;
SELECT balance FROM accounts WHERE id = 5;
-- Returns 1500 (different!)
COMMIT;
Transaction A read the same row twice but got different results because Transaction B modified it in between.
Why This Matters
Nonrepeatable reads can cause inconsistencies in business logic. If your transaction relies on stable data (like calculating totals or making decisions based on previous reads), getting different values mid-transaction can lead to incorrect results or logic errors.
Key Takeaway: Nonrepeatable reads occur when a transaction reads the same data twice and gets different results because another concurrent transaction modified it between reads — a problem allowed by Read Committed isolation but prevented by stricter levels.