🟢 Junior Level

Isolation levels determine how strictly transactions protect each other from interference. There are 4 levels, from weakest to strictest:

1. Read Uncommitted

The weakest level. A transaction can see even uncommitted changes from other transactions. Allows all anomalies.

2. Read Committed

A transaction sees only committed data. The same query within a transaction may return different results.

3. Repeatable Read

Guarantees that data read once will not change until the end of the transaction.

4. Serializable

The strictest level. Transactions execute as if there is no concurrency at all.

Anomaly Table

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prohibited	Possible	Possible
Repeatable Read	Prohibited	Prohibited	Possible*
Serializable	Prohibited	Prohibited	Prohibited

* In MySQL, Repeatable Read also prevents phantom reads.

Snapshot = an instant copy of all database data at a specific point in time. A transaction sees data as it was at the moment of its start (or the moment of the last query — depending on the isolation level).

When NOT to Use Serializable

1. Read-heavy reporting (>1000 TPS on reads) — excessive overhead
2. Long batch operations (>1 min) — high risk of serialization failures
3. High write contention (>50 concurrent writers on the same data) — constant rollbacks

---

🟡 Middle Level

Detailed Description of Each Level

Read Uncommitted

• A transaction sees uncommitted changes from other transactions
• Problem: If the other transaction does a ROLLBACK, you get data that “never existed”
• Usage: Extremely rare, for approximate statistics
• PostgreSQL: This level is absent — automatically replaced with Read Committed. This is an architectural decision, not a setting — present in all PG versions.

Read Committed

• The most popular default level (PostgreSQL, Oracle, SQL Server), because it provides sufficient protection from dirty reads with minimal overhead — no read locks, no serialization failures.
• Within a single transaction, two identical SELECTs may return different results
• Implemented via MVCC — a data snapshot is created before each query

Repeatable Read

• The data snapshot is created once — at the first query in the transaction
• MySQL (InnoDB): Default level. Prevents phantoms through Next-Key Locking (Gap Locks)
• PostgreSQL: On update conflicts, throws a serialization error

Serializable

• The strictest and most expensive level
• PostgreSQL: Uses SSI (Serializable Snapshot Isolation) — tracks dependencies between transactions
• The application must be ready to retry on serialization errors

Performance Impact

The higher the isolation level:

• The more locks or row versions the DB must maintain
• The higher the probability of conflicts and rollbacks
• On Serializable, retry logic must be implemented

When to Use Which Level

• Read Committed: 90% of cases, default for most applications
• Repeatable Read: When data stability within a transaction is needed (reports)
• Serializable: Financial operations, booking — where the cost of error is high

---

🔴 Senior Level

Implementation in Different DBMS — Key Differences

PostgreSQL Approach

• Default: Read Committed
• Uses MVCC with snapshot isolation
• On Read Committed: new snapshot before each statement
• On Repeatable Read: one snapshot for the entire transaction, on conflict — ERROR: could not serialize access due to concurrent update
• Serializable via SSI (Serializable Snapshot Isolation): dependency graph, cycle detection, selective abortion

MySQL (InnoDB) Approach

• Default: Repeatable Read (historically for statement-based replication)
• Next-Key Locking = Record Lock + Gap Lock
• Prevents phantoms on Repeatable Read (goes beyond ANSI SQL)
• Higher deadlock probability due to gap locks

MVCC Internal Mechanics

PostgreSQL:

• Each row tuple has xmin (transaction ID that inserted) and xmax (transaction ID that deleted/updated)
• Visibility rules: a transaction sees a row if xmin was committed before the snapshot and xmax is not yet committed
• Old versions are stored in the same pages → bloat → VACUUM cleanup

MySQL/InnoDB:

• Old versions are stored in the undo log in rollback segments
• More efficient space usage, but more complex recovery

Serializable Snapshot Isolation (PostgreSQL)

SSI tracks:

• RW-dependencies: when transaction T1 reads data that T2 later modifies
• Conflict detection: a dependency graph is built, cycles are searched for
• Transaction abort: when a dangerous structure is detected, one transaction is aborted with SQLSTATE 40001

Application MUST implement retry logic:

@Retryable(value = {CannotSerializeTransactionException.class}, maxAttempts = 3)
@Transactional(isolation = Isolation.SERIALIZABLE)
public void criticalOperation() { ... }

Performance Benchmarks & Trade-offs

Level	Throughput	Latency	Conflict Rate	Use Case
Read Committed	Highest	Lowest	~0%	General purpose
Repeatable Read	Medium	Medium	Low-Medium	Reports, batch processing
Serializable	Lowest	Highest	High	Financial, booking

Production Considerations

• Connection pool exhaustion: Serializable may require more retries → more connections
• Long-running transactions: On RR and Serializable, old row versions are held longer → bloat
• Monitoring: Track serialization failure rate, tune accordingly
• Alternative: Use pessimistic locking (SELECT FOR UPDATE) instead of Serializable for specific hot paths

---

🎯 Interview Cheat Sheet

Must know:

• 4 isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable
• Read Committed — default in PostgreSQL, Oracle, SQL Server; Repeatable Read — default in MySQL
• Read Committed prevents only dirty reads
• Repeatable Read prevents dirty read + non-repeatable read
• Serializable prevents all anomalies but requires retry logic
• PostgreSQL MVCC: new snapshot before each statement on RC, one snapshot on RR
• MySQL InnoDB uses Next-Key Locking (Record + Gap Lock) to prevent phantoms on RR
• Serializable in PostgreSQL uses SSI with dependency graph and selective abort

Common follow-up questions:

• Why doesn’t PostgreSQL have Read Uncommitted? — Architectural decision: MVCC prevents dirty reads for free
• How does Repeatable Read in MySQL differ from PostgreSQL? — MySQL prevents phantoms via Gap Locks, PG via MVCC snapshot
• When to use Serializable? — Financial operations, booking, where the cost of error is high
• What is a serialization failure? — Error 40001 when an RW-dependency cycle is detected in SSI

Red flags (DO NOT say):

• “Repeatable Read works the same in all DBMS” — MySQL and PG differ fundamentally
• “Serializable = just locks” — PostgreSQL uses SSI, not 2PL
• “Read Committed = no anomalies” — non-repeatable and phantom reads are possible

Related topics:

• [[1. Decode each letter of ACID]]
• [[3. What is Read Uncommitted]]
• [[4. What is Read Committed]]
• [[5. What is Repeatable Read]]
• [[6. What is Serializable]]