Phantoms in range queries What anomaly occurs when a transaction re-executes a predicate (range) query and finds additional rows inserted by other transactions since its prior read?

Introduction / Context:
Beyond single-row changes, range queries can be affected by concurrent inserts or deletes, producing anomalies known as phantoms. Correct handling of phantoms is central to serializable isolation and predicate locking theory.

Given Data / Assumptions:

• We execute the same search condition twice, e.g., WHERE amount > 1000.
• The second execution returns additional rows that were not present previously.
• Other transactions may have inserted rows satisfying the predicate.

Concept / Approach:

When new rows appear that match the prior predicate, we call this a phantom read. It differs from nonrepeatable reads (which affect the same previously read row) because the issue is the changing membership of the result set, not just changes to a particular row's values.

Step-by-Step Solution:

Verification / Alternative check:

Standard isolation definitions (ANSI/ISO, academia, and vendor docs) illustrate phantoms as changes in the set of rows qualifying for a predicate, confirming the term and mitigation via serializable isolation or appropriate locking/indexing strategies.

Why Other Options Are Wrong:

• Nonrepeatable read: the same row changes value, not new rows appearing.
• Dirty read: reading uncommitted data, not committed inserts after the fact.
• Consistent read: a stable snapshot, not an anomaly.
• Lost update: clobbering concurrent updates, unrelated to range membership.

Common Pitfalls:

• Using row locks only; they do not protect against new qualifying rows.
• Confusing repeatable read with phantom protection; some engines still allow phantoms at REPEATABLE READ.

Final Answer:

Phantom read.