Do you really want to use XA with MySQL?

I have never been a fan of XA. One reason is because I have had to use and debug buggy implementations of it elsewhere. But the big reason is that it seems like one of those things that sounds great with a lot of hand-waving -- click here, here and here and presto -- distributed commit. And then you put it in production and failure happens.

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 3002907568
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 1, query id 9 mark 172.0.0.0 m

And 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.

---TRANSACTION 0 23167490, ACTIVE (PREPARED) 2 sec, process no 0, OS thread id 0
, undo log entries 1

XA RECOVER is your friend in these cases. It provides enough data so you can commit or rollback.

mysql> xa recover;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            3 |            0 | bar  |
+----------+--------------+--------------+------+

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.

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.