0
select * from INFORMATION_SCHEMA.INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 41844623
                 trx_state: RUNNING
               trx_started: 2016-11-23 15:40:42
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 71
       trx_mysql_thread_id: 0
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 6
          trx_lock_structs: 7
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 64
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

Is there any way to kill a MySQL transaction by it's trx_id? Or purge it somehow? There is no MySQL thread id or query id associated with this transaction and it persists after server restart.

user190084
  • 101
  • 2
  • 1
    `SELECT @@LOG_ERROR;` ...this should give you the path to the MySQL error log on your machine. In that file, I expect you'll find `InnoDB: Rolling back trx with id ...`. Confirm? Show what's in the error log? (Note, this is not a deadlock. I expect it's a runaway rollback.) Resist the urge to do anything to the server at the moment and let the WGU (whirring, grinding unit) do its thing. – Michael - sqlbot Nov 24 '16 at 01:07
  • Also, please don't [cross post](http://stackoverflow.com/q/40775782/1695906) (same essential question, different user). – Michael - sqlbot Nov 24 '16 at 01:12
  • What is a whirring, grinding unit? – user190084 Nov 24 '16 at 01:20
  • 1
    Apparently this is what a XA (distributed transaction) looks like. I was able to use XA RECOVER to to get the XID (under column data) and use XA RollBack XID. This is done at the MySQL command line. Logs helped :)Cross post deleted. The hardest part was figuring out what the XID was - as it is not the trx_id in the above output. – user190084 Nov 24 '16 at 01:52
  • Sorry, just a little humor there. The whirring grinding unit is a busy magnetic hard drive. Fake computer acronym WGU. – Michael - sqlbot Nov 24 '16 at 04:04
  • Any chance you could post an answer describing what you found and how you fixed it? – Michael - sqlbot Nov 24 '16 at 04:06

2 Answers2

0

This is a distributed transaction. It is waiting to commit or roll back.

http://dev.mysql.com/doc/refman/5.7/en/xa-statements.html

To view pending distributed transactions get to a MySQL command prompt and type:

XA RECOVER;

To roll back the transaction take the data found in the data column from the XA Recover output. So if the data column reads 'X12345':

XA ROLLBACK 'X12345'

user190084
  • 101
  • 2
0

Try this fix. Use:

mysql> xa recover convert xid;

then commit them using:

mysql> xa commit 0x01020304627175616C;
Andrew Schulman
  • 8,561
  • 21
  • 31
  • 47
Mohamd Ouda
  • 1