Page 7: Isolation Levels - Real-World Flexibility

Why Relax ACID Properties?

Problem: Full serializability can be too restrictive Example: Long-running report generation shouldn't block all other transactions

SQL Isolation Levels (Weakest to Strongest)

1. Read Uncommitted (Dirtiest Reads)

• Can read data written by uncommitted transactions

• Risk: Dirty reads (reading data that might be rolled back)

• Use Case: Quick reports where exactness doesn't matter

Example:

CopyT₁: write(A = 500)  (not yet committed)
T₂: read(A)         ← sees 500
T₁: abort           ← A goes back to original value
T₂: continues with wrong value 500

2. Read Committed (Most Common Default)

• Can only read committed data

• Risk: Non-repeatable reads (value changes between reads)

Example:

CopyT₁: read(A) = 1000
T₂: write(A = 2000), commit
T₁: read(A) = 2000  ← different from first read!

3. Repeatable Read

• Once you read a value, it stays same throughout transaction

• Risk: Phantom reads (new rows appear)

Example:

CopyT₁: COUNT(*) WHERE age > 25 = 100 people
T₂: INSERT new person with age 30, commit  
T₁: COUNT(*) WHERE age > 25 = 101 people ← phantom row!

4. Serializable (Strictest)

• Full serializability guaranteed

• Risk: Lower performance due to more blocking

Setting Isolation Levels

CopySET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- or READ COMMITTED, REPEATABLE READ, READ UNCOMMITTED