<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="innodb-multi-versioning">
      </a>
      17.3 InnoDB Multi-Versioning
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045167232032">
  </a>
  <a class="indexterm" name="idm46045167230544">
  </a>
  <p>
   <code class="literal">
    InnoDB
   </code>
   is a multi-version storage engine. It
    keeps information about old versions of changed rows to support
    transactional features such as concurrency and rollback. This
    information is stored in undo tablespaces in a data structure called
    a rollback segment. See
   <a class="xref" href="innodb-undo-tablespaces.html" title="17.6.3.4 Undo Tablespaces">
    Section 17.6.3.4, “Undo Tablespaces”
   </a>
   .
   <code class="literal">
    InnoDB
   </code>
   uses the information in the rollback
    segment to perform the undo operations needed in a transaction
    rollback. It also uses the information to build earlier versions of
    a row for a consistent read. See
   <a class="xref" href="innodb-consistent-read.html" title="17.7.2.3 Consistent Nonlocking Reads">
    Section 17.7.2.3, “Consistent Nonlocking Reads”
   </a>
   .
  </p>
  <p>
   Internally,
   <code class="literal">
    InnoDB
   </code>
   adds three fields to each row
    stored in the database:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A 6-byte
      <code class="literal">
       DB_TRX_ID
      </code>
      field indicates the
        transaction identifier for the last transaction that inserted or
        updated the row. Also, a deletion is treated internally as an
        update where a special bit in the row is set to mark it as
        deleted.
     </p>
    </li>
    <li class="listitem">
     <p>
      A 7-byte
      <code class="literal">
       DB_ROLL_PTR
      </code>
      field called the roll
        pointer. The roll pointer points to an undo log record written
        to the rollback segment. If the row was updated, the undo log
        record contains the information necessary to rebuild the content
        of the row before it was updated.
     </p>
    </li>
    <li class="listitem">
     <p>
      A 6-byte
      <code class="literal">
       DB_ROW_ID
      </code>
      field contains a row ID
        that increases monotonically as new rows are inserted. If
      <code class="literal">
       InnoDB
      </code>
      generates a clustered index
        automatically, the index contains row ID values. Otherwise, the
      <code class="literal">
       DB_ROW_ID
      </code>
      column does not appear in any
        index.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Undo logs in the rollback segment are divided into insert and update
    undo logs. Insert undo logs are needed only in transaction rollback
    and can be discarded as soon as the transaction commits. Update undo
    logs are used also in consistent reads, but they can be discarded
    only after there is no transaction present for which
   <code class="literal">
    InnoDB
   </code>
   has assigned a snapshot that in a
    consistent read could require the information in the update undo log
    to build an earlier version of a database row. For additional
    information about undo logs, see
   <a class="xref" href="innodb-undo-logs.html" title="17.6.6 Undo Logs">
    Section 17.6.6, “Undo Logs”
   </a>
   .
  </p>
  <p>
   It is recommend that you commit transactions regularly, including
    transactions that issue only consistent reads. Otherwise,
   <code class="literal">
    InnoDB
   </code>
   cannot discard data from the update undo
    logs, and the rollback segment may grow too big, filling up the undo
    tablespace in which it resides. For information about managing undo
    tablespaces, see
   <a class="xref" href="innodb-undo-tablespaces.html" title="17.6.3.4 Undo Tablespaces">
    Section 17.6.3.4, “Undo Tablespaces”
   </a>
   .
  </p>
  <p>
   The physical size of an undo log record in the rollback segment is
    typically smaller than the corresponding inserted or updated row.
    You can use this information to calculate the space needed for your
    rollback segment.
  </p>
  <p>
   In the
   <code class="literal">
    InnoDB
   </code>
   multi-versioning scheme, a row is
    not physically removed from the database immediately when you delete
    it with an SQL statement.
   <code class="literal">
    InnoDB
   </code>
   only physically
    removes the corresponding row and its index records when it discards
    the update undo log record written for the deletion. This removal
    operation is called a purge, and it is quite fast, usually taking
    the same order of time as the SQL statement that did the deletion.
  </p>
  <p>
   If you insert and delete rows in smallish batches at about the same
    rate in the table, the purge thread can start to lag behind and the
    table can grow bigger and bigger because of all the
   <span class="quote">
    “
    <span class="quote">
     dead
    </span>
    ”
   </span>
   rows, making everything disk-bound and very
    slow. In such cases, throttle new row operations, and allocate more
    resources to the purge thread by tuning the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag">
    <code class="literal">
     innodb_max_purge_lag
    </code>
   </a>
   system
    variable. For more information, see
   <a class="xref" href="innodb-purge-configuration.html" title="17.8.9 Purge Configuration">
    Section 17.8.9, “Purge Configuration”
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="innodb-multi-versioning-secondary-indexes">
       </a>
       Multi-Versioning and Secondary Indexes
      </h3>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     InnoDB
    </code>
    multiversion concurrency control (MVCC)
      treats secondary indexes differently than clustered indexes.
      Records in a clustered index are updated in-place, and their
      hidden system columns point undo log entries from which earlier
      versions of records can be reconstructed. Unlike clustered index
      records, secondary index records do not contain hidden system
      columns nor are they updated in-place.
   </p>
   <p>
    When a secondary index column is updated, old secondary index
      records are delete-marked, new records are inserted, and
      delete-marked records are eventually purged. When a secondary
      index record is delete-marked or the secondary index page is
      updated by a newer transaction,
    <code class="literal">
     InnoDB
    </code>
    looks up
      the database record in the clustered index. In the clustered
      index, the record's
    <code class="literal">
     DB_TRX_ID
    </code>
    is checked, and
      the correct version of the record is retrieved from the undo log
      if the record was modified after the reading transaction was
      initiated.
   </p>
   <p>
    If a secondary index record is marked for deletion or the
      secondary index page is updated by a newer transaction, the
    <a class="link" href="glossary.html#glos_covering_index" title="covering index">
     covering index
    </a>
    technique is not used. Instead of returning values from the index
      structure,
    <code class="literal">
     InnoDB
    </code>
    looks up the record in the
      clustered index.
   </p>
   <p>
    However, if the
    <a class="link" href="index-condition-pushdown-optimization.html" title="10.2.1.6 Index Condition Pushdown Optimization">
     index
      condition pushdown (ICP)
    </a>
    optimization is enabled, and parts
      of the
    <code class="literal">
     WHERE
    </code>
    condition can be evaluated using
      only fields from the index, the MySQL server still pushes this
      part of the
    <code class="literal">
     WHERE
    </code>
    condition down to the storage
      engine where it is evaluated using the index. If no matching
      records are found, the clustered index lookup is avoided. If
      matching records are found, even among delete-marked records,
    <code class="literal">
     InnoDB
    </code>
    looks up the record in the clustered
      index.
   </p>
  </div>
 </div>
 <br/>
</div>
