What are Transaction Isolation Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)?
❓ Interview Prompt: Explain 4 ANSI SQL Transaction Isolation Levels and concurrency anomalies (Dirty Reads, Non-Repeatable Reads, Phantom Reads).
Transaction Isolation Levels control data visibility across concurrent transactions:
| Isolation Level | Dirty Reads Allowed? | Non-Repeatable Reads? | Phantom Reads? |
|---|---|---|---|
| 'READ UNCOMMITTED' | ❌ YES (Dirty Read) | ❌ YES | ❌ YES |
| 'READ COMMITTED' (PostgreSQL Default) | ✅ Protected | ❌ YES | ❌ YES |
| 'REPEATABLE READ' (MySQL InnoDB Default) | ✅ Protected | ✅ Protected | ✅ Protected (via MVCC / Next-Key Locks) |
| 'SERIALIZABLE' | ✅ Protected | ✅ Protected | ✅ Protected (Strict Serial Execution) |
Code Implementation Example
-- Setting transaction isolation level in MySQL InnoDB
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;
COMMIT;Understand transaction isolation level trade-offs to balance concurrency throughput against data read anomalies.