Intermediate LevelSQL & MySQL
What are Dirty Reads, Non-Repeatable Reads, and Phantom Reads?
❓ Interview Prompt: Explain concurrency read anomalies: Dirty Reads (uncommitted data visibility), Non-Repeatable Reads (row value mutation), and Phantom Reads (new row insertions).
Detailing concurrency read anomalies:
1
Dirty Read: Transaction A reads data modified by Transaction B that has not yet been committed. If Transaction B rolls back, Transaction A holds invalid data!
2
Non-Repeatable Read: Transaction A reads a row. Transaction B updates that row and commits. Transaction A re-reads the row and observes different column values.
3
Phantom Read: Transaction A executes a range query. Transaction B inserts new rows matching the range and commits. Transaction A re-executes the range query and discovers new phantom rows.
Code Implementation Example
sql
-- READ COMMITTED prevents Dirty Reads, but permits Non-Repeatable Reads
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- Returns 100
-- Concurrent transaction updates balance to 200 & commits!
SELECT balance FROM accounts WHERE id = 1; -- Returns 200 (Non-Repeatable Read!)
COMMIT;💡Demonstrating a Non-Repeatable Read under READ COMMITTED isolation.
🎯 Interview Key Takeaway
Select transaction isolation levels based on your application's tolerance for Dirty Reads, Non-Repeatable Reads, and Phantom Reads.
#SQL#Dirty Reads#Phantom Reads#Concurrency Anomalies#Intermediate
Back to all SQL & MySQL Questions→