Transaction A starts and reads all orders for customer 42, finding 3 rows. Transaction B then inserts a 4th order for customer 42 and commits. Transaction A re-runs the same SELECT query. Under REPEATABLE READ, what does Transaction A see?
A3 rows — REPEATABLE READ prevents all changes from other transactions from being visible
B4 rows — the newly inserted row can appear as a phantom read
CTransaction A is blocked until Transaction B commits before it can re-query
DAn error — REPEATABLE READ detects the conflict and aborts Transaction A
This is the phantom read anomaly, which REPEATABLE READ does NOT prevent. REPEATABLE READ guarantees that rows you have already read cannot be modified or deleted by other transactions — those existing rows will look the same if you re-read them. But it does not prevent other transactions from inserting *new* rows that match your WHERE clause. Transaction A's second query may return 4 rows, with the newly inserted row appearing as a 'phantom.' To prevent phantom reads, you need SERIALIZABLE isolation, which adds range locks or serialization conflict detection. Option A describes what SERIALIZABLE provides.
Question 2 Multiple Choice
REPEATABLE READ isolation prevents which of the following anomalies?
ADirty reads and phantom reads
BNon-repeatable reads and phantom reads
CDirty reads and non-repeatable reads
DOnly dirty reads
REPEATABLE READ prevents dirty reads (reading uncommitted data from other transactions) and non-repeatable reads (re-reading the same row and getting different values because another transaction modified it). It does NOT prevent phantom reads (new rows matching your WHERE clause appearing in a subsequent query). This puts REPEATABLE READ above READ COMMITTED (which prevents dirty reads but allows non-repeatable reads) but below SERIALIZABLE (which prevents all three anomalies). Remembering the spectrum — READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE — helps place each level's guarantees.
Question 3 True / False
Under REPEATABLE READ, if Transaction A reads a row, then Transaction B modifies and commits that row, Transaction A will see the updated value on its next read of that row.
TTrue
FFalse
Answer: False
This is precisely what REPEATABLE READ prevents — the non-repeatable read anomaly. Once Transaction A has read a row, REPEATABLE READ guarantees that row will look the same for the duration of Transaction A's lifetime. Other transactions cannot modify or delete that row while A is in progress (in lock-based implementations), or A simply reads from a consistent snapshot taken at its start (in snapshot-based implementations like PostgreSQL). The updated value from Transaction B is invisible to Transaction A. This predictability is the key benefit of REPEATABLE READ over READ COMMITTED.
Question 4 True / False
REPEATABLE READ is sufficient to prevent phantom reads in most SQL database systems.
TTrue
FFalse
Answer: False
REPEATABLE READ prevents phantom reads in some implementations (notably MySQL/InnoDB, which uses gap locks to block inserts into ranges you've queried), but this is implementation-specific, not guaranteed by the SQL standard. The SQL standard defines REPEATABLE READ as allowing phantom reads — they are only prevented at the SERIALIZABLE level. PostgreSQL's REPEATABLE READ implementation uses snapshot isolation, which happens to prevent some but not all phantom scenarios. You should not rely on phantom protection from REPEATABLE READ if your application requires it; use SERIALIZABLE to guarantee that the set of matching rows is stable across re-queries.
Question 5 Short Answer
Explain the difference between a non-repeatable read and a phantom read, and why REPEATABLE READ prevents one but not the other.
Think about your answer, then reveal below.
Model answer: A non-repeatable read occurs when a transaction re-reads the same row and gets different values because another transaction modified or deleted that row in between. REPEATABLE READ prevents this by locking (or snapshotting) already-read rows. A phantom read occurs when a transaction re-runs the same query and gets additional rows that didn't exist before, because another transaction inserted new matching rows. REPEATABLE READ does not prevent phantoms because its protection covers only rows that were already read — it has no mechanism to block insertions that would match a future query.
The distinction matters for application design. If your transaction re-reads individual rows (e.g., checking a user's balance twice to verify consistency), REPEATABLE READ is sufficient. If your transaction re-runs a range query and needs the count or set of matching rows to be stable (e.g., verifying that no new inventory has been created before completing a purchase), you need SERIALIZABLE. The practical question is: do you care about the *values within rows* staying stable, or about the *set of rows* matching a condition staying stable? REPEATABLE READ handles the first; SERIALIZABLE handles both.