<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-performance-ro-txn">
      </a>
      10.5.3 Optimizing InnoDB Read-Only Transactions
     </h3>
    </div>
   </div>
  </div>
  <p>
   <code class="literal">
    InnoDB
   </code>
   can avoid the overhead associated with
        setting up the
   <a class="link" href="glossary.html#glos_transaction_id" title="transaction ID">
    transaction
        ID
   </a>
   (
   <code class="literal">
    TRX_ID
   </code>
   field) for transactions
        that are known to be read-only. A transaction ID is only needed
        for a
   <a class="link" href="glossary.html#glos_transaction" title="transaction">
    transaction
   </a>
   that
        might perform write operations or
   <a class="link" href="glossary.html#glos_locking_read" title="locking read">
    locking reads
   </a>
   such as
   <code class="literal">
    SELECT ... FOR UPDATE
   </code>
   . Eliminating
        unnecessary transaction IDs reduces the size of internal data
        structures that are consulted each time a query or data change
        statement constructs a
   <a class="link" href="glossary.html#glos_read_view" title="read view">
    read
        view
   </a>
   .
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   detects read-only transactions when:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The transaction is started with the
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        START TRANSACTION
            READ ONLY
       </code>
      </a>
      statement. In this case, attempting to
            make changes to the database (for
      <code class="literal">
       InnoDB
      </code>
      ,
      <code class="literal">
       MyISAM
      </code>
      , or other types of tables) causes
            an error, and the transaction continues in read-only state:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa37476308"><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">ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.</code></pre>
     </div>
     <p>
      You can still make changes to session-specific temporary
            tables in a read-only transaction, or issue locking queries
            for them, because those changes and locks are not visible to
            any other transaction.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      setting is
            turned on, so that the transaction is guaranteed to be a
            single statement, and the single statement making up the
            transaction is a
      <span class="quote">
       “
       <span class="quote">
        non-locking
       </span>
       ”
      </span>
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement. That is, a
      <code class="literal">
       SELECT
      </code>
      that does not use a
      <code class="literal">
       FOR
            UPDATE
      </code>
      or
      <code class="literal">
       LOCK IN SHARED MODE
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      The transaction is started without the
      <code class="literal">
       READ
            ONLY
      </code>
      option, but no updates or statements that
            explicitly lock rows have been executed yet. Until updates
            or explicit locks are required, a transaction stays in
            read-only mode.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Thus, for a read-intensive application such as a report
        generator, you can tune a sequence of
   <code class="literal">
    InnoDB
   </code>
   queries by grouping them inside
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     START TRANSACTION READ
        ONLY
    </code>
   </a>
   and
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   , or by
        turning on the
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   setting before running the
   <code class="literal">
    SELECT
   </code>
   statements,
        or simply by avoiding any data change statements interspersed
        with the queries.
  </p>
  <p>
   For information about
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     START
        TRANSACTION
    </code>
   </a>
   and
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   , see
   <a class="xref" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    Section 15.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”
   </a>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Transactions that qualify as auto-commit, non-locking, and
          read-only (AC-NL-RO) are kept out of certain internal
    <code class="literal">
     InnoDB
    </code>
    data structures and are therefore
          not listed in
    <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
     <code class="literal">
      SHOW ENGINE
          INNODB STATUS
     </code>
    </a>
    output.
   </p>
  </div>
 </div>
 <br/>
</div>
