<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="xa-statements">
      </a>
      15.3.8.1 XA Transaction SQL Statements
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045178110320">
  </a>
  <a class="indexterm" name="idm46045178109280">
  </a>
  <a class="indexterm" name="idm46045178107792">
  </a>
  <a class="indexterm" name="idm46045178106720">
  </a>
  <a class="indexterm" name="idm46045178105232">
  </a>
  <a class="indexterm" name="idm46045178104160">
  </a>
  <a class="indexterm" name="idm46045178102672">
  </a>
  <a class="indexterm" name="idm46045178101600">
  </a>
  <a class="indexterm" name="idm46045178100112">
  </a>
  <a class="indexterm" name="idm46045178099040">
  </a>
  <a class="indexterm" name="idm46045178097552">
  </a>
  <a class="indexterm" name="idm46045178096480">
  </a>
  <a class="indexterm" name="idm46045178094992">
  </a>
  <a class="indexterm" name="idm46045178093520">
  </a>
  <p>
   To perform XA transactions in MySQL, use the following
        statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88661301"><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 keyword">XA</span> {<span class="token keyword">START</span><span class="token operator">|</span><span class="token keyword">BEGIN</span>} <span class="token keyword"><em class="replaceable">xid</em></span> <span class="token punctuation">[</span><span class="token keyword">JOIN</span><span class="token operator">|</span><span class="token keyword">RESUME</span><span class="token punctuation">]</span>

<span class="token keyword">XA</span> <span class="token keyword">END</span> <span class="token keyword"><em class="replaceable">xid</em></span> <span class="token punctuation">[</span><span class="token keyword">SUSPEND</span> <span class="token punctuation">[</span><span class="token keyword">FOR</span> <span class="token keyword">MIGRATE</span><span class="token punctuation">]</span><span class="token punctuation">]</span>

<span class="token keyword">XA</span> <span class="token keyword">PREPARE</span> <span class="token keyword"><em class="replaceable">xid</em></span>

<span class="token keyword">XA</span> <span class="token keyword">COMMIT</span> <span class="token keyword"><em class="replaceable">xid</em></span> <span class="token punctuation">[</span><span class="token keyword">ONE</span> <span class="token keyword">PHASE</span><span class="token punctuation">]</span>

<span class="token keyword">XA</span> <span class="token keyword">ROLLBACK</span> <span class="token keyword"><em class="replaceable">xid</em></span>

