Please join Percona Senior MySQL DBA for Managed Services, Dov Endress, as he presents XA Transactions on Wednesday, July 25th, 2018 at 12:00 PM PDT (UTC-7) / 3:00 PM EDT (UTC-4).

Register Now

Distributed transactions (XA) are becoming more and more vital as applications evolve. In this webinar, we will learn what distributed transactions are and how MySQL implements the XA specification. We will learn the investigatory and debugging techniques necessary to ensure high availability and data consistency across disparate environments.

This webinar is not intended to be an in-depth look at transaction managers, but focuses on resource managers only. It is primarily intended for database administrators and site reliability engineers.

…

For most people (including me until recently) database XA transactions are a fuzzy concept. In over eight years with Percona, I have never had to deal with XA transactions. Then a few weeks ago I got two customers having issues with XA transactions. That deserves a post.

XA 101

What are XA transactions? XA transactions are useful when you need to coordinate a transaction between different systems. The simplest example could be simply two storage engines within MySQL. Basically, it follows this sequence:

1. XA START
2. Some SQL statements
3. XA END
4. XA PREPARE
5. XA COMMIT or ROLLBACK

Once prepared, the XA transaction survives a MySQL crash. Upon restart, you’ll see something like this in the MySQL error log:

2017-08-23T14:53:54.189068Z 0 [Note] …

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 …