<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="xa-states">
      </a>
      15.3.8.2 XA Transaction States
     </h4>
    </div>
   </div>
  </div>
  <p>
   An XA transaction progresses through the following states:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Use
      <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
       <code class="literal">
        XA
            START
       </code>
      </a>
      to start an XA transaction and put it in the
      <code class="literal">
       ACTIVE
      </code>
      state.
     </p>
    </li>
    <li class="listitem">
     <p>
      For an
      <code class="literal">
       ACTIVE
      </code>
      XA transaction, issue the
            SQL statements that make up the transaction, and then issue
            an
      <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
       <code class="literal">
        XA
            END
       </code>
      </a>
      statement.
      <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
       <code class="literal">
        XA
            END
       </code>
      </a>
      puts the transaction in the
      <code class="literal">
       IDLE
      </code>
      state.
     </p>
    </li>
    <li class="listitem">
     <p>
      For an
      <code class="literal">
       IDLE
      </code>
      XA transaction, you can issue
            either an
      <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
       <code class="literal">
        XA
            PREPARE
       </code>
      </a>
      statement or an
      <code class="literal">
       XA COMMIT ... ONE
            PHASE
      </code>
      statement:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: disc; ">
       <li class="listitem">
        <p>
         <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
          <code class="literal">
           XA
                PREPARE
          </code>
         </a>
         puts the transaction in the
         <code class="literal">
          PREPARED
         </code>
         state. An
         <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
          <code class="literal">
           XA
                RECOVER
          </code>
         </a>
         statement at this point includes the
                transaction's
         <em class="replaceable">
          <code>
           xid
          </code>
         </em>
         value
                in its output, because
         <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
          <code class="literal">
           XA
                RECOVER
          </code>
         </a>
         lists all XA transactions that are in
                the
         <code class="literal">
          PREPARED
         </code>
         state.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          XA COMMIT ... ONE PHASE
         </code>
         prepares and
                commits the transaction. The
         <em class="replaceable">
          <code>
           xid
          </code>
         </em>
         value is not listed by
         <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
          <code class="literal">
           XA
                RECOVER
          </code>
         </a>
         because the transaction terminates.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      For a
      <code class="literal">
       PREPARED
      </code>
      XA transaction, you can
            issue an
      <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
       <code class="literal">
        XA
            COMMIT
       </code>
      </a>
      statement to commit and terminate the
            transaction, or
      <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
       <code class="literal">
        XA
            ROLLBACK
       </code>
      </a>
      to roll back and terminate the
            transaction.
     </p>
    </li>
   </ol>
  </div>
  <p>
   Here is a simple XA transaction that inserts a row into a table
        as part of a global transaction:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59346166"><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 prompt">mysql&gt;</span> <span class="token keyword">XA</span> <span class="token keyword">START</span> <span class="token string">'xatest'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> mytable <span class="token punctuation">(</span>i<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.04 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">XA</span> <span class="token keyword">END</span> <span class="token string">'xatest'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">XA</span> <span class="token keyword">PREPARE</span> <span class="token string">'xatest'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">XA</span> <span class="token keyword">COMMIT</span> <span class="token string">'xatest'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span></code></pre>
  </div>
  <p>
   MySQL 8.4 supports detached XA transactions,
        enabled by the
   <a class="link" href="server-system-variables.html#sysvar_xa_detach_on_prepare">
    <code class="literal">
     xa_detach_on_prepare
    </code>
   </a>
   system
        variable (
   <code class="literal">
    ON
   </code>
   by default). Detached
        transactions are disconnected from the current session following
        execution of
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA
        PREPARE
    </code>
   </a>
   (and can be committed or rolled back by
        another connection). This means that the current session is free
        to start a new local transaction or XA transaction without
        having to wait for the prepared XA transaction to be committed
        or rolled back.
  </p>
  <p>
   When XA transactions are detached, a connection has no special
        knowledge of any XA transaction that it has prepared. If the
        current session tries to commit or roll back a given XA
        transaction (even one which it prepared) after another
        connection has already done so, the attempt is rejected with an
        invalid XID error (
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_xaer_nota" target="_top">
    <code class="literal">
     ER_XAER_NOTA
    </code>
   </a>
   )
        since the requested
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   no longer
        exists.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Detached XA transactions cannot use temporary tables.
   </p>
  </div>
  <p>
   When detached XA transactions are disabled
        (
   <code class="literal">
    xa_detach_on_prepare
   </code>
   set to
   <code class="literal">
    OFF
   </code>
   ), an XA transaction remains connected
        until it is committed or rolled back by the originating
        connection. Disabling detached XA transactions is not
        recommended for a MySQL server instance used in group
        replication; see
   <a class="xref" href="group-replication-requirements.html#group-replication-configuration" title="Server Instance Configuration">
    Server Instance Configuration
   </a>
   , for more
        information.
  </p>
  <p>
   If an XA transaction is in the
   <code class="literal">
    ACTIVE
   </code>
   state,
        you cannot issue any statements that cause an implicit commit.
        That would violate the XA contract because you could not roll
        back the XA transaction. Trying to execute such a statement
        raises the following error:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa761279"><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 1399 (XAE07): XAER_RMFAIL: The command cannot be executed
when global transaction is in the ACTIVE state</code></pre>
  </div>
  <p>
   Statements to which the preceding remark applies are listed at
   <a class="xref" href="implicit-commit.html" title="15.3.3 Statements That Cause an Implicit Commit">
    Section 15.3.3, “Statements That Cause an Implicit Commit”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
