To perform XA transactions in MySQL, use the following statements:
XA {START|BEGIN}xid
[JOIN|RESUME] XA ENDxid
[SUSPEND [FOR MIGRATE]] XA PREPARExid
XA COMMITxid
[ONE PHASE] XA ROLLBACKxid
XA RECOVER
For XA
START
, the JOIN
and
RESUME
clauses are not supported.
For XA
END
the SUSPEND [FOR MIGRATE]
clause is not supported.
Each XA statement begins with the XA
keyword,
and most of them require an xid
value. An xid
is an XA transaction
identifier. It indicates which transaction the statement applies
to. xid
values are supplied by the
client, or generated by the MySQL server. An
xid
value has from one to three
parts:
xid
:gtrid
[,bqual
[,formatID
]]
gtrid
is a global transaction
identifier, bqual
is a branch
qualifier, and formatID
is a number
that identifies the format used by the
gtrid
and
bqual
values. As indicated by the
syntax, bqual
and
formatID
are optional. The default
bqual
value is ''
if not given. The default formatID
value is 1 if not given.
gtrid
and
bqual
must be string literals, each
up to 64 bytes (not characters) long.
gtrid
and
bqual
can be specified in several
ways. You can use a quoted string ('ab'
), hex
string (0x6162
, X'ab'
), or
bit value
(b'
).
nnnn
'
formatID
is an unsigned integer.
The gtrid
and
bqual
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 gtrid
and
bqual
as hex strings.
xid
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 xid
values
in a list of values returned by the
XA
RECOVER
statement.
XA START
starts an XA transaction with the given
xid
xid
value. Each XA transaction must
have a unique xid
value, so the value
must not currently be used by another XA transaction. Uniqueness
is assessed using the gtrid
and
bqual
values. All following XA
statements for the XA transaction must be specified using the
same xid
value as that given in the
XA
START
statement. If you use any of those statements
but specify an xid
value that does
not correspond to some existing XA transaction, an error occurs.
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 gtrid
value in the xid
value. For this
reason, gtrid
values must be globally
unique so that there is no ambiguity about which global
transaction a given XA transaction is part of. The
bqual
part of the
xid
value must be different for each
XA transaction within a global transaction. (The requirement
that bqual
values be different is a
limitation of the current MySQL XA implementation. It is not
part of the XA specification.)
The XA
RECOVER
statement returns information for those XA
transactions on the MySQL server that are in the
PREPARED
state. (See
Section 12.3.7.2, “XA Transaction States”.) The output includes a row for each
such XA transaction on the server, regardless of which client
started it.
XA
RECOVER
output rows look like this (for an example
xid
value consisting of the parts
'abc'
, 'def'
, and
7
):
mysql> XA RECOVER;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+--------+
| 7 | 3 | 3 | abcdef |
+----------+--------------+--------------+--------+
The output columns have the following meanings:
formatID
is the
formatID
part of the transaction
xid
gtrid_length
is the length in bytes of
the gtrid
part of the
xid
bqual_length
is the length in bytes of
the bqual
part of the
xid
data
is the concatenation of the
gtrid
and
bqual
parts of the
xid
User Comments
If you have a failed XA Transaction, it will show as "ACTIVE (PREPARED)" with process no 0 and thread id 0:
1 row in set (2.13 sec)mysql> show engine innodb status\G
....
---TRANSACTION 0 1192549934, ACTIVE (PREPARED) 791 sec, process no 0, OS thread id 0
1 lock struct(s), heap size 368, undo log entries 3
To rollback the transaction, first get its xid:
mysql> xa recover;
The xid is present in this output, but you have to perform a little string manipulation to get it. The format of a xid is: gtrid,bqual,formatID. The column 'data' contains a concatenation of 'gtrid' and 'bqual'. The columns 'gtrid_length' and 'bqual_length' specify how many bytes each of these values uses; use them to split apart 'data'. In this example, the result is:
mysql> xa rollback '1-a00640d:c09d:4ac454ef:b284c0','a00640d:c09d:4ac454ef:b284c2',131075;
ERROR 1402 (XA100): XA_RBROLLBACK: Transaction branch was rolled back
Add your own comment.