<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-features-transactions">
      </a>
      19.5.1.35 Replication and Transactions
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045134952496">
  </a>
  <a class="indexterm" name="idm46045134951008">
  </a>
  <p>
   <b>
    Mixing transactional and nontransactional statements within the same
          transaction.
   </b>
   In general, you should avoid transactions that update both
          transactional and nontransactional tables in a replication
          environment. You should also avoid using any statement that
          accesses both transactional (or temporary) and
          nontransactional tables and writes to any of them.
  </p>
  <p>
   The server uses these rules for binary logging:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If the initial statements in a transaction are
            nontransactional, they are written to the binary log
            immediately. The remaining statements in the transaction are
            cached and not written to the binary log until the
            transaction is committed. (If the transaction is rolled
            back, the cached statements are written to the binary log
            only if they make nontransactional changes that cannot be
            rolled back. Otherwise, they are discarded.)
     </p>
    </li>
    <li class="listitem">
     <p>
      For statement-based logging, logging of nontransactional
            statements is affected by the
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_direct_non_transactional_updates">
       <code class="literal">
        binlog_direct_non_transactional_updates
       </code>
      </a>
      system variable. When this variable is
      <code class="literal">
       OFF
      </code>
      (the default), logging is as just
            described. When this variable is
      <code class="literal">
       ON
      </code>
      ,
            logging occurs immediately for nontransactional statements
            occurring anywhere in the transaction (not just initial
            nontransactional statements). Other statements are kept in
            the transaction cache and logged when the transaction
            commits.
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_direct_non_transactional_updates">
       <code class="literal">
        binlog_direct_non_transactional_updates
       </code>
      </a>
      has no effect for row-format or mixed-format binary logging.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a name="replication-features-transactions-trx-nontrx-mixed">
   </a>
   <b>
    Transactional, nontransactional, and mixed statements.
   </b>
   <a class="indexterm" name="idm46045134939168">
   </a>
   To apply those rules, the server considers a statement
          nontransactional if it changes only nontransactional tables,
          and transactional if it changes only transactional tables. A
          statement that references both nontransactional and
          transactional tables and updates
   <span class="emphasis">
    <em>
     any
    </em>
   </span>
   of
          the tables involved is considered a
   <span class="quote">
    “
    <span class="quote">
     mixed
    </span>
    ”
   </span>
   statement. Mixed statements, like transactional statements,
          are cached and logged when the transaction commits.
  </p>
  <p>
   A mixed statement that updates a transactional table is
        considered unsafe if the statement also performs either of the
        following actions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Updates or reads a temporary table
     </p>
    </li>
    <li class="listitem">
     <p>
      Reads a nontransactional table and the transaction isolation
            level is less than REPEATABLE_READ
     </p>
    </li>
   </ul>
  </div>
  <p>
   A mixed statement following the update of a transactional table
        within a transaction is considered unsafe if it performs either
        of the following actions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Updates any table and reads from any temporary table
     </p>
    </li>
    <li class="listitem">
     <p>
      Updates a nontransactional table and
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_direct_non_transactional_updates">
       <code class="literal">
        binlog_direct_non_transactional_updates
       </code>
      </a>
      is OFF
     </p>
    </li>
   </ul>
  </div>
  <p>
   For more information, see
   <a class="xref" href="replication-rbr-safe-unsafe.html" title="19.2.1.3 Determination of Safe and Unsafe Statements in Binary Logging">
    Section 19.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”
   </a>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    A mixed statement is unrelated to mixed binary logging format.
   </p>
  </div>
  <p>
   In situations where transactions mix updates to transactional
        and nontransactional tables, the order of statements in the
        binary log is correct, and all needed statements are written to
        the binary log even in case of a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     ROLLBACK
    </code>
   </a>
   .
        However, when a second connection updates the nontransactional
        table before the first connection transaction is complete,
        statements can be logged out of order because the second
        connection update is written immediately after it is performed,
        regardless of the state of the transaction being performed by
        the first connection.
  </p>
  <p>
   <b>
    Using different storage engines on source and replica.
   </b>
   It is possible to replicate transactional tables on the source
          using nontransactional tables on the replica. For example, you
          can replicate an
   <code class="literal">
    InnoDB
   </code>
   source table as a
   <code class="literal">
    MyISAM
   </code>
   replica table. However, if you do
          this, there are problems if the replica is stopped in the
          middle of a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     BEGIN
    </code>
   </a>
   ...
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   block because the
          replica restarts at the beginning of the
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     BEGIN
    </code>
   </a>
   block.
  </p>
  <p>
   It is also safe to replicate transactions from
   <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
    <code class="literal">
     MyISAM
    </code>
   </a>
   tables on the source to
        transactional tables, such as tables that use the
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   storage engine, on the
        replica. In such cases, an
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     AUTOCOMMIT=1
    </code>
   </a>
   statement issued
        on the source is replicated, thus enforcing
   <code class="literal">
    AUTOCOMMIT
   </code>
   mode on the replica.
  </p>
  <p>
   When the storage engine type of the replica is nontransactional,
        transactions on the source that mix updates of transactional and
        nontransactional tables should be avoided because they can cause
        inconsistency of the data between the source transactional table
        and the replica nontransactional table. That is, such
        transactions can lead to source storage engine-specific behavior
        with the possible effect of replication going out of synchrony.
        MySQL does not issue a warning about this, so extra care should
        be taken when replicating transactional tables from the source
        to nontransactional tables on the replicas.
  </p>
  <p>
   <b>
    Changing the binary logging format within transactions.
   </b>
   The
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="literal">
     binlog_format
    </code>
   </a>
   and
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_checksum">
    <code class="literal">
     binlog_checksum
    </code>
   </a>
   system
          variables are read-only as long as a transaction is in
          progress.
  </p>
  <p>
   Every transaction (including
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   transactions) is
        recorded in the binary log as though it starts with a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     BEGIN
    </code>
   </a>
   statement, and ends with either a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   or a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     ROLLBACK
    </code>
   </a>
   statement. This is even true for statements affecting tables
        that use a nontransactional storage engine (such as
   <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
    <code class="literal">
     MyISAM
    </code>
   </a>
   ).
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    For restrictions that apply specifically to XA transactions,
          see
    <a class="xref" href="xa-restrictions.html" title="15.3.8.3 Restrictions on XA Transactions">
     Section 15.3.8.3, “Restrictions on XA Transactions”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
