MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
An XA transaction progresses through the following states:
Use XA
START
to start an XA transaction and put it in the
ACTIVE
state.
For an ACTIVE
XA transaction, issue the
SQL statements that make up the transaction, and then issue
an XA
END
statement.
XA
END
puts the transaction in the
IDLE
state.
For an IDLE
XA transaction, you can issue
either an XA
PREPARE
statement or an XA COMMIT ... ONE
PHASE
statement:
XA
PREPARE
puts the transaction in the
PREPARED
state. An
XA
RECOVER
statement at this point includes the
transaction's xid
value
in its output, because
XA
RECOVER
lists all XA transactions that are in
the PREPARED
state.
XA COMMIT ... ONE PHASE
prepares and
commits the transaction. The
xid
value is not listed by
XA
RECOVER
because the transaction terminates.
For a PREPARED
XA transaction, you can
issue an XA
COMMIT
statement to commit and terminate the
transaction, or
XA
ROLLBACK
to roll back and terminate the
transaction.
Here is a simple XA transaction that inserts a row into a table as part of a global transaction:
mysql>XA START 'xatest';
Query OK, 0 rows affected (0.00 sec) mysql>INSERT INTO mytable (i) VALUES(10);
Query OK, 1 row affected (0.04 sec) mysql>XA END 'xatest';
Query OK, 0 rows affected (0.00 sec) mysql>XA PREPARE 'xatest';
Query OK, 0 rows affected (0.00 sec) mysql>XA COMMIT 'xatest';
Query OK, 0 rows affected (0.00 sec)
Within the context of a given client connection, XA transactions
and local (non-XA) transactions are mutually exclusive. For
example, if XA
START
has been issued to begin an XA transaction, a
local transaction cannot be started until the XA transaction has
been committed or rolled back. Conversely, if a local
transaction has been started with
START
TRANSACTION
, no XA statements can be used until the
transaction has been committed or rolled back.
If an XA transaction is in the ACTIVE
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:
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
Statements to which the preceding remark applies are listed at Section 13.3.3, “Statements That Cause an Implicit Commit”.