How Do I Query Long Transactions That Have Been There for a Long Time Without Being Committed and Handle the Corresponding Sessions?

Long transactions can be intentionally designed for specific needs or may happen when you forget to commit transactions after initiating them with BEGIN or START TRANSACTION (transaction leakage). Uncommitted long transactions may cause lock conflicts and undo log accumulation. You can run the following statement to check long transactions:

SELECT * FROM information_schema.innodb_trx order by trx_started asc limit X;

This statement lists X records in ascending order of transaction start time.

trx_started in the output indicates the transaction start time, and trx_mysql_thread_id indicates the session ID. Determine whether to commit, roll back, keep, or kill the session where a long transaction is located based on the session ID in the processlist.