Recently, somebody asked me how he can find the long running transactions in MySQL.
I already have one MySQL Shell plugin that allows you to find the current transactions sorted by time. The plugin allows you to also get the details about the desired transaction. See
Let’s see how we can easily find those long transaction that can be a nightmare for the DBAs (see MySQL History List Length post).
SELECT thr.processlist_id AS mysql_thread_id, concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User, Command, FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration, current_statement as `latest_statement` FROM performance_schema.events_transactions_current trx INNER JOIN performance_schema.threads thr USING (thread_id) LEFT JOIN sys.processlist p ON p.thd_id=thread_id WHERE thr.processlist_id IS NOT NULL AND PROCESSLIST_USER IS NOT NULL AND trx.state = 'ACTIVE' GROUP BY thread_id, timer_wait ORDER BY TIMER_WAIT DESC LIMIT 10;
We can see that currently we have an active transaction open for more than 43 minutes and doing… nothing it seems.
The sleeping ones are those that are practically causing the most issues as they might be interactive sessions that have been forgotten and will stay alive for a long, long time by default (8 hours,
It’s also possible to list the statements that were performed in this transaction (limited to 10 by default,
performance_schema_events_statements_history_size) if the instrumentation is enabled:
UPDATE performance_schema.setup_consumers SET enabled = 'yes' WHERE name LIKE 'events_statements_history_long' OR name LIKE 'events_transactions_history_long';
Now that is enabled, we can see the history for all new transactions using the following statement:
SELECT DATE_SUB(now(), INTERVAL ( SELECT variable_value FROM performance_schema.global_status WHERE variable_name='UPTIME')-TIMER_START*10e-13 second) `start_time`, SQL_TEXT FROM performance_schema.events_statements_history WHERE nesting_event_id=( SELECT EVENT_ID FROM performance_schema.events_transactions_current t LEFT JOIN sys.processlist p ON p.thd_id=t.thread_id WHERE conn_id=<VALUE OF mysql_thread_id COLUMN>) ORDER BY event_id;
Let’s try it:
As you can see, we can have a list of the previous statements that were executed in this long transaction.
Performance_Schema contains all what we need.
Enjoy MySQL and avoid long transactions !