I have problem with MySQL transaction i cant delete it, becuase it doesnt have transaction threadId

Question

Hello everyone so i have been trying to delete transaction which is locking some of my tables, tried to restart the database, but nothing changed (we are using 8.0.35 version MySQL) tried dropping modifying the table but nothing, everything is still the same this transaction is stuck its in running state but nothing is committing or anything tried the XA command but XA cant identify the trx_id says that this ID doesn't exist. Here is the Engine Status :

---TRANSACTION 183328, ACTIVE (PREPARED) 56423 sec recovered trx 4 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 4

well I can post the whole picture so here are the values :

# trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_weight, trx_mysql_thread_id, trx_query, trx_operation_state, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_lock_memory_bytes, trx_rows_locked, trx_rows_modified, trx_concurrency_tickets, trx_isolation_level, trx_unique_checks, trx_foreign_key_checks, trx_last_foreign_key_error, trx_adaptive_hash_latched, trx_adaptive_hash_timeout, trx_is_read_only, trx_autocommit_non_locking, trx_schedule_weight

'183328', 'RUNNING', '2024-08-08 20:30:14', NULL, NULL, '8', '0', NULL, NULL, '0', '2', '4', '1128', '4', '4', '0', 'REPEATABLE READ', '1', '1', NULL, '0', '0', '0', '0', NULL

Also found a blog where he has the same situation:

https://www.mortensi.com/2021/04/show-engine-innodb-status-and-recovered-xa-transactions/

the situation is identical just the problem is that my transaction is locking 2 tables.

Answer 1

I had a similar issue to you.

I was able to solve this issue by using the steps in this AWS Knowledge base article combined with Maria DB XA Transaction specifics. Here are the steps I did:

1. To identify the XA transactions that are in the PREPARED status, run the SHOW ENGINE INNODB STATUS command.

   Example output:

   ---TRANSACTION [2043536230](tel:2043536230), ACTIVE (PREPARED) 302510 sec recovered trx  
    9 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1  
    ---TRANSACTION [2043368758](tel:2043368758), ACTIVE (PREPARED) 303628 sec recovered trx  
    5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2`
   

   Note: XA transactions don't appear in SHOW FULL PROCESSLIST.

2. Run the XA RECOVER command in Command Line.

   Example output:

   mariadb> xa recover;  
        formatID  gtrid_length  bqual_length  data                                   
                1            35             1  3d36dccd-61d0-4ae1-9b39-f9ccc2400d44:44       
                1            43             2  11740f2a-8a85-4b33-b924-982c1539d197:2222 `
   

   Note: Each XA transaction begins with a XA keyword followed by the XA transaction identifier (XID). An XID has three parts:

   • The gtrid is a global transaction identifier.
   • The bqual is a branch qualifier. This value must be different for each XA transaction within a global transaction.
   • The formatID is a number that identifies the format that the gtrid and bqual values use.

   For more information on gtrid, bqual, and formatID, see XA transaction SQL statements on the MySQL website. For MariaDB Here

3. Run the XA RECOVER FORMAT='SQL' command to get the data in a human readable form that can be directly copy-pasted into XA COMMIT or XA ROLLBACK

   Example output:

    mariadb> xa recover format='SQL';  
    formatID  gtrid_length  bqual_length  data                                       
            3             11             2  X'31320d3334093637763738',X'6162630a646566',3    
    2 rows in set (0.00 sec)
   

4. Run the XA ROLLBACK or XA COMMIT command to roll back or commit the XA transactions. Use the preceding method to break down every XA transaction that requires a rollback. Which looks like this:

    mariadb> xa rollback X'31320d3334093637763738',X'6162630a646566',3 ;