Interview › Databases & Caching
What is the difference between a dirty read, non-repeatable read, and phantom read?
Databases & Caching · Intermediate level
Answer
A dirty read sees uncommitted data, a non-repeatable read sees the same row change between reads in one transaction, and a phantom read sees the result set of a predicate change because rows were inserted, deleted, or updated by another transaction.
Technical explanation
Stronger isolation can improve correctness but may increase blocking or retry requirements.
Deadlocks are handled by aborting a victim transaction; the application must retry the whole transaction safely.
Short transactions, consistent lock ordering, proper indexes, and idempotent retry logic are essential.
Hands-on example
Safe transfer example:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT id FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
On serialization failure or deadlock, retry the whole transaction, not just one statement.
Check how well your resume matches the role with our free resume checker— match score, ATS check, and the skills you're missing.
More Databases & Caching interview questions
- What is Amazon RDS, and what does it manage for you versus self-managed databases?
- What database engines does RDS support?
- What is the difference between RDS and Aurora?
- What is Multi-AZ in RDS, and how does automatic failover work?
- How long does an RDS Multi-AZ failover typically take, and what triggers it?
- What is the difference between Multi-AZ and a read replica?
- When would you use a read replica, and can it become a standalone database?
- Can a read replica be in a different region, and why would you do that?