Phantom Read

1. Overview

Database transactions are defined by the four properties known as ACID. The Isolation Level (I in ACID) allows you to trade off data integrity for performance.

The weaker the isolation level, the more anomalies can occur, and in this article, we are going to describe the Phantom Read phenomenon.

2. Description

2.1 Phenomenon

If a transaction makes a business decision based on a set of rows satisfying a given predicate, without range locks, a concurrent transaction might insert a record matching that particular predicate.

In the diagram above, the flow of statements goes like this:

• Alice and Bob start two database transactions.
• Bob’s reads all the post_comment records associated with the post row with the identifier value of 1.
• Alice adds a new post_comment record which is associated with the post row having the identifier value of 1.
• Alice commits her database transaction.
• If Bob’s re-reads the post_comment records having the post_id column value equal to 1, he will observe a different version of this result set.

This phenomenon is problematic when the current transaction makes a business decision based on the first version of the given result set.

2.2 Prevention

The SQL standard says that Phantom Read occurs if two consecutive query executions render different results because a concurrent transaction has modified the range of records in between the two calls.

Although providing consistent reads is a mandatory requirement for serializability, that is not sufficient. For instance, one buyer might purchase a product without being aware of a better offer that was added right after the user has finished fetching the offer list.

The 2PL-based Serializable isolation prevents Phantom Reads through the use of predicate locking while MVCC database engines address the Phantom Read anomaly by returning consistent snapshots.

However, a concurrent transaction can still modify the range of records that was read previously. Even if the MVCC database engine introspects the transaction schedule, the outcome is not always the same as a 2PL-based implementation. One such example is when the second transaction issues an insert without reading the same range of records as the first transaction. In this particular use case, some MVCC database engines will not end up rolling back the first transaction.

3. Transaction Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Snapshot	No	No	No
Serializable	No	No	No

4. References

https://vladmihalcea.com/phantom-read/