I'm currently having an issue with some long running transactions that are holding locks to a single row in a table that I'm being unable to kill.
Innodb_trx holds the following information - special attention to thread_id = null;
select * from information_schema.innodb_trx\G
*************************** 2. row ***************************
trx_id: 153261728
trx_state: RUNNING
trx_started: 2019-10-02 10:05:42
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 0
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 2
trx_lock_structs: 3
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
and
show engine innodb status\G;
------------------
---TRANSACTION 153261728, ACTIVE (PREPARED) 27716 sec recovered trx
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
---TRANSACTION 96697370, ACTIVE (PREPARED) 988082 sec recovered trx
0 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
Is there as way to kill/stop/rollback a transaction like this?