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.
Understanding Transaction Isolation Levels Reference
Databases handle overlap with locks, versioning, or both. A lock can make one transaction wait while another transaction finishes using a row or a range of rows. Versioning, often called multi version concurrency control, keeps older committed versions of data.
A reader can then use a stable earlier version while a writer creates a newer one. This reduces waiting for read heavy workloads.
The important detail is that isolation is not simply a list of permissions. It is a set of rules implemented by a database engine, and the exact behavior can differ between products.
A snapshot is a view of the database at a particular point in time. Under snapshot isolation, a transaction commonly reads from the snapshot that existed when it began. It will not suddenly see another transaction's later committed changes in its ordinary reads.
This seems close to serializable behavior, but it has an important gap called write skew. Two transactions can each read the same condition, update different rows, then both commit. Each update looks safe by itself, yet together they can break a rule.
For example, a hospital schedule may require at least one doctor to remain on call. Two doctors can each see that the other is on call, then each mark their own row as off call. No row was overwritten, but the final schedule is invalid.
Serializable isolation protects database rules that depend on more than one row. Systems may achieve it by locking ranges, tracking read and write dependencies, or detecting conflicts and aborting one transaction. An aborted transaction is not necessarily an error in the application.
It can mean the database prevented an unsafe result. The application should usually retry a transaction that fails because of a serialization conflict. Retrying must be designed carefully.
A payment request, email, or message sent outside the transaction must not happen twice. Applications often use an idempotency key so repeating a request does not repeat its real world effect.
Students often meet these issues in ticket booking, bank transfers, inventory counts, course registration, and collaborative editing. Checking that a seat is available before reserving it is not enough if many users can check at once. The reservation update needs a safe transaction design.
Learn to describe each example as reads, decisions, writes, commits, and possible rollbacks. Track which rows or query ranges are involved, not just individual values. Notice the difference between a result that is temporarily surprising and a result that violates a business rule.
Higher isolation can mean more waiting, more aborted transactions, or lower throughput. The best choice depends on the rule being protected and on whether the application can safely retry work.