Databases & System Design•Part 2: Relational Databases (RDBMS / PostgreSQL / MySQL) (6 - 25)
Dirty Reads vs Non-Repeatable Reads vs Phantom Reads:
In Simple Words:
- Dirty Read: Reading uncommitted data written by another transaction that later rolls back.
- Non-Repeatable Read: Re-reading a row and finding its values updated by another committed transaction.
- Phantom Read: Re-running a range query (
WHERE age > 30) and finding new rows inserted by another committed transaction.
sql
-- Non-Repeatable Read Example:
-- Tx 1:
BEGIN;
SELECT salary FROM employees WHERE id = 5; -- Returns 50,000
-- Tx 2 commits: UPDATE employees SET salary = 60,000 WHERE id = 5; COMMIT;
-- Tx 1 re-reads:
SELECT salary FROM employees WHERE id = 5; -- In Read Committed, returns 60,000!