<span class="token keyword">XA</span> <span class="token keyword">RECOVER</span> <span class="token punctuation">[</span>CONVERT <span class="token keyword">XID</span><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   For
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA
        START
    </code>
   </a>
   , the
   <code class="literal">
    JOIN
   </code>
   and
   <code class="literal">
    RESUME
   </code>
   clauses are recognized but have no
        effect.
  </p>
  <p>
   For
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA
        END
    </code>
   </a>
   the
   <code class="literal">
    SUSPEND [FOR MIGRATE]
   </code>
   clause is recognized but has no effect.
  </p>
  <p>
   Each XA statement begins with the
   <code class="literal">
    XA
   </code>
   keyword,
        and most of them require an
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   value. An
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   is an XA transaction
        identifier. It indicates which transaction the statement applies
        to.
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   values are supplied by the
        client, or generated by the MySQL server. An
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   value has from one to three
        parts:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa73051315"><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"><em class="replaceable">xid</em>: <em class="replaceable">gtrid</em> [, <em class="replaceable">bqual</em> [, <em class="replaceable">formatID</em> ]]</code></pre>
  </div>
  <p>
   <em class="replaceable">
    <code>
     gtrid
    </code>
   </em>
   is a global transaction
        identifier,
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   is a branch
        qualifier, and
   <em class="replaceable">
    <code>
     formatID
    </code>
   </em>
   is a number
        that identifies the format used by the
   <em class="replaceable">
    <code>
     gtrid
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   values. As indicated by the
        syntax,
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     formatID
    </code>
   </em>
   are optional. The default
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   value is
   <code class="literal">
    ''
   </code>
   if not given. The default
   <em class="replaceable">
    <code>
     formatID
    </code>
   </em>
   value is 1 if not given.
  </p>
  <p>
   <em class="replaceable">
    <code>
     gtrid
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   must be string literals, each
        up to 64 bytes (not characters) long.
   <em class="replaceable">
    <code>
     gtrid
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   can be specified in several
        ways. You can use a quoted string (
   <code class="literal">
    'ab'
   </code>
   ), hex
        string (
   <code class="literal">
    X'6162'
   </code>
   ,
   <code class="literal">
    0x6162
   </code>
   ),
        or bit value
        (
   <code class="literal">
    b'
    <em class="replaceable">
     <code>
      nnnn
     </code>
    </em>
    '
   </code>
   ).
  </p>
  <p>
   <em class="replaceable">
    <code>
     formatID
    </code>
   </em>
   is an unsigned integer.
  </p>
  <p>
   The
   <em class="replaceable">
    <code>
     gtrid
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   values are interpreted in bytes
        by the MySQL server's underlying XA support routines. However,
        while an SQL statement containing an XA statement is being
        parsed, the server works with some specific character set. To be
        safe, write
   <em class="replaceable">
    <code>
     gtrid
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   as hex strings.
  </p>
  <p>
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   values typically are generated by
        the Transaction Manager. Values generated by one TM must be
        different from values generated by other TMs. A given TM must be
        able to recognize its own
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   values
        in a list of values returned by the
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA
        RECOVER
    </code>
   </a>
   statement.
  </p>
  <p>
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA START
     <em class="replaceable">
      <code>
       xid
      </code>
     </em>
    </code>
   </a>
   starts an XA
        transaction with the given
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   value.
        Each XA transaction must have a unique
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   value, so the value must not
        currently be used by another XA transaction. Uniqueness is
        assessed using the
   <em class="replaceable">
    <code>
     gtrid
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   values. All following XA
        statements for the XA transaction must be specified using the
        same
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   value as that given in the
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA
        START
    </code>
   </a>
   statement. If you use any of those statements
        but specify an
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   value that does
        not correspond to some existing XA transaction, an error occurs.
  </p>
  <p>
   <code class="literal">
    XA START
   </code>
   ,
   <code class="literal">
    XA BEGIN
   </code>
   ,
   <code class="literal">
    XA END
   </code>
   ,
   <code class="literal">
    XA COMMIT
   </code>
   , and
   <code class="literal">
    XA ROLLBACK
   </code>
   statements are not filtered by
        the default database when the server is running with
   <a class="link" href="replication-options-replica.html#option_mysqld_replicate-do-db">
    <code class="option">
     --replicate-do-db
    </code>
   </a>
   or
   <a class="link" href="replication-options-replica.html#option_mysqld_replicate-ignore-db">
    <code class="option">
     --replicate-ignore-db
    </code>
   </a>
   .
  </p>
  <p>
   One or more XA transactions can be part of the same global
        transaction. All XA transactions within a given global
        transaction must use the same
   <em class="replaceable">
    <code>
     gtrid
    </code>
   </em>
   value in the
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   value. For this
        reason,
   <em class="replaceable">
    <code>
     gtrid
    </code>
   </em>
   values must be globally
        unique so that there is no ambiguity about which global
        transaction a given XA transaction is part of. The
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   part of the
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   value must be different for each
        XA transaction within a global transaction. (The requirement
        that
   <em class="replaceable">
    <code>
     bqual
    </code>
   </em>
   values be different is a
        limitation of the current MySQL XA implementation. It is not
        part of the XA specification.)
  </p>
  <p>
   The
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA
        RECOVER
    </code>
   </a>
   statement returns information for those XA
        transactions on the MySQL server that are in the
   <code class="literal">
    PREPARED
   </code>
   state. (See
   <a class="xref" href="xa-states.html" title="15.3.8.2 XA Transaction States">
    Section 15.3.8.2, “XA Transaction States”
   </a>
   .) The output includes a row for each
        such XA transaction on the server, regardless of which client
        started it.
  </p>
  <p>
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA
        RECOVER
    </code>
   </a>
   requires the
   <a class="link" href="privileges-provided.html#priv_xa-recover-admin">
    <code class="literal">
     XA_RECOVER_ADMIN
    </code>
   </a>
   privilege. This
        privilege requirement prevents users from discovering the XID
        values for outstanding prepared XA transactions other than their
        own. It does not affect normal commit or rollback of an XA
        transaction because the user who started it knows its XID.
  </p>
  <p>
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA
        RECOVER
    </code>
   </a>
   output rows look like this (for an example
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   value consisting of the parts
   <code class="literal">
    'abc'
   </code>
   ,
   <code class="literal">
    'def'
   </code>
   , and
   <code class="literal">
    7
   </code>
   ):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72239386"><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">RECOVER</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> formatID <span class="token punctuation">|</span> gtrid_length <span class="token punctuation">|</span> bqual_length <span class="token punctuation">|</span> data   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>        7 <span class="token punctuation">|</span>            3 <span class="token punctuation">|</span>            3 <span class="token punctuation">|</span> abcdef <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The output columns have the following meanings:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       formatID
      </code>
      is the
      <em class="replaceable">
       <code>
        formatID
       </code>
      </em>
      part of the transaction
      <em class="replaceable">
       <code>
        xid
       </code>
      </em>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       gtrid_length
      </code>
      is the length in bytes of
            the
      <em class="replaceable">
       <code>
        gtrid
       </code>
      </em>
      part of the
      <em class="replaceable">
       <code>
        xid
       </code>
      </em>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       bqual_length
      </code>
      is the length in bytes of
            the
      <em class="replaceable">
       <code>
        bqual
       </code>
      </em>
      part of the
      <em class="replaceable">
       <code>
        xid
       </code>
      </em>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       data
      </code>
      is the concatenation of the
      <em class="replaceable">
       <code>
        gtrid
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        bqual
       </code>
      </em>
      parts of the
      <em class="replaceable">
       <code>
        xid
       </code>
      </em>
     </p>
    </li>
   </ul>
  </div>
  <p>
   XID values may contain nonprintable characters.
   <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
    <code class="literal">
     XA
        RECOVER
    </code>
   </a>
   permits an optional
   <code class="literal">
    CONVERT
        XID
   </code>
   clause so that clients can request XID values in
        hexadecimal.
  </p>
 </div>
 <br/>
</div>
