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.

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

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

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. Let’s try committing the transaction now, 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.

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.

Unleash Peak MySQL Performance! Mydbops offers expert MySQL database management services to streamline your XA transactions and prevent lock issues. We provide comprehensive solutions and support to empower your MySQL deployments. Contact us today!

{{cta}}