19 Aug 2024

XA transactions now work in your Galera Cluster!

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 that allows multiple resource managers (like databases) to participate in a single, coordinated transaction, ensuring atomicity across all involved systems. There is more over at Wikipedia and in the MySQL documentation.

We’ve covered setup before here: Galera Cluster Enterprise Edition (EE) with Red Hat Enterprise Linux (RHEL) 8. Use a simple configuration, ensure that in your /etc/my.cnf you also have the correct wsrep_provider, e.g. wsrep_provider=/usr/lib64/galera-enterprise-4/libgalera_smm.so. And then it is a simple mysqld_bootstrap away!

So, our example to show that this works, and your application can make use of it. First we setup Account which holds balances, and Audit, which logs transactions. Audit references Account to ensure that every transaction is associated with a valid account.

CREATE DATABSE xa;
USE xa;

CREATE TABLE Account (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

CREATE TABLE Audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT,
    transaction_amount DECIMAL(10, 2),
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES Account(account_id)
);

Then we initialise the Account table with 2 accounts, one with a balance of $1,000 and another with a balance of $2,000.

INSERT INTO Account (account_id, balance) VALUES (1, 1000.00), (2, 2000.00);

Then we perform the fund transfer by deducting $100 from Account A, and adding it to Account B. Transaction will be logged for audit, naturally.

XA START 'xatest';

-- Deduct amount from Account A
UPDATE Account SET balance = balance - 100.00 WHERE account_id = 1;

-- Add amount to Account B
UPDATE Account SET balance = balance + 100.00 WHERE account_id = 2;

-- Log the transaction in the Audit table
INSERT INTO Audit (account_id, transaction_amount) VALUES (1, -100.00), (2, 100.00);

XA END 'xatest';
XA PREPARE 'xatest';
XA COMMIT 'xatest';

Then we can verify the data integrity by a few simple SELECTs:

mysql> SELECT * FROM Account;
+------------+---------+
| account_id | balance |
+------------+---------+
|          1 |  900.00 |
|          2 | 2100.00 |
+------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM Audit;
+----------+------------+--------------------+---------------------+
| audit_id | account_id | transaction_amount | timestamp           |
+----------+------------+--------------------+---------------------+
|        1 |          1 |            -100.00 | 2024-08-18 11:10:02 |
|        2 |          2 |             100.00 | 2024-08-18 11:10:02 |
+----------+------------+--------------------+---------------------+
2 rows in set (0.00 sec)

We can also try to do one to have a rollback example, as stated here:

mysql> XA START 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE Account SET balance = balance - 100.00 WHERE account_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Account SET balance = balance + 100.00 WHERE account_id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> INSERT INTO Audit (account_id, transaction_amount, invalid_column) VALUES (1, -100.00, 100.00);
ERROR 1054 (42S22): Unknown column 'invalid_column' in 'field list'
mysql> XA END 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> XA PREPARE 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> XA ROLLBACK 'xatest';
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> SELECT * FROM Account;
+------------+---------+
| account_id | balance |
+------------+---------+
|          1 |  900.00 |
|          2 | 2100.00 |
+------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM Audit;
+----------+------------+--------------------+---------------------+
| audit_id | account_id | transaction_amount | timestamp           |
+----------+------------+--------------------+---------------------+
|        1 |          1 |            -100.00 | 2024-08-18 11:10:02 |
|        2 |          2 |             100.00 | 2024-08-18 11:10:02 |
+----------+------------+--------------------+---------------------+
2 rows in set (0.00 sec)

This rollback example was a forced error to reference an invalid_column in the Audit table. This error will cause the transaction to fail, triggering a rollback when you try to prepare or commit the XA transaction. This ensures that none of the operations are committed, preserving the consistency of your data.

Want to know if you application uses XA transactions? You can by executing the following:

mysql> SHOW GLOBAL STATUS LIKE 'Com_xa%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_xa_commit   | 1     |
| Com_xa_end      | 2     |
| Com_xa_prepare  | 3     |
| Com_xa_recover  | 0     |
| Com_xa_rollback | 1     |
| Com_xa_start    | 2     |
+-----------------+-------+
6 rows in set (0.00 sec)

If you see that, and you are planning to migrate to Galera Cluster, remember to use Galera Cluster Enterprise Edition (EE) (or change your application to not use it). But we are truly happy that XA transactions work inside of Galera Cluster Enterprise Edition (EE)!

If you want to evaluate XA transactions in your Galera Cluster, and try out all the offerings of Galera Cluster Enterprise Edition (EE), please contact your friendly sales representative via email at sales@galeracluster.com or via the contact form.