详解MySQL分布式事务原理及在MySQL和MyCAT中的实现，附Python代码示例。
原文标题：MySQL分布式事务原理及实现
原文作者：牧羊人的方向
冷月清谈：
XA事务模型涉及应用程序（AP）、资源管理器（RM）和事务管理器（TM）。AP定义事务边界并访问资源，RM管理共享资源（如数据库），TM管理全局事务，包括提交和回滚。
两阶段提交协议的第一阶段，TM通知RM准备提交，RM执行操作但不提交。第二阶段，TM根据RM的反馈决定提交或回滚。XA事务的缺点是存在超时问题，MySQL的XA事务实现也存在主从复制数据不一致的风险。
MySQL支持外部XA（跨多实例）和内部XA（同一实例下跨引擎）。文章给出了MySQL XA事务的语法，包括XA START、XA END、XA PREPARE、XA COMMIT和XA ROLLBACK。同时指出了XA事务与本地事务和锁表操作互斥的限制。
文章提供了Python代码示例，演示了在单节点MySQL中使用XA事务转账的场景，并分析了MySQL事务状态的变化。此外，文章还介绍了MyCAT对XA分布式事务的支持，包括其流程、异常处理和缺陷。
怜星夜思：
2、MyCAT 的 XA 事务实现有哪些改进空间？除了文章提到的缺陷，大家在实际使用过程中还遇到过哪些问题？
3、除了MySQL和MyCAT，还有哪些常用的分布式事务解决方案？它们各自有什么优缺点？
原文内容
MySQL 5.0版本后开始支持XA分布式事务，本文简要介绍XA事务原理及MySQL和MyCAT中XA事务的实现，并使用Python程序案例进行测试验证。
1、XA事务原理
分布式事务处理是指一个程序或程序段，在一个资源或多个资源上为完成某些功能的执行过程的集合。分布式事务处理的关键是必须有一种方法可以知道事务在任何地方所做的动作，提交或回滚事务的决定必须产生统一结果。X/Open定义了分布式事务处理模型，包括应用程序AP、事务管理器TM、资源管理器RM、通信资源管理器CRM。
-
应用程序（Application Program）：定义事务边界（定义事务开始和结束）并访问事务边界内的资源
-
资源管理器（Resource Manager）：RM管理计算机共享的资源，资源包含比如数据库、文件系统等
-
事务管理器(Transaction Manager ，简称TM)：负责管理全局事务，分配事务唯一标识，监控事务的执行进度，并负责事务的提交、回滚、失败恢复等。
2）分布式事务的流程如下：
-
配置TM，将RM注册到TM，即给TM注册RM为数据源
-
AP从TM获取资源管理器的代理，获取TM所管理的RM的JDBC连接
-
AP向TM发起全局事务
-
TM将XID通知到各RM
-
AP通过链接直接对RM进行操作
-
AP结束全局事务
-
TM会通知RM全局事务结束
-
开始二阶段提交
-
xa_start：负责开启或者恢复一个事务分支，并且管理XID到调用线程
-
xa_end：负责取消当前线程与事务分支的关联
-
xa_prepare：负责询问RM 是否准备好了提交事务分支
-
xa_commit：通知RM提交事务分支
-
xa_rollback：通知RM回滚事务分支
-
xa_close：结束使用RM
-
xa_open：AP初始化RM
4）XA协议是使用了二阶段协议
-
应用程序调用了事务管理器的提交方法，此后第一阶段分为两个步骤：
-
事务管理器通知参与该事务的各个资源管理器，通知他们开启事务、执行SQL（暂不提交），并进入prepare状态（该状态下可执行commit / rollback）。
-
资源管理器接收到消息后开始准备阶段，写好事务日志并执行事务，但不提交，然后将是否就绪的消息返回给事务管理器
-
RM根据自己的情况，如果判断自己进行的工作可以被提交，那就就对工作内容进行持久化，并给TM回执OK；否者给TM的回执NO
-
RM在发送了否定答复并回滚了已经的工作后，就可以丢弃这个事务分支信息了
-
第二阶段也分为两个步骤：
-
事务管理器在接受各个消息后，开始分析，如果有任意其一失败，则发送回滚命令，否则发送提交命令。
-
各个资源管理器接收到命令后，执行（耗时很少），并将提交消息返回给事务管理器。
两阶段提交的好处是有了事务管理器进行统一管理，让事务在提交前尽可能的完成所有能完成的工作。同时两阶段提交可以保证事务的一致性，不管是事务管理器还是各个资源管理器，每执行一步操作都会被日志记录，为出现故障后的恢复提供依据。
5）XA事务的问题和MySQL的局限性
XA事务明显的问题是timeout问题，因为事务管理器要收集各个资源管理器的响应消息，如果其中一个或多个一直不返回消息，则事务管理器一直等待，应用程序也被阻塞。
MySQL中的XA事务，长期起来存在一个缺陷：MySQL中数据库的主从复制是通过binlog复制完成的，而binlog是MySQL数据库内部XA事务的协调者，并且MySQL数据库为binlog做了优化，binlog不写prepare日志只写commit日志。如果所有的节点prepare完成，但是在commit前crash，如果crash恢复的时候选择commit，由于binlog在prepare阶段未写，在主库看来此分布式事务最终提交了，但是此事务的操作并未写到binlog中，因此也就未能成功复制到备库，从而导致主备库数据不一致的情况出现。
2、MySQL中XA实现
MYSQL的数据库存储引擎InnoDB的事务特性能够保证在存储引擎级别实现ACID，而分布式事务让存储引擎级别的事务扩展到数据库层面，甚至扩展到多个数据库之间，这是通过两阶段提交协议来实现的，MySQL 5.0或者更新版本开始支持XA事务，从下图可知MySQL中只有InnoDB引擎支持XA协议：
-
外部XA用于跨多MySQL实例的分布式事务，需要应用层作为协调者，应用层负责决定提交还是回滚崩溃时的悬挂事务。MySQL数据库外部XA可以用在分布式数据库代理层，实现对MySQL数据库的分布式事务支持
-
内部XA事务用于同一实例下跨多引擎事务，由Binlog作为协调者，比如在一个存储引擎提交时，需要将提交信息写入二进制日志，这就是一个分布式内部XA事务，只不过二进制日志的参与者是MySQL本身。Binlog作为内部XA的协调者，在binlog中出现的内部xid，在crash recover时，由binlog负责提交。(这是因为，binlog不进行prepare，只进行commit，因此在binlog中出现的内部xid，一定能够保证其在底层各存储引擎中已经完成prepare)。
1）首先确保MySQL开启XA事务支持
mysql> SHOW VARIABLES LIKE '%xa%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_support_xa | ON |
+------------------------+-------+
innodb_support_xa的值是ON就说明mysql已经开启对XA事务的支持了，如果不是可以使用命令“SET innodb_support_xa=ON”开启
2）XA事务的语法有以下
XA START 'xid'; // 'xid' 是用户给的，全局唯一在一台mysql中开启一个XA事务
XA END 'xid '; //标识XA事务的操作结束
XA PREPARE 'xid'; //告知mysql 准备提交这个xa事务
XA COMMIT 'xid'; //告知mysql提交这个xa事务
XA ROLLBACK 'xid'; //告知mysql回滚这个xa事务
XA RECOVER;//查看本机mysql目前有哪些xa事务处于prepare状态
其中xid是一个全局唯一的id标示一个分支事务，每个分支事务有自己的全局唯一的一个id,是一个字符串
-
如果这个xa事务commit了，那么什么也不用做
-
如果这个xa事务还没有prepare，那么直接回滚它
-
如果这个xa事务prepare了，还没commit， 那么把它恢复到prepare的状态，由用户去决定commit或rollback
-
当mysql crash后重新启动之后，执行“XA RECOVER；”查看当前处于prepare状态的xa事务，然后commit或rollback它们。
1） XA事务和本地事务以及锁表操作是互斥的
-
开启了xa事务就无法使用本地事务和锁表操作
mysql> xa start 'xat1';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
mysql> lock table test.tb01 read;
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
mysql> xa end 'xat1';
Query OK, 0 rows affected (0.00 sec)
-
开启了本地事务就无法使用xa事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> xa start 'xat1';
ERROR 1400 (XAE09): XAER_OUTSIDE: Some work is done outside global transaction
2）xa start 之后必须xa end，否则不能执行xa commit和xa rollback。所以如果在执行xa事务过程中有语句出错了，你也需要先xa end一下，然后才能xarollback。
以MySQL数据库单节点XA事务为例：
mysql> xa start 'xa01';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test.tb01 values('user5','123');
Query OK, 1 row affected (0.00 sec)
mysql> xa end 'xa01';
Query OK, 0 rows affected (0.00 sec)
mysql> xa prepare 'xa01';
Query OK, 0 rows affected (0.00 sec)
mysql> xa recover;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
| 1 | 4 | 0 | xa01 |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)
mysql> xa commit 'xa01';
Query OK, 0 rows affected (0.01 sec)
MySQL事务中状态如下图所示：
-
首先使用XA START ‘XA01'启动了一个XA事务，并把它置于ACTIVE状态
-
对于一个ACTIVE状态的 XA事务，我们可以执行构成事务的多条SQL语句，也就是指定分支事务的边界，然后执行一个XA END ‘XA01'语句，XA END把事务放入IDLE状态，也就是结束事务边界，在xa start和xa end之间的语句就构成了本分支事务的一个事务范围。当调用xa end 'XA01'后由于结束了事务边界，所以这时候如何在执行sql语句会抛出ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state错误，也就是当分支事务处于IDLE状态时候不允许执行没有包含到分支事务边界里面的其他sql.
-
对于一个IDLE 状态XA事务，可以执行一个XA PREPARE语句或一个XA COMMIT…ONE PHASE语句,其中XA PREPARE把事务放入PREPARED状态。在此点上的XA RECOVER语句将在其输出中包括事务的xid值，因为XA RECOVER会列出处于PREPARED状态的所有XA事务。XA COMMIT…ONE PHASE用于预备和提交事务，也就是转换为一阶段协议，直接提交事务。
-
对于一个PREPARE状态的XA事务，可以执行XA COMMIT语句来提交或者执行XA ROLLBACK来回滚xa事务。
-
其中二阶段协议中第一阶段是执行xa prepare时候，这时候MySQL客户端（TM）向MySQL数据库服务器（RM）发出prepare"准备提交"请求，数据库收到请求后执行数据修改和日志记录等处理，处理完成后只是把事务的状态改成"可以提交",然后把结果返回给事务管理器。
-
如果第一阶段中数据库都prepare成功，那么mysql客户端（TM）向数据库服务器发出"commit"请求，数据库服务器把事务的"可以提交"状态改为"提交完成"状态，然后返回应答。如果在第一阶段内数据库的操作发生了错误，或者mysql客户端（RM）收不到数据库的回应，则认为事务失败，执行rollback回撤所有数据库的事务。
1）创建表acct_from和acct_to并插入数据
CREATE TABLE `acct_from` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`username` varchar(255) NOT NULL DEFAULT '',
`money` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `acct_to` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` varchar(255) NOT NULL DEFAULT '',
`money` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
mysql> insert into acct_from values(1,'aaa',1000);
Query OK, 1 row affected (0.08 sec)
mysql> insert into acct_from values(2,'bbb',2000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into acct_from values(3,'ccc',2000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into acct_to values(1,'aaa',100);
Query OK, 1 row affected (0.00 sec)
mysql> insert into acct_to values(2,'aaa',200);
Query OK, 1 row affected (0.00 sec)
mysql> insert into acct_to values(3,'ccc',300);
Query OK, 1 row affected (0.00 sec)
2）Python代码如下
import pymysql
# 打开数据库 （如果连接失败会报错）
db1 = pymysql.connect(host='192.168.112.101', port=3306, user='root', passwd='password', db='test01', charset="utf8")
db2 = pymysql.connect(host='192.168.112.101', port=3306, user='root', passwd='password', db='test01', charset="utf8")
# 获取游标对象
cursor1 = db1.cursor()
cursor2 = db2.cursor()
#事务分支1关联分支事务sql语句
cursor1.execute("XA START 'XA01'")
result1 = cursor1.execute("update acct_from set money=money - 50 where id=1")
cursor1.execute("XA END 'XA01'")
#事务分支2关联分支事务sql语句
cursor2.execute("XA START 'XA02'")
result2 = cursor2.execute("update acct_to set money= money + 50 where id=1")
cursor2.execute("XA END 'XA02'")
#两阶段提交协议第一阶段
ret1 = cursor1.execute("XA PREPARE 'XA01'")
ret2 = cursor2.execute("XA PREPARE 'XA02'")
#两阶段提交协议第二阶段
if ret1==0 and ret2==0:
cursor1.execute("XA COMMIT 'XA01'")
cursor2.execute("XA COMMIT 'XA02'")
print("result1:"+result1+";result2:"+result2)
else:
cursor1.execute("XA ROLLBACK 'XA01'")
cursor2.execute("XA ROLLBACK 'XA02'")
print("XA rollback!")
# 关闭连接
db1.close()
db2.close()
3）查看结果
mysql> select * from acct_from;
+----+----------+-------+
| id | username | money |
+----+----------+-------+
| 1 | aaa | 950 |
| 2 | bbb | 2000 |
| 3 | ccc | 2000 |
+----+----------+-------+
3 rows in set (0.00 sec)
mysql> select * from acct_to;
+----+----------+-------+
| id | username | money |
+----+----------+-------+
| 1 | aaa | 150 |
| 2 | aaa | 200 |
| 3 | ccc | 300 |
+----+----------+-------+
3 rows in set (0.00 sec)
3、MyCat中分布式事务实现
Mycat在1.6版本以后已经完全支持标准XA分布式事务类型了，mycat会作为事务协调者的角色。考虑到分布式事务的开销比较大，一般推荐在全局表的事务以及对一致性要求比较高的场景中使用。用户应用侧（AP）的使用流程如下：
1）set autocommit=0 —在应用层需要设置事务不能自动提交；
2）set xa=on —在SQL中设置XA为开启状态；
3）执行SQL—insert/update/select/delete
4）commit或者rollback
1）MyCAT中XA分布式事务的完整流程图如图所示：
-
set autocommit=0 将 MysqlConnection 中的 autocommit 设置为 false；
-
set xa=on：在Mycat中开启XA事务管理器
-
用XA START XID命令进行XA事务开始标记
-
执行SQL业务
-
运行XA END XID
-
XA PREPARE XID最后进行 1pc 提交并记录日志到 tm.log 中，如果 1pc 阶段有异常，则直接回滚事务XA ROLLBACK xid
-
在多节点MySQL中全部进行2pc提交（XA COMMIT），提交成功后，事务结束；如果有异常，则对事务进行重新提交或者回滚。
-
一阶段commit异常：如果1pc提交任意一个mysql节点无法提交或者异常，则全部节点的事务进行回滚，抛出异常给应用侧事务回滚
-
Mycat Crash Recovery：Mycat 崩溃以后，根据tm.log事务日志再进行重启恢复，mycat 启动后执行事务日志查找各个节点中已经prepared的XA事务，进行commit或者rollback
-
MySQLConnection：数据库连接。
-
NonBlockingSession：用户连接 Session。
-
MultiNodeCoordinator：协调者，多节点事务处理
-
CommitNodeHandler：分片提交处理
-
RollbackNodeHandler：分片回滚处理
4）Python实现Mycat分布式XA事务
import pymysql
# 打开数据库 （如果连接失败会报错）
db = pymysql.connect(host='192.168.112.10', port=8066, user='root', passwd='password', db='mycat', charset="utf8",autocommit=False)
# 获取游标对象
cursor = db.cursor()
# 开启XA事务
cursor.execute("set XA=ON")
try:
result1 = cursor.execute("select * from TB01")
except Exception:
result1 = -1
print(result1)
try:
result2 = cursor.execute("insert into TB01(id,city) values(15,'Foshan');")
except Exception:
result2 = -1
print(result2)
try:
result3 = cursor.execute("select * from TB01")
except Exception:
result3 = -1
print(result3)
#提交或回滚XA事务
if result1>=0 and result2>=0 and result3>=0:
db.commit()
print("XA commit!")
else:
db.rollback()
print("XA rollback!")
# 关闭连接
db.close()
-
协调日志写入性能：在每次写入文件时，是将内存中所有的日志全部重新写入，导致写入性能随着 XA 事务次数的增加，性能会越来越糟糕，导致 XA 事务整体性能会非常差
-
数据节点未全部PREPARE就进行COMMIT
-
MyCAT启动时回滚PREPARE的XA事务：MyCAT启动时，回滚所有处于PREPARE状态 的XA事务，可能某个XA事务部分 COMMIT部分PREPARE。此时直接回滚，会导致数据不一致。
-
单节点事务未记录协调日志：发起 XA PREPARE完后，MyCAT挂了。重启后，该XA事务在MyCAT里就“消失“了，参与者的该XA事务一直处于PREPARE状态。从理论上来说，需要回滚该 XA 事务
-
XA COMMIT部分节点挂了重新恢复后，未进一步处理：当一部分节点 XA COMMIT 完成，另外一部分此时挂了。在管理员重启挂掉的节点，其对应的XA事务未进一步处理，导致数据不一致。
参考资料：
-
mycat-definitive-guide
-
https://www.cnblogs.com/wt645631686/p/10882998.html
-
https://www.cnblogs.com/barrywxx/p/10435136.html
-
https://my.oschina.net/oosc/blog/1805729