15.3.8.1 XA Transaction SQL Statements (original) (raw)

15.3.8.1 XA Transaction SQL Statements

To perform XA transactions in MySQL, use the following statements:

XA {START|BEGIN} xid [JOIN|RESUME]

XA END xid [SUSPEND [FOR MIGRATE]]

XA PREPARE xid

XA COMMIT xid [ONE PHASE]

XA ROLLBACK xid

XA RECOVER [CONVERT XID]

For XA START, the JOIN andRESUME clauses are recognized but have no effect.

For XA END the SUSPEND [FOR MIGRATE] clause is recognized but has no effect.

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 (X'6162', 0x6162), 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 theXA RECOVER statement.

XA START_xid_ starts an XA transaction with the given 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 theXA 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.

Beginning with MySQL 8.0.31, XA START,XA BEGIN, XA END,XA COMMIT, and XA ROLLBACK statements are not filtered by the default database when the server is running with--replicate-do-db or--replicate-ignore-db.

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 thePREPARED state. (SeeSection 15.3.8.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 requires theXA_RECOVER_ADMIN 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.

XA RECOVER output rows look like this (for an example_xid_ value consisting of the parts'abc', 'def', and7):

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_

XID values may contain nonprintable characters.XA RECOVER permits an optional CONVERT XID clause so that clients can request XID values in hexadecimal.