A phantom read is a concurrency control anomaly that occurs in database systems when a transaction retrieves a set of rows matching specific search criteria, and subsequently another concurrent transaction inserts or deletes rows that would satisfy the same search criteria, causing the original transaction's result set to change if the query is executed again ¹⁾. This phenomenon represents a violation of the REPEATABLE READ isolation level and demonstrates the challenges inherent in managing concurrent database access without sacrificing performance or consistency.

Phantom reads differ from other concurrency anomalies in their specific mechanism and scope. While dirty reads involve reading uncommitted data and non-repeatable reads involve row-level inconsistencies, phantom reads operate at the set level—affecting the cardinality and composition of result sets rather than individual row values ²⁾.

The defining characteristics of a phantom read include:

* Range-based queries: The anomaly specifically affects queries that retrieve multiple rows using predicates such as WHERE clauses with range conditions * Insert/delete operations: Other transactions must insert or delete rows (not modify existing ones) that fall within the search criteria * Repeated query execution: The phantom read manifests only when the same query is executed multiple times within a single transaction * Set-level inconsistency: The number of rows or the composition of result sets changes between successive executions of identical queries

The SQL Standard defines four isolation levels with increasing protection against concurrency anomalies. Phantom reads specifically occur at the REPEATABLE READ isolation level but are prevented at the SERIALIZABLE isolation level ³⁾.

REPEATABLE READ isolation guarantees that rows read during a transaction remain stable in their values—subsequent reads of the same row return identical data. However, this isolation level does not prevent new rows matching the search criteria from being inserted by concurrent transactions. For example, a transaction executing:

SELECT * FROM orders WHERE amount > 1000; might return 5 rows initially, but after another transaction inserts a new order exceeding 1000, the same query executed later in the original transaction would return 6 rows. The original set of 5 rows remains unchanged (preventing non-repeatable reads), but the new matching row creates a phantom.

SERIALIZABLE isolation prevents phantom reads by implementing predicate locking or other mechanisms that effectively lock the logical range covered by a query, preventing insertions or deletions that would affect result sets ⁴⁾.

Phantom reads produce tangible consequences in real-world database applications. Consider a banking scenario where a transaction calculates the total balance across all customer accounts within a branch:

* Initial execution: Query returns 10 accounts with total balance of $500,000 * Concurrent insertion: Another transaction opens a new account in the same branch with a $50,000 initial deposit * Second execution: The same totaling query now returns 11 accounts with total balance of $550,000

This inconsistency can cause reconciliation errors, incorrect financial reporting, and audit discrepancies.

Another example involves inventory management systems where a transaction counts available stock units:

* Query retrieves products with stock > 0, returning 15 items * Concurrent transaction creates a new product variant and adds it to inventory * Subsequent count query now returns 16 items

Database systems employ multiple approaches to prevent phantom reads:

Predicate Locking: SERIALIZABLE isolation implementations use predicate locks that lock the logical range of a query rather than specific rows. Any INSERT, UPDATE, or DELETE operation that would affect the predicate's result set is blocked until the transaction commits.

Versioning and MVCC: Multi-Version Concurrency Control (MVCC) maintains multiple versions of data, allowing transactions to operate on consistent snapshots. Some MVCC implementations include predicate versioning to handle phantom read prevention ⁵⁾.

Application-Level Synchronization: Developers can implement pessimistic or optimistic locking strategies at the application layer, including explicit SELECT FOR UPDATE statements with appropriate lock hints to prevent concurrent modifications.

Materialization: Storing result sets at the application level and validating constraints on subsequent operations prevents reliance on repeated database queries that might yield different results.

Different database systems handle phantom reads with varying default isolation levels and locking mechanisms:

* PostgreSQL: Implements REPEATABLE READ using MVCC snapshots, which actually prevents phantom reads in practice despite the SQL standard allowing them at this level * MySQL/InnoDB: Provides REPEATABLE READ with gap locking that prevents phantoms by locking the gaps between index records * Oracle Database: Uses SERIALIZABLE isolation with multi-version read consistency * SQL Server: Implements isolation levels with options for optimistic versioning

Understanding phantom reads remains essential for database architects and application developers, particularly in distributed database systems, cloud databases, and applications requiring strong consistency guarantees. Modern transactional databases increasingly use MVCC and advanced locking mechanisms to balance concurrency with correctness, making phantom read issues less prevalent but still relevant for applications requiring REPEATABLE READ isolation with explicit phantom read prevention.

• Dirty Read
• Lost Update
• Concurrency Control