Identifying hung transactions in MySQL

gistfile1.md

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:

1. Enable performance_schema if not enabled (it is disabled on RDS / Aurora by default).
2. Enable events_statements_history thusly:

update performance_schema.setup_consumers set ENABLED = 'YES' where NAME='events_statements_history';

3. 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```