mysql的XA事务恢复过程详解

mysql数据库开机报错：
innodb: the log sequence number in ibdata files does not match
innodb: the log sequence number in the ib_logfiles!
100224 12:24:20 innodb: database was not shut down normally!
innodb: starting crash recovery.
innodb: reading tablespace information from the .ibd files...
innodb: restoring possible half-written data pages from the doublewrite
innodb: buffer...
innodb: transaction 0 4497755 was in the xa prepared state.
innodb: transaction 0 4468551 was in the xa prepared state.
innodb: transaction 0 4468140 was in the xa prepared state.
innodb: 3 transaction(s) which must be rolled back or cleaned up
innodb: in total 0 row operations to undo
innodb: trx id counter is 0 5312768
innodb: starting in background the rollback of uncommitted transactions
100224 12:24:20 innodb: rollback of non-prepared transactions completed
100224 12:24:20 innodb: started; log sequence number 0 3805002509
100224 12:24:20 innodb: starting recovery for xa transactions...
100224 12:24:20 innodb: transaction 0 4497755 in prepared state after recovery
100224 12:24:20 innodb: transaction contains changes to 8 rows
100224 12:24:20 innodb: transaction 0 4468551 in prepared state after recovery
100224 12:24:20 innodb: transaction contains changes to 1 rows
100224 12:24:20 innodb: transaction 0 4468140 in prepared state after recovery
100224 12:24:20 innodb: transaction contains changes to 1 rows
100224 12:24:20 innodb: 3 transactions in prepared state after recovery
100224 12:24:20 [note] found 3 prepared transaction(s) in innodb
100224 12:24:20 [warning] found 3 prepared xa transactions
100224 12:24:20 [note] event scheduler: loaded 0 events
100224 12:24:20 [note] /opt/mysql/bin/mysqld: ready for connections.
version: '5.1.39' socket: '/tmp/mysql.sock' port: 3306 mysql community server (gpl)
意味着有三个xa的事务没有提交或回滚。
登录到mysql
mysql> xa recover;
+----------+--------------+--------------+------------------------------------------------------------+
| formatid | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------------------------------------------------------------+
| 131075 | 30 | 28 | 1-7f000001:bae5:4b6928eb:f06397f000001:bae5:4b6928eb:f0650 |
| 131075 | 30 | 28 | 1-7f000001:bae5:4b6928eb:fb5c37f000001:bae5:4b6928eb:fb5cd |
| 131075 | 30 | 28 | 1-7f000001:bae5:4b6928eb:f03ea7f000001:bae5:4b6928eb:f0400 |
+----------+--------------+--------------+------------------------------------------------------------+
数据表示信息如下：
formatidis the formatidpart of the transaction xid
gtrid_lengthis the length in bytes of the gtridpart of the xid
bqual_lengthis the length in bytes of the bqualpart of the xid
datais the concatenation of the gtridand bqualparts of the xid
这是三个xa事务的信息，准备直接回滚。
mysql> xa rollback '1-7f000001:bae5:4b6928eb:fb5c3','7f000001:bae5:4b6928eb:fb5cd',131075;
query ok, 0 rows affected (0.41 sec)
再启动就正常了。

mysql xa
i.5. 对xa事务的限制
xa事务支持限于innodb存储引擎。
mysql xa实施是针对外部xa的，其中，mysql服务器作为资源管理器，而客户端程序作为事务管理器。未实施“内部xa”。这样，就允许mysql服务器内的单独存储引擎作为rm（资源管理器），而服务器本身作为tm（事务管理器）。处理包含1个以上存储引擎的xa事务时，需要内部xa。内部xa的实施是不完整的，这是因为，它要求存储引擎在表处理程序层面上支持两阶段提交，目前仅对innodb实现了该特性。
对于xa start，不支持join和resume子句。

对于xa end，不支持suspend [for migrate]子句。
在全局事务内，对于每个xa事务，xid值的bqual部分应是不同的，该要求是对当前mysql xa实施的限制。它不是xa规范的组成部分。

如果xa事务达到prepared状态而且mysql服务器宕机，当服务器重启后，能够继续处理事务。就像原本应当的那样。但是，如果客户端连接中止而服务器继续运行，服务器将回滚任何未完成的xa事务，即使该事务已达到prepared状态也同样。它应能提交或回滚prepared xa事务，但在不更改二进制日志机制的情况下不能这样。