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 = re.search(r'^MySQL\sthread\sid\s(\d+),', line) if match: print 'MySQL thread id', match.group(1), ' has been running for ', secondsTransactionElapsed, ' seconds' print 'To kill it, run: kill', match.group(1) targetTransactionFound = False else: match = re.search(r'^---TRANSACTION\s\w+,\sACTIVE\s(\d+)\ssec', line) if match: if (long(match.group(1)) > longRunningThreshold): targetTransactionFound = True secondsTransactionElapsed = match.group(1)