Phantom read/update problem

> (Data|State) Management and Processing > Data Properties and Transactions

1 - 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.

Advertising

3 - 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.

4 - 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.