MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
XA transaction support is limited to the
InnoDB
storage engine.
For “external XA,” a MySQL server acts as a
Resource Manager and client programs act as Transaction
Managers. For “Internal XA”, storage engines within
a MySQL server act as RMs, and the server itself acts as a TM.
Internal XA support is limited by the capabilities of individual
storage engines. Internal XA is required for handling XA
transactions that involve more than one storage engine. The
implementation of internal XA requires that a storage engine
support two-phase commit at the table handler level, and
currently this is true only for InnoDB
.
For XA
START
, the JOIN
and
RESUME
clauses are recognized but have no
effect.
For XA
END
the SUSPEND [FOR MIGRATE]
clause is recognized but has no effect.
The requirement that the bqual
part
of the xid
value be different for
each XA transaction within a global transaction is a limitation
of the current MySQL XA implementation. It is not part of the XA
specification.
Prior to MySQL 5.7.7, XA transactions were not compatible with
replication. This was because an XA transaction that was in
PREPARED
state would be rolled back on clean
server shutdown or client disconnect. Similarly, an XA
transaction that was in PREPARED
state would
still exist in PREPARED
state in case the
server was shutdown abnormally and then started again, but the
contents of the transaction could not be written to the binary
log. In both of these situations the XA transaction could not be
replicated correctly.
In MySQL 5.7.7 and later, there is a change in behavior and an
XA transaction is written to the binary log in two parts. When
XA PREPARE
is issued, the first part of the
transaction up to XA PREPARE
is written using
an initial GTID. A XA_prepare_log_event
is
used to identify such transactions in the binary log. When
XA COMMIT
or XA ROLLBACK
is issued, a second part of the transaction containing only the
XA COMMIT
or XA ROLLBACK
statement is written using a second GTID. Note that the initial
part of the transaction, identified by
XA_prepare_log_event
, is not necessarily
followed by its XA COMMIT
or XA
ROLLBACK
, which can cause interleaved binary logging
of any two XA transactions. The two parts of the XA transaction
can even appear in different binary log files. This means that
an XA transaction in PREPARED
state is now
persistent until an explicit XA COMMIT
or
XA ROLLBACK
statement is issued, ensuring
that XA transactions are compatible with replication.
On a replica, immediately after the XA transaction is prepared,
it is detached from the replica applier thread, and can be
committed or rolled back by any thread on the replica. This
means that the same XA transaction can appear in the
events_transactions_current
table
with different states on different threads. The
events_transactions_current
table
displays the current status of the most recent monitored
transaction event on the thread, and does not update this status
when the thread is idle. So the XA transaction can still be
displayed in the PREPARED
state for the
original applier thread, after it has been processed by another
thread. To positively identify XA transactions that are still in
the PREPARED
state and need to be recovered,
use the XA
RECOVER
statement rather than the Performance Schema
transaction tables.
The following restrictions exist for using XA transactions in MySQL 5.7.7 and later:
XA transactions are not fully resilient to an unexpected
halt with respect to the binary log. If there is an
unexpected halt while the server is in the middle of
executing an XA PREPARE
, XA
COMMIT
, XA ROLLBACK
, or
XA COMMIT ... ONE PHASE
statement, the
server might not be able to recover to a correct state,
leaving the server and the binary log in an inconsistent
state. In this situation, the binary log might either
contain extra XA transactions that are not applied, or miss
XA transactions that are applied. Also, if GTIDs are
enabled, after recovery
@@GLOBAL.GTID_EXECUTED
might not
correctly describe the transactions that have been applied.
Note that if an unexpected halt occurs before XA
PREPARE
, between XA PREPARE
and
XA COMMIT
(or XA
ROLLBACK
), or after XA COMMIT
(or XA ROLLBACK
), the server and binary
log are correctly recovered and taken to a consistent state.
The use of replication filters or binary log filters in
combination with XA transactions is not supported. Filtering
of tables could cause an XA transaction to be empty on a
replica, and empty XA transactions are not supported. Also,
with the settings
master_info_repository=TABLE
and
relay_log_info_repository=TABLE
on a replica, which became the defaults in MySQL 8.0, the
internal state of the data engine transaction is changed
following a filtered XA transaction, and can become
inconsistent with the replication transaction context state.
The error
ER_XA_REPLICATION_FILTERS
is
logged whenever an XA transaction is impacted by a
replication filter, whether or not the transaction was empty
as a result. If the transaction is not empty, the replica is
able to continue running, but you should take steps to
discontinue the use of replication filters with XA
transactions in order to avoid potential issues. If the
transaction is empty, the replica stops. In that event, the
replica might be in an undetermined state in which the
consistency of the replication process might be compromised.
In particular, the gtid_executed
set on a
replica of the replica might be inconsistent with that on
the source. To resolve this situation, isolate the source
and stop all replication, then check GTID consistency across
the replication topology. Undo the XA transaction that
generated the error message, then restart replication.
Prior to MySQL 5.7.19, FLUSH TABLES
WITH READ LOCK
is not compatible with XA
transactions.
XA transactions are considered unsafe for statement-based
replication. If two XA transactions committed in parallel on
the source are being prepared on the replica in the inverse
order, locking dependencies can occur that cannot be safely
resolved, and it is possible for replication to fail with
deadlock on the replica. This situation can occur for a
single-threaded or multithreaded replica. When
binlog_format=STATEMENT
is
set, a warning is issued for DML statements inside XA
transactions. When
binlog_format=MIXED
or
binlog_format=ROW
is set,
DML statements inside XA transactions are logged using
row-based replication, and the potential issue is not
present.