Standard SQL defines four isolation levels. Each level is a superset of the previous — it prevents everything the lower level prevents, plus one more anomaly.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Typical Use Case |
|---|---|---|---|---|
| Read Uncommitted | ✅ Possible | ✅ Possible | ✅ Possible | Approximate analytics, rarely used |
| Read Committed | ❌ Prevented | ✅ Possible | ✅ Possible | Default in PostgreSQL, Oracle, SQL Server |
| Repeatable Read | ❌ Prevented | ❌ Prevented | ⚠️ DB-dependent | Long-running reports, financial reads |
| Serializable | ❌ Prevented | ❌ Prevented | ❌ Prevented | Financial transactions, booking systems |
Note: MySQL's default is Repeatable Read. PostgreSQL's default is Read Committed.
How Databases Implement Isolation
There are two main implementation strategies:
Lock-Based Concurrency Control: Readers and writers acquire locks on rows or ranges. Higher isolation levels hold locks longer and acquire more of them. This is simple but creates contention — a slow transaction blocks others.
Multi-Version Concurrency Control (MVCC): The database keeps multiple versions of each row. Readers see a consistent snapshot of the database as of a point in time, without blocking writers. Writers create new versions rather than overwriting. PostgreSQL, MySQL InnoDB, and Oracle all use MVCC. This is why "readers don't block writers" in modern databases.
Senior insight: MVCC is not free. Old row versions must be cleaned up by a background process (called VACUUM in PostgreSQL). If long-running transactions hold open snapshots, old versions accumulate — a phenomenon called table bloat. A single forgotten transaction left open for hours can cause significant storage growth and query slowdowns.