Isolation level comparison: which level disallows dirty reads and nonrepeatable reads, but still allows phantom reads?
-
ARead committed
-
BRead uncommitted
-
CRepeatable read
-
DSerializable
Answer
Correct Answer: Repeatable read
Explanation
Introduction / Context:Understanding which anomalies are blocked at each isolation level is fundamental for designing correct transactional behavior under concurrency.
Given Data / Assumptions:
- Dirty read must be disallowed.
- Nonrepeatable read must be disallowed.
- Phantom reads may still occur.
Concept / Approach:Repeatable read ensures that if a transaction reads a row twice, the values do not change between reads (no dirty or nonrepeatable reads). It does not, however, lock ranges in a way that always prevents new qualifying rows from appearing, so phantom reads can still occur (engine specifics aside).
Step-by-Step Solution:
Read uncommitted: allows all anomalies — not acceptable.Read committed: blocks dirty reads only — still allows nonrepeatable and phantom reads.Repeatable read: blocks dirty and nonrepeatable — permits phantoms.Serializable: blocks all three — too strong for the stated condition.Verification / Alternative check:Refer to ANSI SQL isolation definitions; behavior matches the description, noting DBMS nuances (e.g., Next-Key locks may reduce phantoms).
Why Other Options Are Wrong:Read committed and read uncommitted are too permissive; serializable is stricter than required.
Common Pitfalls:Thinking repeatable read also blocks phantoms universally; that typically requires serializable or range locking.
Final Answer:Repeatable read