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 commiting or anything tried the XA command but XA cant identify the trx_id says that this ID doesnt exsists. 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:
the situation is identical just the problem is that my transaction is locking 2 tables.
[1]: https://i.sstatic.net/BHcGe2lz.png