How to Deal with XA Transactions Recovery

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:

XA START

Some SQL statements

XA END

XA PREPARE

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:

There are some binary data that can’t be shown in HTML. The XA Xid is made of three fields: gtrid (global trx id), bqual (branch qualifier) and formatId. Java applications use all three fields. For my example above, I used “X’01020304′,’bqual’,1234”. You can trust Java application servers to be creative with Xid values. With MySQL 5.7, you can output the data part in hex with
convert xid :

Shell

1

2

3

4

5

6

7

mysql>xa recover convert xid;

+----------+--------------+--------------+----------------------+

|formatID|gtrid_length|bqual_length|data|

+----------+--------------+--------------+----------------------+

|1234|4|5|0x01020304627175616C|

+----------+--------------+--------------+----------------------+

1row inset(0.01sec)

The Problem

If you do nothing, the prepared transaction stays there forever and holds locks and a read view open. As a consequence, the history list grows without bound along with your ibdata1 file, where the undo entries are kept. If you have slaves, they all have the prepared transaction too (at least with 5.7). No fun.

As a consequence, if you are using XA transactions, you MUST check if there are prepared transactions pending after the server or mysqld restarted. If you find such transactions, you need to commit or roll them back, depending on what is involved.

But how do you commit these XA transactions? The problem here is the output of
xa recover. As it is, the output is unusable if there is a bqual field or non-default formatID field:

Shell

1

2

mysql>xa commit0x01020304627175616C;

ERROR1397(XAE04):XAER_NOTA:Unknown XID

The Fix

Looking back at the
xa recover convert xid output above, the gtrid_length and bqual_length are provided. With the use of these values, you can extract the parts of the data field which gives us:

gtrid = 0x01020304

bqual = 0x627175616C

And, of course, the formatID is 1234. Altogether, we have:

Shell

1

2

mysql>xa commit0x01020304,0x627175616C,1234;

Query OK,0rows affected(0.15sec)

Which finally works! On 5.6 the
convert xid option is not available. You have to be a bit more creative:

Shell

1

2

3

4

5

6

7

8

9

10

11

root@master57:/var/lib/mysql# mysql -r -e 'xa recoverG' | hexdump -C

000000002a2a2a2a2a2a2a2a2a2a2a2a2a2a2a2a|****************|

000000102a2a2a2a2a2a2a2a2a2a2a20312e2072|***********1.r|

000000206f77202a2a2a2a2a2a2a2a2a2a2a2a2a|ow*************|

000000302a2a2a2a2a2a2a2a2a2a2a2a2a2a0a20|**************.|

00000040202020666f726d617449443a20313233|formatID:123|

00000050340a67747269645f6c656e6774683a20|4.gtrid_length:|

00000060340a627175616c5f6c656e6774683a20|4.bqual_length:|

00000070350a2020202020202020646174613a20|5.data:|

0000008001020304627175616c0a|....bqual.|

0000008a

But there is a limitation in 5.6: you can only XA commit/rollback transactions that belong to your session. That means after a crash you are out of luck. To get rid of these you need to promote a slave or perform a logical dump and restore. The best plan is to avoid the use of XA transactions with 5.6.

I submitted this bug to Percona Server for MySQL in order to get a usable output out of
xa recover convert xid. If you think this is important, vote for it!

Related

Author

Yves is a Principal Architect at Percona, specializing in distributed technologies such as MySQL Cluster, Pacemaker and XtraDB cluster. He was previously a senior consultant for MySQL and Sun Microsystems. He holds a Ph.D. in Experimental Physics.