Well, that is more a catchy headline, than the reality: XA transactions work in your Galera Cluster Enterprise Edition (EE). This has been supported since our very first release with 8.0.28 Enterprise, right up till now, in 8.0.39 Enterprise. If you try to run an XA transaction in your regular Galera Cluster you’ll be given an error as such:

mysql> XA START 'xatest';
ERROR 1235 (42000): This version of MySQL doesn't yet support 'XA with wsrep replication plugin'

However, with Galera Cluster Enterprise Edition (EE), XA transactions, “just work”. What is an XA transaction, you might ask? An XA transaction is a distributed transaction …

Introduction

XA stands for “eXtended Architecture”, and is a standard created by The Open Group for distributed transaction processing. While MySQL 5.0 was the first version to support XA, MySQL 5.7 has removed major limitations,  fixed a number of bugs, and increased overall test-case coverage.…

Today I was doing some tests with XA transactions in MySQL 5.6.

The output of the XA RECOVER command to list transactions was hard to read because of the representation of the data column:

The good news is that 5.7 has transaction information in performance_schema:

mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode 
    -> from performance_schema.events_transactions_current;
+-----------------+-----------------+--------+--------+----------+-------------+
| trx_id          | isolation_level | state  | xid    | xa_state | access_mode |
+-----------------+-----------------+--------+--------+----------+-------------+
|            NULL | REPEATABLE READ | ACTIVE | x-1    | PREPARED | READ WRITE  |
| 421476507015704 | REPEATABLE READ | …

The recently released TokuDB brings many features. One of those features is support for XA Transactions. InnoDB already has support for XA Transactions.

XA Transactions are transactions which span multiple databases and or applications. XA Transactions use 2-phase commit, which is also the same method which MySQL Cluster uses.

Internal XA Transactions are used to keep the binary log and InnoDB in sync.

Demo 1: XA Transaction on 1 node:

mysql55-tokudb6> XA START 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('demo01');
Query OK, 1 row affected (0.01 sec)

mysql55-tokudb6> SELECT * FROM xatest;
+----+--------+
| id | name   |
+----+--------+
|  3 | demo01 |
+----+--------+
1 row in set (0.00 sec)

mysql55-tokudb6> XA END 'demo01';
Query OK, 0 rows affected (0.00 sec) …

With Paul McCullagh’s PBXT storage engine getting integrated into MariaDB 5.1, it’s never been easier to it out. So we have, on a slave off one of our own production systems which gets lots of inserts from our Zabbix monitoring system.

That’s possibly an ideal usage profile, since PBXT is a log based engine (simplistically stated, it indexes its transaction logs, rather than rewriting data from log into index and indexing that) so it should require less disk I/O than say InnoDB. And that means it should be particularly suited to for instance logging, which have lots of inserts on a sustained basis. Note that for short insert burst you may not see a difference with InnoDB because of caching, but sustain it and then you can notice.

Because PBXT has such different/distinct architecture there’s a lot of learning involved. Together …

I have just released PBXT 1.0.09 RC3. Besides bug fixes (details in the release notes), this version includes 2 Beta features:

• XA/2-Phase Commit support
• Native online backup Driver

XA support has been around MySQL for quite a while, and we all know of it usefulness, for example when sharding. So I was surprised to find a bug in the XA recovery: Bug #47134. Contrary to what is reported, the crash can also occur when using XA with just the default engines installed, so watch out for that one (the good news: the bug fix is simple).

Online backup is really cool! I have heard that it may soon be released in a coming version of 5.4, so lets hope that this is true.

In a little test, I did a backup of a 10GB database in 49.26 seconds! …

Recently a customer mentioned that they were seeing corruption rarely when they copied InnoDB files using LVM to setup a new slave. Investigating further, it turns out that replication would start, but would then hit a lock wait timeout. This locking issue occurred across restarts causing replication to always fail. They would solve this by taking a new LVM snapshot and resetting it up.

This is a classic case of an XA transaction in a prepared state persisting across restarts and the LVM sometimes taking a snapshot at the exact instant when this can occur.

Most people don't use actually XA transactions, so they may not consider this possibility. However, it can still occur even if you do not use them due to XA being used internally for the binary log and InnoDB. We will be able to identify that case later.

You can tell this …