Database Isolation Levels
What is Isolation?
Section titled “What is Isolation?”Isolation is an ACID property that ensures concurrent transactions don’t interfere with each other. Each transaction sees a consistent snapshot of the data, even when other transactions are running simultaneously.
The Isolation Levels Spectrum
Section titled “The Isolation Levels Spectrum”Isolation levels form a spectrum from weakest (most concurrency, most anomalies) to strongest (least concurrency, no anomalies):
Level 1: Read Uncommitted
Section titled “Level 1: Read Uncommitted”Read Uncommitted is the lowest isolation level. Transactions can read uncommitted data from other transactions.
The Problem: Dirty Reads
Section titled “The Problem: Dirty Reads”Example: Transaction 1 updates balance to 200 but hasn’t committed. Transaction 2 reads 200. Then Transaction 1 rolls back. Transaction 2 saw dirty (uncommitted) data that never actually existed!
When to Use: Almost never. Only for read-only analytics where accuracy isn’t critical.
Level 2: Read Committed
Section titled “Level 2: Read Committed”Read Committed prevents dirty reads. Transactions can only read committed data.
How It Works
Section titled “How It Works”Key Characteristic: Each read sees the latest committed value at the time of the read.
The Problem: Non-Repeatable Reads
Section titled “The Problem: Non-Repeatable Reads”Example: Transaction 1 reads balance = 100. Transaction 2 updates it to 200 and commits. Transaction 1 reads again and gets 200. Same transaction, different values!
When to Use: Default in most databases (PostgreSQL, SQL Server). Good balance of consistency and performance.
Level 3: Repeatable Read
Section titled “Level 3: Repeatable Read”Repeatable Read prevents dirty reads and non-repeatable reads. A transaction sees a consistent snapshot throughout its lifetime.
How It Works
Section titled “How It Works”Key Characteristic: Transaction sees the same data on repeated reads, even if other transactions commit changes.
The Problem: Phantom Reads
Section titled “The Problem: Phantom Reads”Example: Transaction 1 counts active orders (gets 5). Transaction 2 inserts a new active order and commits. Transaction 1 counts again (gets 6). A phantom row appeared!
When to Use: When you need consistent reads of the same rows (e.g., calculating totals, validating constraints).
Level 4: Serializable
Section titled “Level 4: Serializable”Serializable is the highest isolation level. Transactions execute as if they ran one at a time (serially).
How It Works
Section titled “How It Works”Key Characteristic: Database ensures transactions produce the same result as if they ran serially (one after another).
When to Use: Critical financial transactions, inventory management, any scenario where perfect consistency is required.
Isolation Level Comparison
Section titled “Isolation Level Comparison”| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Concurrency | Use Case |
|---|---|---|---|---|---|
| Read Uncommitted | ❌ Possible | ❌ Possible | ❌ Possible | ✅ Highest | Almost never |
| Read Committed | ✅ Prevented | ❌ Possible | ❌ Possible | ✅ High | Default (most DBs) |
| Repeatable Read | ✅ Prevented | ✅ Prevented | ❌ Possible | ⚠️ Medium | Consistent reads |
| Serializable | ✅ Prevented | ✅ Prevented | ✅ Prevented | ❌ Lowest | Critical operations |
Real-World Examples
Section titled “Real-World Examples”Example 1: Bank Balance Check (Read Committed)
Section titled “Example 1: Bank Balance Check (Read Committed)”Isolation Level: Read Committed
Why: User wants to see current balance, doesn’t need perfect consistency for a simple read.
Example 2: Inventory Count (Repeatable Read)
Section titled “Example 2: Inventory Count (Repeatable Read)”Isolation Level: Repeatable Read
Why: Need to read inventory multiple times and ensure it doesn’t change between reads.
Example 3: Financial Transfer (Serializable)
Section titled “Example 3: Financial Transfer (Serializable)”Isolation Level: Serializable
Why: Financial transactions require perfect consistency. Can’t have race conditions or anomalies.
LLD ↔ HLD Connection
Section titled “LLD ↔ HLD Connection”How isolation levels affect your code design:
Choosing Isolation Level
Section titled “Choosing Isolation Level”1import psycopg22
3class DatabaseService:4 def __init__(self, connection_string):5 self.conn = psycopg2.connect(connection_string)6
7 def read_balance(self, account_id: int):8 # Read Committed (default) - good for simple reads9 with self.conn.cursor() as cursor:10 cursor.execute(11 "SELECT balance FROM accounts WHERE id = %s",12 (account_id,)13 )14 return cursor.fetchone()[0]15
16 def process_order(self, order_id: int):17 # Repeatable Read - need consistent reads18 self.conn.set_isolation_level(19 psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ20 )21 try:22 with self.conn.cursor() as cursor:23 # Read inventory twice - will get same value24 cursor.execute("SELECT quantity FROM inventory WHERE product_id = %s", (1,))25 qty1 = cursor.fetchone()[0]26
27 # Do validation...28
29 cursor.execute("SELECT quantity FROM inventory WHERE product_id = %s", (1,))30 qty2 = cursor.fetchone()[0]31 # qty1 == qty2 guaranteed!32 finally:33 self.conn.set_isolation_level(34 psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED35 )36
37 def transfer_money(self, from_id: int, to_id: int, amount: float):38 # Serializable - critical operation39 self.conn.set_isolation_level(40 psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE41 )42 try:43 with self.conn.cursor() as cursor:44 cursor.execute(45 "UPDATE accounts SET balance = balance - %s WHERE id = %s",46 (amount, from_id)47 )48 cursor.execute(49 "UPDATE accounts SET balance = balance + %s WHERE id = %s",50 (amount, to_id)51 )52 self.conn.commit()53 except Exception:54 self.conn.rollback()55 raise1import java.sql.*;2
3public class DatabaseService {4 private Connection connection;5
6 public double readBalance(int accountId) throws SQLException {7 // Read Committed (default) - good for simple reads8 try (PreparedStatement stmt = connection.prepareStatement(9 "SELECT balance FROM accounts WHERE id = ?"10 )) {11 stmt.setInt(1, accountId);12 ResultSet rs = stmt.executeQuery();13 if (rs.next()) {14 return rs.getDouble("balance");15 }16 }17 return 0;18 }19
20 public void processOrder(int orderId) throws SQLException {21 // Repeatable Read - need consistent reads22 connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);23 try {24 // Read inventory twice - will get same value25 try (PreparedStatement stmt = connection.prepareStatement(26 "SELECT quantity FROM inventory WHERE product_id = ?"27 )) {28 stmt.setInt(1, 1);29 ResultSet rs1 = stmt.executeQuery();30 int qty1 = rs1.next() ? rs1.getInt("quantity") : 0;31
32 // Do validation...33
34 ResultSet rs2 = stmt.executeQuery();35 int qty2 = rs2.next() ? rs2.getInt("quantity") : 0;36 // qty1 == qty2 guaranteed!37 }38 } finally {39 connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);40 }41 }42
43 public void transferMoney(int fromId, int toId, double amount) throws SQLException {44 // Serializable - critical operation45 connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);46 try {47 connection.setAutoCommit(false);48 try (PreparedStatement stmt1 = connection.prepareStatement(49 "UPDATE accounts SET balance = balance - ? WHERE id = ?"50 )) {51 stmt1.setDouble(1, amount);52 stmt1.setInt(2, fromId);53 stmt1.executeUpdate();54 }55 try (PreparedStatement stmt2 = connection.prepareStatement(56 "UPDATE accounts SET balance = balance + ? WHERE id = ?"57 )) {58 stmt2.setDouble(1, amount);59 stmt2.setInt(2, toId);60 stmt2.executeUpdate();61 }62 connection.commit();63 } catch (SQLException e) {64 connection.rollback();65 throw e;66 }67 }68}Deep Dive: Advanced Isolation Considerations
Section titled “Deep Dive: Advanced Isolation Considerations”Deadlocks: The Hidden Cost of High Isolation
Section titled “Deadlocks: The Hidden Cost of High Isolation”Deadlock occurs when two transactions wait for each other’s locks indefinitely.
Deadlock Scenario
Section titled “Deadlock Scenario”Production Impact:
- Frequency: Deadlocks occur ~0.01-0.1% of transactions in high-concurrency systems
- Detection: Databases detect deadlocks automatically (usually less than 1 second)
- Recovery: One transaction aborted, retried by application
- Cost: Aborted transaction wasted work, retry overhead
Deadlock Prevention Strategies
Section titled “Deadlock Prevention Strategies”Strategy 1: Lock Ordering
Pattern: Always acquire locks in the same order.
Example:
1def transfer_money(self, from_id, to_id, amount):2 # Always lock lower ID first3 first_id = min(from_id, to_id)4 second_id = max(from_id, to_id)5
6 with self.lock(first_id):7 with self.lock(second_id):8 # Transfer logicBenefit: Prevents circular wait → no deadlocks
Strategy 2: Lock Timeout
Pattern: Set timeout on lock acquisition.
Example:
1def transfer_money(self, from_id, to_id, amount):2 try:3 # Try to acquire locks with timeout4 if not self.try_lock(from_id, timeout=5):5 raise LockTimeout("Could not acquire lock")6 if not self.try_lock(to_id, timeout=5):7 raise LockTimeout("Could not acquire lock")8 # Transfer logic9 finally:10 self.unlock(from_id)11 self.unlock(to_id)Benefit: Fails fast instead of deadlocking
Strategy 3: Reduce Isolation Level
Pattern: Use lower isolation when possible.
Example:
- Serializable: Highest deadlock risk
- Repeatable Read: Medium deadlock risk
- Read Committed: Lower deadlock risk (fewer locks)
Trade-off: Lower isolation = fewer deadlocks but more anomalies
Isolation Level Performance Impact
Section titled “Isolation Level Performance Impact”Lock Contention Analysis
Section titled “Lock Contention Analysis”Read Committed:
- Locks: Short-lived (released after read)
- Contention: Low
- Throughput: High (10K-50K TPS)
- Deadlock Risk: Low
Repeatable Read:
- Locks: Held for transaction duration
- Contention: Medium
- Throughput: Medium (5K-20K TPS)
- Deadlock Risk: Medium
Serializable:
- Locks: Range locks, held for transaction duration
- Contention: High
- Throughput: Low (1K-5K TPS)
- Deadlock Risk: High
Production Benchmark (PostgreSQL):
| Isolation Level | Read Latency | Write Latency | Throughput | Deadlock Rate |
|---|---|---|---|---|
| Read Committed | 5ms | 10ms | 20K TPS | 0.01% |
| Repeatable Read | 8ms | 15ms | 10K TPS | 0.05% |
| Serializable | 15ms | 30ms | 3K TPS | 0.2% |
Key Insight: Each isolation level increase costs 2-3x in performance.
Snapshot Isolation: The Middle Ground
Section titled “Snapshot Isolation: The Middle Ground”Snapshot Isolation is used by many databases (PostgreSQL, MySQL InnoDB) as their default “Repeatable Read” implementation.
How it works:
- Each transaction sees a snapshot of data at transaction start
- Writes create new versions (multi-version concurrency control - MVCC)
- No locks for reads (read from snapshot)
- Locks only for writes (prevent conflicts)
Benefits:
- ✅ No read locks: Readers don’t block writers
- ✅ No write locks: Writers don’t block readers (until commit)
- ✅ Better concurrency: Higher throughput than locking
Trade-offs:
- ⚠️ Storage overhead: Must store multiple versions
- ⚠️ Vacuum required: Old versions must be cleaned up
Production Example: PostgreSQL
- Uses MVCC for all isolation levels
- Vacuum process: Runs periodically to clean old versions
- Performance: 2-5x better than locking-based isolation
Read Phenomena: Detailed Analysis
Section titled “Read Phenomena: Detailed Analysis”Dirty Read: Real-World Impact
Section titled “Dirty Read: Real-World Impact”Scenario: Financial reporting
Impact:
- Financial reports: Wrong numbers → regulatory issues
- Analytics: Incorrect insights → bad decisions
- Auditing: Compliance violations
Prevention: Read Committed or higher
Non-Repeatable Read: Business Logic Impact
Section titled “Non-Repeatable Read: Business Logic Impact”Scenario: Inventory validation
Impact:
- Overselling: Sell more than available
- Double-booking: Book same resource twice
- Race conditions: Business logic fails
Prevention: Repeatable Read or higher
Phantom Read: Aggregation Impact
Section titled “Phantom Read: Aggregation Impact”Scenario: Count operations
Impact:
- Statistics: Wrong averages, totals
- Reports: Incorrect aggregations
- Business metrics: Wrong KPIs
Prevention: Serializable (only level that prevents phantoms)
Isolation Level Selection: Production Guidelines
Section titled “Isolation Level Selection: Production Guidelines”Decision Framework
Section titled “Decision Framework”Production Rules:
- Start with Read Committed (default, works for 90% of cases)
- Upgrade to Repeatable Read if you read same row twice
- Upgrade to Serializable only if phantoms cause problems
- Monitor deadlock rate - if high, consider lowering isolation
Database-Specific Isolation Implementations
Section titled “Database-Specific Isolation Implementations”PostgreSQL Isolation Levels
Section titled “PostgreSQL Isolation Levels”Read Committed (Default):
- Uses MVCC (Multi-Version Concurrency Control)
- Snapshot: Taken at start of each statement
- Locks: Only for writes
- Performance: Excellent
Repeatable Read:
- Uses MVCC with transaction-level snapshot
- Snapshot: Taken at transaction start
- Locks: Only for writes
- Note: Actually prevents phantoms (stricter than SQL standard!)
Serializable:
- Uses Serializable Snapshot Isolation (SSI)
- Detection: Detects serialization conflicts
- Abort: Aborts conflicting transactions
- Performance: Good (better than locking-based)
MySQL InnoDB Isolation Levels
Section titled “MySQL InnoDB Isolation Levels”Read Committed:
- Uses MVCC
- Snapshot: Per statement
- Performance: Good
Repeatable Read (Default):
- Uses MVCC with transaction snapshot
- Gap locks: Prevents phantoms (stricter than standard!)
- Performance: Good
Serializable:
- Uses locking (not MVCC)
- Locks: All reads acquire shared locks
- Performance: Poor (much slower)
Key Difference: MySQL’s Repeatable Read is stricter than PostgreSQL’s!
Production Best Practices
Section titled “Production Best Practices”Practice 1: Keep Transactions Short
Section titled “Practice 1: Keep Transactions Short”Rule: Minimize transaction duration to reduce lock contention.
Bad:
1# BAD: Long transaction2def process_order(order):3 with transaction():4 validate_order(order) # External API call (slow!)5 reserve_inventory(order)6 charge_payment(order)7 # Transaction held during slow API callGood:
1# GOOD: Short transaction2def process_order(order):3 # Do slow work outside transaction4 validation_result = validate_order(order) # Outside transaction5
6 # Short transaction7 with transaction():8 reserve_inventory(order)9 charge_payment(order)Benefit: Reduces lock time → fewer deadlocks, better throughput
Practice 2: Use Appropriate Isolation Per Operation
Section titled “Practice 2: Use Appropriate Isolation Per Operation”Pattern: Different isolation levels for different operations.
Example:
1class OrderService:2 def get_order(self, order_id):3 # Read Committed - simple read4 return self.db.read(order_id, isolation='READ_COMMITTED')5
6 def validate_inventory(self, product_id):7 # Repeatable Read - need consistent reads8 return self.db.read(product_id, isolation='REPEATABLE_READ')9
10 def transfer_money(self, from_id, to_id, amount):11 # Serializable - critical operation12 return self.db.transfer(from_id, to_id, amount, isolation='SERIALIZABLE')Benefit: Optimize each operation for its needs
Practice 3: Monitor Isolation-Level Metrics
Section titled “Practice 3: Monitor Isolation-Level Metrics”What to Monitor:
- Deadlock rate: Should be less than 0.1%
- Lock wait time: P95 should be less than 100ms
- Transaction duration: P95 should be less than 1s
- Rollback rate: High rate indicates problems
Production Monitoring:
1class IsolationMonitor:2 def track_transaction(self, isolation_level, duration, deadlocked):3 self.metrics.increment(f'transaction.{isolation_level}.total')4 self.metrics.histogram(f'transaction.{isolation_level}.duration', duration)5
6 if deadlocked:7 self.metrics.increment(f'transaction.{isolation_level}.deadlock')8 self.alert_on_deadlock(isolation_level)Key Takeaways
Section titled “Key Takeaways”What’s Next?
Section titled “What’s Next?”Now that you understand isolation levels, let’s explore how to scale databases to handle more load:
Next up: Scaling Databases — Learn about read replicas, connection pooling, and scaling strategies.