Phantom read/update problem

About

Phantom problem is a phenomena. A data problem during concurrency update.

In the phantom problem, a transaction accesses a relation more than once with the same predicate in the same transaction, but sees new phantom tuples on re-access that were not seen on the first access.

In other words, a transaction reruns a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

Articles Related

Example

• A transaction queries the number of employees.
• Five minutes later it performs the same query, but now the number has increased by one because another user inserted a record for a new hire.
• More data satisfies the query criteria than before, but unlike in a fuzzy read the previously read data is unchanged.

Cause

This is because two-phase locking at tuple-level granularity does not prevent the insertion of new tuples into a table. Two-phase locking of tables prevents phantoms, but table-level locking can be restrictive in cases where transactions access only a few tuples via an index.