Posted in MySQL

SHOW ENGINE INNODB STATUS and recovered XA transactions

While testing a XA transactions based workload, I noticed in the output of SHOW ENGINE INNODB STATUS;

---TRANSACTION 108674, ACTIVE (PREPARED) 35 sec
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

So far, so good. A XA transaction has been PREPARED, and reported as such. But after a while (and I was not really looking at the session), I repeated the command and the output turned to:

---TRANSACTION 108674, ACTIVE (PREPARED) 91 sec recovered trx
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

I went to check logs, because XA transactions are recovered after MySQL Server restart, and I made sure no crash or restart was logged. What had happened?

Simple explanation (thanks to the great Kuba Łopuszański who put me immediately on the right track). The session had disconnected, and MySQL reports a distributed transaction as recovered when the client disconnects (so it’s not really recovered as consequence of crash recovery, but in reality it was just disconnected). Wrapping up, a transaction is reported as recovered if:

  • The session disconnects
  • The instance is restarted (after shutdown or crash)
  • The transaction has been replicated, so the replica reports it as recovered as well

SHOW ENGINE INNODB STATUS provides a lot of information at first sight, but I really missed the detail.

Edit. An XA transaction which was XA PREPARED will no longer be shown as “recovered” in case a connection was disconnected, this will be changed starting from MySQL Server 8.0.26. Such a disconnected and PREPARED transaction will still be reported in the list output by XA RECOVER and will still be visible in information_schema.innodb_trx as a row with trx_mysql_thread_id=0.

select * from information_schema.innodb_trx \G;
*************************** 1. row ***************************
                    trx_id: 358468
                 trx_state: RUNNING
               trx_started: 2021-05-21 10:39:31
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 0
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0

Related posts:

Default ThumbnailMySQL Server Performance Tuning: The Perfect Scalability InnoDB Cluster bulk importHow To Bulk Import Data Into InnoDB Cluster? Configure an MDS DB SystemConfigure a MySQL Database Service (MDS) DB System MySQL in DockerMySQL in a Docker Container cheat sheet

admin

2 thoughts on “SHOW ENGINE INNODB STATUS and recovered XA transactions”

  1. Beibei Hu

    Hi Admin (sorry don’t know how to call you properly):

    “While testing a XA transactions based workload, I noticed in the output of SHOW ENGINE INNODB STATUS;
    —TRANSACTION 108674, ACTIVE (PREPARED) 35 sec
    4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2”

    Could you please share me how you tested the XA transactions to get this InnoDB status? I didn’t see this ACTIVE (PREPARED) state after preparing my XA transactions.

    Thanks.

    Reply

    1. admin

      Something as simple as:

      mysql> XA start "mytx";
      Query OK, 0 rows affected (0.00 sec)
      mysql> insert into t(a) values (55);
      Query OK, 1 row affected (0.05 sec)
      mysql> XA end "mytx";
      Query OK, 0 rows affected (0.00 sec)
      mysql> XA prepare "mytx";
      Query OK, 0 rows affected (0.01 sec)

      That will produce:

      ---TRANSACTION 8970, ACTIVE (PREPARED) 20 sec
      1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
      MySQL thread id 11, OS thread handle 123145538027520, query id 50 localhost root starting
      show engine innodb status

      Reply

Leave A Comment