Mastering MySQL XA Transactions: Principles, Syntax, and Real-World Examples

In production MySQL deployments that use sharding, local transactions are insufficient, so MySQL provides a distributed transaction solution called XA transactions, which is supported only by the InnoDB storage engine.

XA enables multiple resource managers (RM) to participate in a global transaction coordinated by a transaction manager (TM). The RM (e.g., a database server) must be able to commit or roll back, while the TM communicates with each RM to ensure all branches either commit or roll back as a single unit.

MySQL can act as the RM, and client programs act as the TM. A global transaction succeeds only if every participating branch can commit; otherwise, all branches are rolled back.

XA Distributed Transaction Principle: 2PC (Two‑Phase Commit)

The two‑phase commit protocol consists of:

Prepare phase – the TM asks all RMs to prepare for commit.

Commit/rollback phase – the TM instructs RMs to either commit or roll back based on the prepare results.

If any branch cannot prepare, the TM orders a rollback for all branches.

External XA vs. Internal XA

External XA spans multiple MySQL instances and requires the application layer (e.g., PHP) to act as the coordinator. It is used by distributed database proxies such as NetEase DDB or Taobao TDDL.

Internal XA occurs within a single MySQL instance across multiple storage engines, coordinated by the binary log (binlog). The binlog records the commit but not the prepare phase, guaranteeing that a prepared transaction has already completed its prepare in each engine.

Basic XA Syntax

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]

Each XA statement begins with the keyword XA and usually requires an xid (transaction identifier) supplied by the client or generated by MySQL.

XA Transaction Examples

Internal XA Simple Example

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)

External XA Example (PHP)

<?php
$xatest1 = new mysqli("192.168.33.10","root","123456","xatest1") or die("xatest1 connection failed");
$xatest2 = new mysqli("192.168.33.11","root","123456","xatest2") or die("xatest2 connection failed");
$xid = uniqid();
$xatest1->query("XA START '$xid'");
$xatest2->query("XA START '$xid'");
try {
    $xatest1->query("UPDATE db1 SET class=1 WHERE id=1");
    $xatest2->query("UPDATE db2 SET grade=2 WHERE id=1");
    $xatest1->query("XA END '$xid'");
    $xatest1->query("XA PREPARE '$xid'");
    $xatest2->query("XA END '$xid'");
    $xatest2->query("XA PREPARE '$xid'");
    $xatest1->query("XA COMMIT '$xid'");
    $xatest2->query("XA COMMIT '$xid'");
} catch (Exception $e) {
    $xatest1->query("XA ROLLBACK '$xid'");
    $xatest2->query("XA ROLLBACK '$xid'");
    echo $e->getMessage();
}
$xatest1->close();
$xatest2->close();

Common Issues with XA Transactions

XA and non‑XA transactions are mutually exclusive on the same client connection; you cannot start a local transaction while an XA transaction is active.

In master‑slave replication, the binlog (which coordinates internal XA) writes only commit records, not prepare records. If a crash occurs after all branches have prepared but before commit, the master may consider the transaction committed while the binlog lacks the changes, causing data inconsistency between master and slave.

Recovery after a crash may lead to inconsistent states if some nodes have committed and others have rolled back.

Workarounds include avoiding replication for backup and using XA for synchronous writes, or logging prepared transactions to separate files instead of the binlog.

Performance

XA transactions have poor performance; using a high‑throughput messaging middleware is recommended for achieving eventual consistency in distributed systems.

References

MySQL XA official documentation: http://dev.mysql.com/doc/refman/5.7/en/xa.html

XA SQL syntax: https://dev.mysql.com/doc/refman/5.7/en/xa-statements.html

Distributed TP: XA specification: https://publications.opengroup.org/c193

XA limitations: https://dev.mysql.com/doc/mysql-reslimits-excerpt/8.0/en/xa-restrictions.html