Troubleshooting XA transactions in MySQL

This is one of the exciting troubleshooting related to XA transactions let’s dive in. Let me provide a few backgrounds.

For one of our clients, when we try to drop a table, it is waiting for metadata lock. On debugging it is one of the XA transactions is holding the shared write lock and causing metadata lock on all the other local transactions.

Let us view the metadata locks from the Performance Schema.

mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,LOCK_STATUS,SOURCE from performance_schema.metadata_locks\G ******************* 1. row ********************* OBJECT_TYPE: TABLE OBJECT_SCHEMA: sbtest OBJECT_NAME: sbtest_table1 LOCK_TYPE: SHARED_WRITE LOCK_STATUS: GRANTED SOURCE: xa.cc:284 ******************* 2. row ********************* OBJECT_TYPE: TABLE OBJECT_SCHEMA: sbtest OBJECT_NAME: sbtest_table2 LOCK_TYPE: SHARED_WRITE LOCK_STATUS: GRANTED SOURCE: xa.cc:284 ******************* 3. row ********************* OBJECT_TYPE: TABLE OBJECT_SCHEMA: sbtest OBJECT_NAME: sbtest_table3 LOCK_TYPE: SHARED_WRITE LOCK_STATUS: GRANTED SOURCE: xa.cc:284 ******************* 4. row ********************* OBJECT_TYPE: TABLE OBJECT_SCHEMA: sbtest OBJECT_NAME: sbtest_table1 LOCK_TYPE: EXCLUSIVE LOCK_STATUS: PENDING SOURCE: xa.cc:284

What’s really happening?

In MySQL 8.0.28 and earlier, XA transactions and local (non-XA) transactions are mutually exclusive. ( XA Changes in 8.0.29 ) For example, if XA START has been issued to begin an XA transaction, a local transaction cannot be started until the XA transaction has been committed or rolled back. In our case,  the XA transaction has already in a PREPARED state(neither committed nor rolled back), holding the lock. Hence, the local transaction which we are trying to initiate is not able to acquire the lock as they are mutually exclusive.

XA transactions will enter into a PREPARED state when the transactions are either not committed (XA COMMIT;) or not rolled back (XA ROLLBACK);

XA Transaction Flow

Flow chart of XA transaction

We could see from the Show Engine InnoDB Status Output, that the transaction has reached the Prepared state,\.

---TRANSACTION 28992918, ACTIVE (PREPARED) 1072517 sec recovered trx5 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 8---TRANSACTION 234688, ACTIVE (PREPARED) 1072517 sec recovered trx3 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 6---TRANSACTION 25949, ACTIVE (PREPARED) 1072517 sec recovered trx1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1

We can get the list of all XA Prepared transactions by running XA RECOVER; To commit or rollback these XA transactions we need to know the XID of each XA transaction

mysql> XA RECOVER;
+----------+--------------+--------------+----------------------------------------------+
| formatID | gtrid_length | bqual_length | data                                         |                                       
+----------+--------------+--------------+----------------------------------------------+
|        1 |           38 |            1 | 3d36dccd-61d0-4ae1-9b39-f9ccc2400d44:44      |
|        1 |           39 |            2 | 11740f2a-8a85-4b33-b924-982c1539d197:2222    |
|        1 |           41 |            4 |0d0afb17-c480-4a30-88c0-907b20794d13:10011001 |
+----------+--------------+--------------+----------------------------------------------+

How did we fix this issue?

As we already know, each XA transaction begins with a XA keyword followed by the XID. An XID is an XA transaction identifier. An XID value has from one to three parts:

• Gtrid is a global transaction identifier.
• Bqual is a branch qualifier.
• FormatID is a number that identifies the format used by the gtrid and bqual values.
• The bqual part of the XID value must be different for each XA transaction within a global transaction.

mysql> xa recover convert xid;
+----------+--------------+--------------+-------------------------------------+
| formatID | gtrid_length | bqual_length | data                                |                                                        
+----------+--------------+--------------+-------------------------------------+
|        1 |           6 |            2 | 0x1A64306166623137                   |
|        1 |           7 |            4 | 0x31313734306632612D3861             |           +----------+--------------+--------------+-------------------------------------+
3 rows in set (0.00 sec)

The above “XA RECOVER CONVERT XID” gives the XID values in the hexadecimal values. The gtrid_length and bqual_length fields tell us the position to start and stop.  We need to take the gtrid_length in bytes, and bqual_length in bytes and separate out those values.

Let’s take the data column value from the above output. Let’s split the value in bytes based on gtrid_length (given as 6) and bqual_length (shown as 2) values.

As ‘1A’ is a single byte, keeping this in mind, we need to split the data value.

0x 1A6430616662 3137 Gtrid_length - 6     == > (1A 64 30 61 66 62) Bqual_length - 2   == > (31 37)

Let’s try committing the transaction now

mysql>XA COMMIT X’1A6430616662’,X’3137’,1; Query OK, 0 rows affected (0.03 sec)

where 1 is the format identifier

Let’s take the 2nd transaction now. Based on the gtrid_length and bqual_length values given we are splitting the data value.

0x 31313734306632 612D3861  Gtrid_length - 7    == > (31 31 37 34 30 66 32) Bqual_length - 4  == > (61 2D 38 61)

mysql>XA COMMIT X’31313734306632’,X’612D3861’,1 Query OK, 0 rows affected (0.03 sec)

Here,

“1” is the format identifier

Key takeaways

In this way, from the XA RECOVER CONVERT XID output, we would be able to get the XID of any XA transaction which is in a prepared state and we can commit/rollback it based on our requirement.

Happy troubleshooting !!!

Advertisement