Understanding and Practicing MySQL XA Distributed Transactions

MySQL has supported XA (distributed) transactions since version 5.0.3, implementing the X/Open CAE specification for coordinated transaction processing across multiple resource managers.

The XA model involves a Transaction Manager (TM) that coordinates several Resource Managers (RMs); each RM represents a database that can commit or roll back its part of the transaction.

Distributed transactions follow a two‑phase commit (2PC) protocol: in the first phase all branches are prepared, and in the second phase the TM decides to commit or roll back each branch based on the preparation results.

MySQL’s XA statements all start with the XA keyword and require an XID identifier composed of gtrid (global transaction ID), bqual (branch qualifier), and formatID :

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]

Example scenario: a user "Xiao Jie" has a savings account in database cash_gftest and an investment account in financing_gftest . The goal is to transfer 3000 units from savings to investment as a single atomic operation.

Setup:

create database cash_gftest default charset utf8;
use cash_gftest;
create table cash_account(name varchar(10), balance decimal(10,2)) engine=innodb;
insert into cash_account values('郭佳',10000),('刘强',8000),('肖杰',22000);
create database financing_gftest default charset utf8;
use financing_gftest;
create table financing_account(name varchar(10), balance decimal(10,2)) engine=innodb;
insert into financing_account values('郭佳',0),('刘强',0),('肖杰',0);

Two client sessions are opened, each connected to one of the databases. Session 1 (cash) starts the XA branch, updates the balance, ends and prepares the branch:

xa start 'transfer_of_account','cash';
update cash_account set balance=balance-3000 where name='肖杰';
xa end 'transfer_of_account','cash';
xa prepare 'transfer_of_account','cash';

Session 2 (financing) performs the complementary operations:

xa start 'transfer_of_account','financing';
update financing_account set balance=balance+3000 where name='肖杰';
xa end 'transfer_of_account','financing';
xa prepare 'transfer_of_account','financing';

Both sessions can query xa recover to see the prepared branches, confirming that each branch is ready for the final decision.

Finally, the TM commits both branches:

xa commit 'transfer_of_account','cash';
xa commit 'transfer_of_account','financing';

The successful commit demonstrates that MySQL fully supports distributed transactions, ensuring atomicity across multiple databases.