What's changed: Initial version
4.3Deadlock and isolation levels
Covers detecting deadlock via the wait-for graph and countermeasures, the four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—and how they correspond to the three read anomalies dirty read, non-repeatable read, and phantom read, plus MVCC, building the judgment needed to choose the optimal isolation level from the balance between consistency and throughput.
For a DBA operating an order-processing system where an inventory-aggregation batch and order processing run concurrently, "which isolation level to choose" is one of the most important judgments, balancing the risk of data inconsistency against overall system throughput. The strictest level, SERIALIZABLE, prevents all read anomalies but sacrifices concurrency significantly; the loosest, READ UNCOMMITTED, is fast but even permits dirty reads. This section teaches the judgment needed to identify which kinds of inconsistency the business can tolerate and choose the optimal isolation level, along with how to handle deadlock.
4.3.1Deadlock detection and avoidance
- Deadlock is a state in which two or more transactions each wait indefinitely for the other to release a lock it holds, so that neither can proceed. The DBMS tracks "who is waiting for whom to release a lock" for each transaction as a wait-for graph, and determines that a deadlock exists the moment a cycle appears in this graph.
- There are two approaches to handling deadlock: detection and avoidance. In the detection approach, the wait-for graph is checked periodically for cycles, and once one is found, one of the involved transactions is chosen as the victim and rolled back so the other can proceed. In the avoidance approach, before a lock request is granted, a rule based on, e.g., timestamp order (such as the Wait-Die or Wound-Wait scheme) decides whether to grant the request or abort it immediately, so that a cycle never forms in the first place.
4.3.2The four isolation levels and three read anomalies
- Three read anomalies: a dirty read occurs when a transaction reads a change made by another transaction that has not yet committed (and might still be rolled back). A non-repeatable read occurs when the same row, read twice within one transaction, comes back with a different value because another transaction committed an update to it in between. A phantom read occurs when the same query condition, executed twice, returns a different set (count) of rows because another transaction inserted or deleted rows matching the condition in between.
- The four isolation levels are defined by how many of these three anomalies each one permits: READ UNCOMMITTED (permits all three: dirty read, non-repeatable read, and phantom read) < READ COMMITTED (prevents dirty reads, but non-repeatable reads and phantom reads can still occur) < REPEATABLE READ (prevents dirty reads and non-repeatable reads, but phantom reads can still occur) < SERIALIZABLE (prevents all three anomalies). Higher levels increase consistency but widen the scope of locking held, reducing concurrency.
The correspondence table between isolation levels and read anomalies is most-tested: READ UNCOMMITTED = all three (dirty read, non-repeatable read, phantom read) can occur; READ COMMITTED = prevents only dirty reads (non-repeatable reads and phantom reads can still occur); REPEATABLE READ = prevents dirty reads and non-repeatable reads (phantom reads can still occur); SERIALIZABLE = prevents all three. Watch for the misconception that "REPEATABLE READ also prevents phantom reads" (under the standard definition, REPEATABLE READ does not prevent phantom reads).
Suppose a DBA for an order-processing system is weighing two requirements. (1) The inventory-aggregation batch wants consistent aggregation using the values as of the batch's start, even if other transactions update inventory records while it runs (the same row read multiple times should not change value). (2) However, if rows within the aggregation scope are newly inserted while the batch runs, the DBA wants to avoid control so strict that it significantly harms overall system throughput. The DBA first identifies that requirement (1) calls for "the same row's value not changing between reads"—that is, preventing non-repeatable reads. READ COMMITTED does not satisfy this, since non-repeatable reads can still occur under it, while SERIALIZABLE prevents non-repeatable reads and phantom reads alike, but at the cost of a wider locking scope and a larger impact on throughput. Since requirement (2) lets the DBA judge that "strictly preventing new rows from appearing or disappearing (phantom reads) is not required," the DBA chooses REPEATABLE READ—this prevents dirty reads and non-repeatable reads while tolerating phantom reads, securing more concurrency than SERIALIZABLE, an appropriately sized choice for the requirements. A common mistake to avoid here is assuming that "the name REPEATABLE READ implies reads are fully reproducible, so it must also prevent phantom reads"—REPEATABLE READ prevents a given row's value from changing (non-repeatable read) but, under the standard definition, does not prevent the row set itself from growing or shrinking (phantom read), so if strict handling of rows appearing or disappearing within the aggregation scope is required, the DBA must either raise the level to SERIALIZABLE or separately verify the behavior of an implementation using MVCC (multi-version concurrency control, an approach based on snapshots in which reads do not block writes).
| Isolation level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| READ UNCOMMITTED | Can occur | Can occur | Can occur |
| READ COMMITTED | Prevented | Can occur | Can occur |
| REPEATABLE READ | Prevented | Prevented | Can occur |
| SERIALIZABLE | Prevented | Prevented | Prevented |
Trap: "REPEATABLE READ, as its name suggests, fully guarantees read reproducibility, so it also prevents phantom reads" is wrong—under the standard definition, REPEATABLE READ prevents dirty reads and non-repeatable reads, but not phantom reads (the row set growing or shrinking). Preventing phantom reads as well requires SERIALIZABLE. Also wrong (from the previous section): "following two-phase locking means deadlock cannot occur"—wait-for-graph-based detection or an avoidance scheme is separately required.
4.3.3Section summary
- Deadlock is detected via a cycle in the wait-for graph, resolved by rolling back a chosen victim, or prevented up front via an avoidance scheme
- Isolation levels grow stricter in the order READ UNCOMMITTED < READ COMMITTED (prevents dirty reads) < REPEATABLE READ (+ prevents non-repeatable reads) < SERIALIZABLE (+ prevents phantom reads)
- Identify which anomalies the business can tolerate and choose the level by balancing against throughput, taking MVCC and similar mechanisms into account
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. An inventory-aggregation batch requires that the value of the same row not change across multiple reads even if other transactions update it during aggregation, but does not need to strictly prevent new rows from being added within the aggregation scope (phantom reads). Which isolation level satisfies this requirement while limiting the impact on throughput?
Q2. A transaction read a value changed by another transaction that had not yet committed, and because that other transaction was later rolled back, the reading transaction continued processing based on an incorrect value. Which combination of this phenomenon and the minimum isolation level needed to prevent it is most appropriate?
Q3. A DBMS manages the wait relationships among transactions as a wait-for graph. Which condition is most appropriate for determining that a deadlock has occurred?
Keep track of your progress
The full study guide is free to read. Sign up free to practice with the question bank, track what you have read, review your mistakes, and highlight passages.