<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-next-key-locking">
      </a>
      17.7.4 Phantom Rows
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045163891360">
  </a>
  <a class="indexterm" name="idm46045163890288">
  </a>
  <a class="indexterm" name="idm46045163888800">
  </a>
  <a class="indexterm" name="idm46045163887312">
  </a>
  <a class="indexterm" name="idm46045163885824">
  </a>
  <a class="indexterm" name="idm46045163884336">
  </a>
  <a class="indexterm" name="idm46045163882848">
  </a>
  <a class="indexterm" name="idm46045163881360">
  </a>
  <a class="indexterm" name="idm46045163879872">
  </a>
  <p>
   The so-called
   <span class="firstterm">
    phantom
   </span>
   problem occurs within a transaction when the same query produces
      different sets of rows at different times. For example, if a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   is executed twice, but
      returns a row the second time that was not returned the first
      time, the row is a
   <span class="quote">
    “
    <span class="quote">
     phantom
    </span>
    ”
   </span>
   row.
  </p>
  <p>
   Suppose that there is an index on the
   <code class="literal">
    id
   </code>
   column
      of the
   <code class="literal">
    child
   </code>
   table and that you want to read
      and lock all rows from the table having an identifier value larger
      than 100, with the intention of updating some column in the
      selected rows later:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa61472622"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> child <span class="token keyword">WHERE</span> id <span class="token operator">&gt;</span> <span class="token number">100</span> <span class="token keyword">FOR</span> <span class="token keyword">UPDATE</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The query scans the index starting from the first record where
   <code class="literal">
    id
   </code>
   is bigger than 100. Let the table contain
      rows having
   <code class="literal">
    id
   </code>
   values of 90 and 102. If the
      locks set on the index records in the scanned range do not lock
      out inserts made in the gaps (in this case, the gap between 90 and
      102), another session can insert a new row into the table with an
   <code class="literal">
    id
   </code>
   of 101. If you were to execute the same
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   within the same transaction,
      you would see a new row with an
   <code class="literal">
    id
   </code>
   of 101 (a
   <span class="quote">
    “
    <span class="quote">
     phantom
    </span>
    ”
   </span>
   ) in the result set returned by the query.
      If we regard a set of rows as a data item, the new phantom child
      would violate the isolation principle of transactions that a
      transaction should be able to run so that the data it has read
      does not change during the transaction.
  </p>
  <p>
   To prevent phantoms,
   <code class="literal">
    InnoDB
   </code>
   uses an algorithm
      called
   <span class="firstterm">
    next-key locking
   </span>
   that
      combines index-row locking with gap locking.
   <code class="literal">
    InnoDB
   </code>
   performs row-level locking in such a way
      that when it searches or scans a table index, it sets shared or
      exclusive locks on the index records it encounters. Thus, the
      row-level locks are actually index-record locks. In addition, a
      next-key lock on an index record also affects the
   <span class="quote">
    “
    <span class="quote">
     gap
    </span>
    ”
   </span>
   before the index record. That is, a next-key
      lock is an index-record lock plus a gap lock on the gap preceding
      the index record. If one session has a shared or exclusive lock on
      record
   <code class="literal">
    R
   </code>
   in an index, another session cannot
      insert a new index record in the gap immediately before
   <code class="literal">
    R
   </code>
   in the index order.
  </p>
  <p>
   When
   <code class="literal">
    InnoDB
   </code>
   scans an index, it can also lock
      the gap after the last record in the index. Just that happens in
      the preceding example: To prevent any insert into the table where
   <code class="literal">
    id
   </code>
   would be bigger than 100, the locks set by
   <code class="literal">
    InnoDB
   </code>
   include a lock on the gap following
   <code class="literal">
    id
   </code>
   value 102.
  </p>
  <p>
   You can use next-key locking to implement a uniqueness check in
      your application: If you read your data in share mode and do not
      see a duplicate for a row you are going to insert, then you can
      safely insert your row and know that the next-key lock set on the
      successor of your row during the read prevents anyone meanwhile
      inserting a duplicate for your row. Thus, the next-key locking
      enables you to
   <span class="quote">
    “
    <span class="quote">
     lock
    </span>
    ”
   </span>
   the nonexistence of something
      in your table.
  </p>
  <p>
   Gap locking can be disabled as discussed in
   <a class="xref" href="innodb-locking.html" title="17.7.1 InnoDB Locking">
    Section 17.7.1, “InnoDB Locking”
   </a>
   . This may cause phantom problems
      because other sessions can insert new rows into the gaps when gap
      locking is disabled.
  </p>
 </div>
 <br/>
</div>
