Finding long running INNODB transactions

1. The script prints out elapsed time since transaction started, MySQL thread id, and the kill statement for transactions running longer than a defined threshold value, in seconds. Just copy, paster, and then execute the kill statement if you want to terminate the long transaction(s);
2. Adjust shellCmd variable;
3. Adjust longRunningThreshold value as needed. It is measured in seconds;
4. No special libraries/modules needed, as long as there is a working mysql client;
5. re module is used for regex processing. Good place to find examples of regular expression search and grouping. A status variable is used to assist locating MySQL thread id once a transaction running longer than the defined threshold is found.

import re, shlex, subprocess 

def runCmd(cmd):
    proc = subprocess.Popen(shlex.split(cmd), stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=False)
    out, err = proc.communicate()
    ret = proc.returncode
    return (ret, out, err)

shellCmd = """mysql -h hostName -e "show innodb status\\G""""
longRunningThreshold = 600

returnCode, stdOut, stdErr = runCmd(shellCmd)

targetTransactionFound = False
for line in stdOut.split('\n'):
    if targetTransactionFound:
        match ='^MySQL\sthread\sid\s(\d+),', line)
        if match:
            print 'MySQL thread id',, ' has been running for ', secondsTransactionElapsed, ' seconds'
            print 'To kill it, run: kill',
            targetTransactionFound = False
        match ='^---TRANSACTION\s\w+,\sACTIVE\s(\d+)\ssec', line)
        if match:
            if (long( > longRunningThreshold):
                targetTransactionFound = True
                secondsTransactionElapsed =

3 thoughts on “Finding long running INNODB transactions”

  1. Thanks Vasil for the link.

    It looks interesting. I will find some time to dig into it. Mark Leith’s blog post on the same subject is also pretty interesting.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.