In this blog post, we will discuss how to display long transactions in MySQL 8.0. Having transactions running for a long time can lead to performance issues and can cause the database to become unresponsive. In order to avoid these issues, it is important to monitor and manage long transactions in your database. In this post, we will show you how to identify and display long transactions in MySQL 8.0.

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 check.getRunningStatements().

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;

Long transaction example output

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, interactive_timeout).

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:

Statements part of a transaction

As you can see, we can have a list of the previous statements that were executed in this long transaction.

Once again, Performance_Schema contains all what we need.

Enjoy MySQL and avoid long transactions !