<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-undo-logs">
      </a>
      17.6.6 Undo Logs
     </h3>
    </div>
   </div>
  </div>
  <p>
   An undo log is a collection of undo log records associated with a
      single read-write transaction. An undo log record contains
      information about how to undo the latest change by a transaction
      to a
   <a class="link" href="glossary.html#glos_clustered_index" title="clustered index">
    clustered index
   </a>
   record. If another transaction needs to see the original data as
      part of a consistent read operation, the unmodified data is
      retrieved from undo log records. Undo logs exist within
   <a class="link" href="glossary.html#glos_undo_log_segment" title="undo log segment">
    undo log segments
   </a>
   ,
      which are contained within
   <a class="link" href="glossary.html#glos_rollback_segment" title="rollback segment">
    rollback segments
   </a>
   .
      Rollback segments reside in
   <a class="link" href="glossary.html#glos_undo_tablespace" title="undo tablespace">
    undo tablespaces
   </a>
   and
      in the
   <a class="link" href="glossary.html#glos_global_temporary_tablespace" title="global temporary tablespace">
    global
      temporary tablespace
   </a>
   .
  </p>
  <p>
   Undo logs that reside in the global temporary tablespace are used
      for transactions that modify data in user-defined temporary
      tables. These undo logs are not redo-logged, as they are not
      required for crash recovery. They are used only for rollback while
      the server is running. This type of undo log benefits performance
      by avoiding redo logging I/O.
  </p>
  <p>
   For information about data-at-rest encryption for undo logs, see
   <a class="xref" href="innodb-data-encryption.html#innodb-data-encryption-undo-log" title="Undo Log Encryption">
    Undo Log Encryption
   </a>
   .
  </p>
  <p>
   Each undo tablespace and the global temporary tablespace
      individually support a maximum of 128 rollback segments. The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_rollback_segments">
    <code class="literal">
     innodb_rollback_segments
    </code>
   </a>
   variable
      defines the number of rollback segments.
  </p>
  <p>
   The number of transactions that a rollback segment supports
      depends on the number of undo slots in the rollback segment and
      the number of undo logs required by each transaction. The number
      of undo slots in a rollback segment differs according to
   <code class="literal">
    InnoDB
   </code>
   page size.
  </p>
  <div class="informaltable">
   <table summary="Number of undo slots in a rollback segment for each InnoDB page size">
    <colgroup>
     <col style="width: 50%"/>
     <col style="width: 50%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       InnoDB Page Size
      </th>
      <th>
       Number of Undo Slots in a Rollback Segment (InnoDB Page Size / 16)
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       <code class="literal">
        4096 (4KB)
       </code>
      </td>
      <td>
       <code class="literal">
        256
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        8192 (8KB)
       </code>
      </td>
      <td>
       <code class="literal">
        512
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        16384 (16KB)
       </code>
      </td>
      <td>
       <code class="literal">
        1024
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        32768 (32KB)
       </code>
      </td>
      <td>
       <code class="literal">
        2048
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        65536 (64KB)
       </code>
      </td>
      <td>
       <code class="literal">
        4096
       </code>
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   A transaction is assigned up to four undo logs, one for each of
      the following operation types:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      operations on
          user-defined tables
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      operations on
          user-defined tables
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      operations on
          user-defined temporary tables
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      operations on
          user-defined temporary tables
     </p>
    </li>
   </ol>
  </div>
  <p>
   Undo logs are assigned as needed. For example, a transaction that
      performs
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   ,
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   , and
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   operations on regular and
      temporary tables requires a full assignment of four undo logs. A
      transaction that performs only
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   operations on regular tables
      requires a single undo log.
  </p>
  <p>
   A transaction that performs operations on regular tables is
      assigned undo logs from an assigned undo tablespace rollback
      segment. A transaction that performs operations on temporary
      tables is assigned undo logs from an assigned global temporary
      tablespace rollback segment.
  </p>
  <p>
   An undo log assigned to a transaction remains attached to the
      transaction for its duration. For example, an undo log assigned to
      a transaction for an
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   operation on a regular table is used for all
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   operations on regular tables
      performed by that transaction.
  </p>
  <p>
   Given the factors described above, the following formulas can be
      used to estimate the number of concurrent read-write transactions
      that
   <code class="literal">
    InnoDB
   </code>
   is capable of supporting.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    It is possible to encounter a concurrent transaction limit error
        before reaching the number of concurrent read-write transactions
        that
    <code class="literal">
     InnoDB
    </code>
    is capable of supporting. This
        occurs when a rollback segment assigned to a transaction runs
        out of undo slots. In such cases, try rerunning the transaction.
   </p>
   <p>
    When transactions perform operations on temporary tables, the
        number of concurrent read-write transactions that
    <code class="literal">
     InnoDB
    </code>
    is capable of supporting is
        constrained by the number of rollback segments allocated to the
        global temporary tablespace, which is 128 by default.
   </p>
  </div>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If each transaction performs either an
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      <span class="bold">
       <strong>
        or
       </strong>
      </span>
      an
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      or
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      operation, the number of
          concurrent read-write transactions that
      <code class="literal">
       InnoDB
      </code>
      is capable of supporting is:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa12494739"><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-none">(innodb_page_size / 16) * innodb_rollback_segments * number of undo tablespaces</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      If each transaction performs an
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      <span class="bold">
       <strong>
        and
       </strong>
      </span>
      an
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      or
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      operation, the number of
          concurrent read-write transactions that
      <code class="literal">
       InnoDB
      </code>
      is capable of supporting is:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa86131014"><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-none">(innodb_page_size / 16 / 2) * innodb_rollback_segments * number of undo tablespaces</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      If each transaction performs an
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      operation on a temporary
          table, the number of concurrent read-write transactions that
      <code class="literal">
       InnoDB
      </code>
      is capable of supporting is:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa6789528"><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-none">(innodb_page_size / 16) * innodb_rollback_segments</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      If each transaction performs an
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      <span class="bold">
       <strong>
        and
       </strong>
      </span>
      an
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      or
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      operation on a temporary
          table, the number of concurrent read-write transactions that
      <code class="literal">
       InnoDB
      </code>
      is capable of supporting is:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa40513178"><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-none">(innodb_page_size / 16 / 2) * innodb_rollback_segments</code></pre>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
