Galera Cluster and XA Transactions

A few weeks ago, we received an interesting Galera Cluster support case from one of our customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup.

After some investigations, we found a lot of insert queries in state "query end" and lasting for long time without being completed. Also some other queries which were sleeping for long time having the info of "XA COMMIT":

SQL> SHOW PROCESSLIST;

27	user	host:33214	foodmart	Query	14440	sleeping	XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x

SQL> SHOW ENGINE INNODB STATUS;

TRANSACTIONS
============
---TRANSACTION 2DE71D, ACTIVE 14459 sec
9 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 115
MySQL thread id 27, OS thread handle 0x7fc21a42c700, query id 96187 host host-ip foodmart sleeping
XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x41544f4d ONE PHASE

XA means eXtended Architecture and "XA COMMIT" statement is one of the distributed transactions (XA Transactions) commands which are clearly NOT supported in Galera Cluster and one of its limitations because of possible rollback on commit.

The following command can be used to check if XA Transactions are being used by your application or not:

SQL> SHOW GLOBAL STATUS LIKE 'Com_xa%';
 
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Com_xa_commit     | 2828094 |
| Com_xa_end        | 2828221 |
| Com_xa_prepare    | 0       |
| Com_xa_recover    | 2205697 |
| Com_xa_rollback   | 42      |
| Com_xa_start      | 2828305 |
+-------------------+---------+
6 rows in set (0.00 sec)

There are only two possible solutions for this problem:

• Get rid of all XA transactions in the application to get the Galera Cluster work.
• Use another HA solution (Active/passive, Master/Slave, ... etc) but not Galera Cluster.

Conclusion

• XA transactions can not be supported in Galera Cluster and that is already stated clearly in the Galera Cluster Limitations.
• Before Moving to Galera Cluster, it is preferred to go through all its limitations and check whether your application can cope with them or not.