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
RESUME clauses are recognized but have no
SUSPEND [FOR MIGRATE]
clause is recognized but has no effect.
The requirement that 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
An XA transaction is written to the binary log in two parts.
XA PREPARE is issued, the first part of
the transaction up to
XA PREPARE is written
using an initial GTID. A
is used to identify such transactions in the binary log. When
XA COMMIT or
is issued, a second part of the transaction containing only the
XA COMMIT or
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
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 replication slave, immediately after the XA transaction is
prepared, it is detached from the slave applier thread, and can
be committed or rolled back by any thread on the slave. This
means that the same XA transaction can appear in the
with different states on different threads. The
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
PREPARED state and need to be recovered,
RECOVER statement rather than the Performance Schema
The following restrictions exist for using XA transactions:
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 ROLLBACK, or
XA COMMIT ... ONE PHASEstatement, 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_EXECUTEDmight not correctly describe the transactions that have been applied. Note that if an unexpected halt occurs before
XA PREPARE, between
XA ROLLBACK), or after
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 replication slave, and empty XA transactions are not supported. Also, with the settings
relay_log_info_repository=TABLEon a replication slave, 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.
ER_XA_REPLICATION_FILTERSis 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 replication slave 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 replication slave stops. In that event, the replication slave might be in an undetermined state in which the consistency of the replication process might be compromised. In particular, the
gtid_executedset on a slave of the slave might be inconsistent with that on the master. To resolve this situation, isolate the master and stop all replication, then check GTID consistency across the replication topology. Undo the XA transaction that generated the error message, then restart replication.
XA transactions are considered unsafe for statement-based replication. If two XA transactions committed in parallel on the master are being prepared on the slave 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 slave. This situation can occur for a single-threaded or multithreaded replication slave. When
binlog_format=STATEMENTis set, a warning is issued for DML statements inside XA transactions. When
binlog_format=ROWis set, DML statements inside XA transactions are logged using row-based replication, and the potential issue is not present.
Prior to MySQL 5.7.7, XA transactions were not compatible with
replication at all. This was because an XA transaction that
PREPARED state would be rolled back
on clean server shutdown or client disconnect. Similarly, an
XA transaction that was in
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.