Detailed explanation of mysql XA transaction recovery process

Mysql database startup Error:
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 schedents: 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)
This means that three XA transactions are not committed or rolled back.
Log on to mysql
Mysql> xa recover;
+ ---------- + -------------- + ------------------------------------------------------------ +
| FormatID | gtrid_length | bqual_length | data |
+ ---------- + -------------- + ------------------------------------------------------------ +
| 131075 | 30 | 28 | 1-7f000001: bae5: 4b6928eb: f06108f000001: 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 |
+ ---------- + -------------- + ------------------------------------------------------------ +
The data representation is as follows::
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
This is information about the three XA transactions and is ready to be rolled back directly.
Mysql> xa rollback '1-7f000001: bae5: 4b6928eb: fb5c3 ', '7f000001: bae5: 4b6928eb: fb5cd', 131075;
Query OK, 0 rows affected (0.41 sec)
It is normal to start again.

MySQL XA
I .5. XA transaction restrictions
XA transaction support is limited to the InnoDB Storage engine.
MySQL XA is implemented for external XA, where the MySQL server acts as the resource manager and the client program acts as the Transaction Manager. "Internal XA" is not implemented ". In this way, the separate storage engine in the MySQL server is allowed as RM (Resource Manager), and the server itself is used as TM (Transaction Manager ). Internal XA is required to process XA transactions that contain more than one storage engine. The internal XA implementation is incomplete because it requires the storage engine to support two-phase commit at the table processing program level. Currently, this feature is only implemented for InnoDB.
JOIN and RESUME clauses are not supported for xa start.

For xa end, the SUSPEND [for migrate] clause is not supported.
In a global transaction, for each XA transaction, the bqual part of the xid value should be different, which is a limit on the implementation of the current MySQL XA. It is not part of the XA specification.

If the XA transaction reaches the PREPARED state and the MySQL server is down, the transaction can be processed after the server is restarted. As it should have been. However, if the client connection is terminated and the server continues running, the server rolls back any unfinished XA transactions, even if the transaction has reached the PREPARED status. It should be able to commit or roll back prepared xa transactions, but this is not possible without changing the binary log mechanism.