# Isolation & Its Levels

Quick Reference: Data Consistency | CAP Theorem

# Quick Reference

# Clear Definition

Isolation is an ACID property that determines how transaction concurrency is managed. Isolation levels define what data one transaction can see when other transactions are running concurrently. Higher isolation levels prevent more concurrency anomalies but reduce performance.

💡 Key Insight: Isolation is about preventing concurrent transactions from interfering with each other. Higher isolation = fewer anomalies but lower concurrency.

# Core Concepts

# ACID Isolation Levels

# 1. Read Uncommitted (Lowest)

Definition: Transactions can read uncommitted data from other transactions.

Anomalies Allowed:

• ✅ Dirty reads
• ✅ Non-repeatable reads
• ✅ Phantom reads

Use Cases: Rarely used, only for read-only analytics

# 2. Read Committed

Definition: Transactions can only read committed data.

Anomalies Allowed:

• ❌ Dirty reads (prevented)
• ✅ Non-repeatable reads
• ✅ Phantom reads

Use Cases: Default in PostgreSQL, Oracle

# 3. Repeatable Read

Definition: Same query in a transaction always returns same results.

Anomalies Allowed:

• ❌ Dirty reads (prevented)
• ❌ Non-repeatable reads (prevented)
• ✅ Phantom reads

Use Cases: Default in MySQL InnoDB

# 4. Serializable (Highest)

Definition: Transactions execute as if serially, one after another.

Anomalies Allowed:

• ❌ Dirty reads (prevented)
• ❌ Non-repeatable reads (prevented)
• ❌ Phantom reads (prevented)

Use Cases: Critical financial systems

# Read Phenomena

# 1. Dirty Read

Definition: Reading uncommitted data that may be rolled back.

Example:

T1: UPDATE balance SET amount = 1000 WHERE id = 1
T2: SELECT amount FROM balance WHERE id = 1  -- Reads 1000
T1: ROLLBACK  -- T2 read data that was never committed

# 2. Non-Repeatable Read

Definition: Same query returns different results within transaction.

Example:

T1: SELECT amount FROM balance WHERE id = 1  -- Returns 100
T2: UPDATE balance SET amount = 200 WHERE id = 1
T1: SELECT amount FROM balance WHERE id = 1  -- Returns 200 (different!)

# 3. Phantom Read

Definition: Same query returns different number of rows.

Example:

T1: SELECT * FROM orders WHERE total > 100  -- Returns 5 rows
T2: INSERT INTO orders VALUES (..., 150)
T1: SELECT * FROM orders WHERE total > 100  -- Returns 6 rows (phantom!)

# Best Practices

1. Use Appropriate Level: Don't use Serializable unless necessary
2. Default Levels: Understand your database's default
3. Application Logic: Handle isolation at application level when needed
4. Performance: Balance isolation with performance

# Interview Tips

🎯 Interview Focus: Understand isolation levels and read phenomena.

# Common Questions

• "Explain the four isolation levels."
• "What's the difference between non-repeatable read and phantom read?"
• "When would you use Serializable isolation?"

# Quick Reference Summary

Isolation Levels: Control what concurrent transactions can see. Higher isolation prevents more anomalies but reduces concurrency.

Read Phenomena: Dirty reads, non-repeatable reads, phantom reads. Each isolation level prevents different phenomena.

Previous Topic: Data Consistency ←

Next Topic: CAP Theorem →

Back to: Step 3 Overview | Main Index