Credit for this gist goes to Alexander Rubin at https://www.percona.com/blog/author/alexanderrubin/
Article used for this was at: https://www.percona.com/blog/2017/05/08/chasing-a-hung-transaction-in-mysql-innodb-history-length-strikes-back/
To identify hung transactions:
show engine innodb status
Also:
select * from information_schema.innodb_trx
show processlist
It shows:
---TRANSACTION 41271309586, ACTIVE 766132 sec
2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
...
but there's no indication as to what the actual SQL is that is keeping the transaction active.
Solution:
- Enable performance_schema if not enabled (it is disabled on RDS / Aurora by default).
- Enable events_statements_history thusly:
update performance_schema.setup_consumers set ENABLED = 'YES' where NAME='events_statements_history';
-
Run this query to find all transaction started 10 seconds ago (change the number of seconds to match your workload):
SELECT ps.id as processlist_id, trx_started, trx_isolation_level, esh.EVENT_ID, esh.TIMER_WAIT, esh.event_name as EVENT_NAME, esh.sql_text as SQL_TEXT, esh.RETURNED_SQLSTATE, esh.MYSQL_ERRNO, esh.MESSAGE_TEXT, esh.ERRORS, esh.WARNINGS FROM information_schema.innodb_trx trx JOIN information_schema.processlist ps ON trx.trx_mysql_thread_id = ps.id LEFT JOIN performance_schema.threads th ON th.processlist_id = trx.trx_mysql_thread_id LEFT JOIN performance_schema.events_statements_history esh ON esh.thread_id = th.thread_id WHERE trx.trx_started < CURRENT_TIME - INTERVAL 10 SECOND AND ps.USER != 'SYSTEM_USER' ORDER BY esh.EVENT_ID```