blog9.md
Phantom Read refers to a situation where a transaction, upon querying a range consecutively, sees rows in the second query that it did not see in the first.

Phantom Read

Phantom Read refers to a situation where a transaction, upon querying a range consecutively, sees rows in the second query that it did not see in the first.

When does Phantom Read occur?

  • Under the Repeatable Read isolation level, normal queries are snapshot reads and will not see data inserted by other transactions.
  • Therefore, phantom reads only occur in current reads.
  • Queries with for update are treated as current reads. And the rule for current reads is to read the latest value of all committed records.

Problems with Phantom Reads

  1. Semantics: Locking a row should prevent other transactions from reading/writing. However, this semantic is broken with phantom reads.
  2. Data Consistency: Locks are designed to ensure data consistency. This not only pertains to the internal state of database data but also the logical consistency between data and logs.

Resolving Phantom Reads

  • Gap Lock: To address phantom reads, InnoDB introduced a new lock called Gap Lock. This lock locks the gap between two values.
  • Concurrency Issues: Introducing gap locks can potentially lock a broader range, affecting concurrency.
  • Next-key Lock: Gap locks, combined with row locks, are termed as next-key locks. Each next-key lock is a semi-open interval.

Locking Rules:

  1. Principle 1: The basic unit of locking is the next-key lock.
  2. Principle 2: Only objects accessed during the search will be locked.
  3. Optimization 1: For unique index equality checks, the next-key lock degrades to a row lock.
  4. Optimization 2: For unique index equality checks, when traversing to the right and the last value doesn’t satisfy the equality condition, the next-key lock degrades to a gap lock.
  5. Bug Alert: Range queries on unique indices will stop at the first value that doesn’t satisfy the condition.

Note: It’s recommended to use limit while deleting data. This not only controls the number of records deleted for safer operations but also reduces the lock scope.