✏️ Explanatory Question
Level: Advanced — These are the three "read anomalies" that isolation levels are designed to prevent.
These are concurrency problems that can occur when multiple transactions run simultaneously. Each isolation level exists specifically to prevent one or more of them.
| Anomaly | Caused By | Affects | Prevented From |
|---|---|---|---|
| Dirty Read | Reading uncommitted data | A single value | READ COMMITTED+ |
| Non-Repeatable Read | Committed UPDATE mid-transaction | An existing row's value | REPEATABLE READ+ |
| Phantom Read | Committed INSERT/DELETE | The set of rows returned | SERIALIZABLE (or InnoDB RR) |
-- DIRTY READ (only under READ UNCOMMITTED)
-- Txn A:
UPDATE accounts SET balance = 5000 WHERE acc_id = 1; -- not committed yet
-- Txn B (reads the uncommitted 5000):
SELECT balance FROM accounts WHERE acc_id = 1; -- 5000 (dirty!)
-- Txn A: ROLLBACK; -- the 5000 never really existed
-- NON-REPEATABLE READ
-- Txn A:
SELECT balance FROM accounts WHERE acc_id = 1; -- reads 1000
-- Txn B:
UPDATE accounts SET balance = 2000 WHERE acc_id = 1;
COMMIT;
-- Txn A (reads again):
SELECT balance FROM accounts WHERE acc_id = 1; -- now 2000 (changed!)
-- PHANTOM READ
-- Txn A:
SELECT COUNT(*) FROM accounts WHERE balance > 1000; -- returns 5
-- Txn B:
INSERT INTO accounts VALUES (99, 5000);
COMMIT;
-- Txn A (runs again):
SELECT COUNT(*) FROM accounts WHERE balance > 1000; -- returns 6 (phantom!)