Jul
23
2018
--

Webinar Weds 7/25: XA Transactions

xa transactions distributed transactions

xa transactions distributed transactionsPlease 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).

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.

Register Now

Dov Endress

Dov Endress, Senior MySQL DBA in Managed Services

Dov joined Percona in the fall of 2015 as a senior support engineer. He learned BASIC in elementary school on an Apple IIE, and his first computer was a Commodore 64. Dov started working in the LAMP stack in 1999 and has been doing so ever since. He lives in northern Nevada with his wife, step-daughter, grandson, a clowder of cats and Macy – the best dog a person could meet. In his free time, he can be found somewhere outdoors or making things in the garage.

The post Webinar Weds 7/25: XA Transactions appeared first on Percona Database Performance Blog.

Sep
22
2017
--

How to Deal with XA Transactions Recovery

XA Transactions

XA TransactionsFor 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] Starting crash recovery...
2017-08-23T14:53:54.189204Z 0 [Note] InnoDB: Starting recovery for XA transactions...
2017-08-23T14:53:54.189225Z 0 [Note] InnoDB: Transaction 45093 in prepared state after recovery
2017-08-23T14:53:54.189244Z 0 [Note] InnoDB: Transaction contains changes to 2 rows
2017-08-23T14:53:54.189257Z 0 [Note] InnoDB: 1 transactions in prepared state after recovery
2017-08-23T14:53:54.189267Z 0 [Note] Found 1 prepared transaction(s) in InnoDB
2017-08-23T14:53:54.189312Z 0 [Warning] Found 1 prepared XA transactions
2017-08-23T14:53:54.189329Z 0 [Note] Crash recovery finished.
2017-08-23T14:53:54.189472Z 0 [Note] InnoDB: Starting recovery for XA transactions...
2017-08-23T14:53:54.189489Z 0 [Note] InnoDB: Transaction 45093 in prepared state after recovery
2017-08-23T14:53:54.189501Z 0 [Note] InnoDB: Transaction contains changes to 2 rows
2017-08-23T14:53:54.189520Z 0 [Note] InnoDB: 1 transactions in prepared state after recovery
2017-08-23T14:53:54.189529Z 0 [Note] Found 1 prepared transaction(s) in InnoDB
2017-08-23T14:53:54.189539Z 0 [Warning] Found 1 prepared XA transactions

The command

xa recover

 shows you an output like:

mysql> xa recover;
+----------+--------------+--------------+-----------+
| formatID | gtrid_length | bqual_length | data      |
+----------+--------------+--------------+-----------+
|     1234 |            4 |            5 |  bqual |
+----------+--------------+--------------+-----------+
1 row in set (0.00 sec)

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

 :

mysql> xa recover convert xid;
+----------+--------------+--------------+----------------------+
| formatID | gtrid_length | bqual_length | data                 |
+----------+--------------+--------------+----------------------+
|     1234 |            4 |            5 | 0x01020304627175616C |
+----------+--------------+--------------+----------------------+
1 row in set (0.01 sec)

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:

mysql> xa commit 0x01020304627175616C;
ERROR 1397 (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:

mysql> xa commit 0x01020304,0x627175616C,1234;
Query OK, 0 rows affected (0.15 sec)

Which finally works! On 5.6 the

convert xid

 option is not available. You have to be a bit more creative:

root@master57:/var/lib/mysql# mysql -r -e 'xa recoverG' | hexdump -C
00000000  2a 2a 2a 2a 2a 2a 2a 2a  2a 2a 2a 2a 2a 2a 2a 2a  |****************|
00000010  2a 2a 2a 2a 2a 2a 2a 2a  2a 2a 2a 20 31 2e 20 72  |*********** 1. r|
00000020  6f 77 20 2a 2a 2a 2a 2a  2a 2a 2a 2a 2a 2a 2a 2a  |ow *************|
00000030  2a 2a 2a 2a 2a 2a 2a 2a  2a 2a 2a 2a 2a 2a 0a 20  |**************. |
00000040  20 20 20 66 6f 72 6d 61  74 49 44 3a 20 31 32 33  |   formatID: 123|
00000050  34 0a 67 74 72 69 64 5f  6c 65 6e 67 74 68 3a 20  |4.gtrid_length: |
00000060  34 0a 62 71 75 61 6c 5f  6c 65 6e 67 74 68 3a 20  |4.bqual_length: |
00000070  35 0a 20 20 20 20 20 20  20 20 64 61 74 61 3a 20  |5.        data: |
00000080  01 02 03 04 62 71 75 61  6c 0a                    |....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!

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com