Given the restrictions on XA in MySQL, there is even less reason to use it. If a server crashes and there are transactions in the PREPARED state, the transactions can be commited after the server recovers. But those transactions will not be written into the binlog. What good is doing all of this work to keep separate primaries in sync if the primaries and slaves will not have the same transactions?
What happens when an app forgets to commit or rollback a PREPARED transaction? Nothing good. This is what a PREPARED transaction normally looks like in SHOW INNODB STATUS.
---TRANSACTION 0 23167490, ACTIVE (PREPARED) 39 sec, process no 13492, OS thread id 3002907568And this is what it looks like after a server has crashed and restarted. There is not much here and no way to map it back to the database account that created it. At this point you need to use XA RECOVER and hope the app that created it used a good transaction ID.
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 1, query id 9 mark 184.108.40.206 m
---TRANSACTION 0 23167490, ACTIVE (PREPARED) 2 sec, process no 0, OS thread id 0XA RECOVER is your friend in these cases. It provides enough data so you can commit or rollback.
, undo log entries 1
mysql> xa recover;Today I looked at a log from a crash. The cause was a 4 day old XA transaction that someone forgot to commit. Ouch. I have a solution that is better than monitoring for stale transactions. Disable the use of XA commands in the MySQL parser and eliminate the problem. There is a variable, innodb_support_xa, that can be set to disable the support for XA by InnoDB, but this only changes things internally. The XA commands will still be parsed. And applications will not know that XA is not supported.
| formatID | gtrid_length | bqual_length | data |
| 1 | 3 | 0 | bar |
The MySQL implementation has other interesting behavior. It will rollback a prepared transaction when the session that prepared the transaction disconnects and when the server does a clean shutdown. When the server crashes, prepared transactions do not get a rollback during recovery. Given that the MySQL server acts as a resource manager and not as a transaction manager, this seems odd. If a client starts an XA transaction on several servers, prepares all of them and then loses a connection because of a flaky network, it will be suprised that it cannot commit on all servers. And there is no way to prevent this.