Transaction isolation levels define how much one database transaction is protected from the effects of other concurrent transactions. This cheat sheet helps college computer science students compare common anomalies, SQL isolation levels, and practical tradeoffs. It is useful when studying database concurrency control, ACID properties, and real system behavior under load.
Key Facts
- A dirty read occurs when T2 reads data written by T1 before T1 commits, so T2 may use a value that is later rolled back.
- A nonrepeatable read occurs when T1 reads the same row twice and gets different values because T2 updated and committed that row between the reads.
- A phantom read occurs when T1 repeats a predicate query and sees a different set of rows because T2 inserted, deleted, or updated matching rows.
- A lost update occurs when T1 and T2 both read the same value, both compute a new value, and the later write overwrites the earlier committed update.
- Read Uncommitted allows dirty reads, nonrepeatable reads, and phantom reads, so it provides the weakest isolation.
- Read Committed prevents dirty reads by allowing a transaction to read only committed data, but nonrepeatable reads and phantom reads may still occur.
- Repeatable Read prevents dirty reads and nonrepeatable reads for rows already read, but phantom behavior depends on the database implementation.
- Serializable makes concurrent transactions behave as if they ran one at a time in some serial order, giving the strongest standard isolation guarantee.
Vocabulary
- Transaction
- A transaction is a sequence of database operations that should complete as one logical unit or have no lasting effect.
- Isolation Level
- An isolation level is a database setting that controls which concurrency effects one transaction can observe from other transactions.
- Dirty Read
- A dirty read happens when a transaction reads another transaction's uncommitted change.
- Phantom Read
- A phantom read happens when repeating a query returns extra or missing rows because another transaction changed rows matching the query condition.
- Snapshot Isolation
- Snapshot isolation lets a transaction read from a consistent committed snapshot while detecting or preventing some write conflicts.
- Serializability
- Serializability is the property that concurrent transactions produce the same final result as some one-at-a-time execution order.
Common Mistakes to Avoid
- Assuming Read Committed prevents all inconsistent reads is wrong because it only prevents reading uncommitted data, not changes committed between two reads.
- Treating Repeatable Read as identical in every database is wrong because systems differ on whether they prevent phantoms, use locks, or use multiversion snapshots.
- Confusing snapshot isolation with full serializability is wrong because snapshot isolation can still allow anomalies such as write skew in some workloads.
- Choosing Serializable for every workload without testing is wrong because stronger isolation can increase blocking, aborts, deadlocks, or retry costs.
- Ignoring retry logic is wrong because high isolation levels and optimistic concurrency schemes may abort transactions that must be safely retried by the application.
Practice Questions
- 1 T1 reads account balance 100, T2 updates the balance to 130 and commits, then T1 reads the balance again as 130. Which anomaly occurred, and which common isolation level prevents it?
- 2 Two transactions each read stock quantity 10. T1 writes 7 after selling 3 units, and T2 writes 8 after selling 2 units. What final quantity is stored, and what anomaly occurred?
- 3 At Read Committed, T1 runs SELECT count(*) FROM orders WHERE status = 'open' and gets 5. T2 inserts a new open order and commits. T1 repeats the query and gets 6. Name the anomaly.
- 4 A bank wants maximum correctness for transfers, but a reporting dashboard mainly needs fast approximate reads of committed data. Explain why these two tasks may use different isolation levels.