ApsaraDB RDS:What do I do if the transactions whose trx_mysql_thread_id value is 0 are not committed in ApsaraDB RDS for MySQL instances?

Problem description

The following error is reported in ApsaraDB RDS for MySQL:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The query result of the information_schema.innodb_trx table shows that a transaction with a trx_mysql_thread_id value of 0 is not committed.

Cause

A transaction with a trx_mysql_thread_id value of 0 is an XA transaction. The error occurs because the lock is held by an uncommitted XA transaction.

Solution

1. Execute the XA RECOVER; statement to query uncommitted XA transactions.

   

   Note

   If garbled text appears in the results after executing XA RECOVER;, you can execute XA RECOVER CONVERT XID;.

2. Roll back or commit the uncommitted XA transactions based on your requirements.

   • You can use the XA COMMIT syntax to commit an XA transaction.

     xa commit 'gtrid_length(data)','bqual_length(data)',formatID;

     Example:

     xa commit '123','_abc',100;

   • You can use the XA ROLLBACK syntax to roll back an XA transaction.

     xa rollback 'gtrid_length(data)','bqual_length(data)',formatID;

     Example:

     xa rollback '123','_abc',100;

For more information about the syntaxes for XA transactions, see MySQL official documentation.