Notes:
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.

[sourcecode language=”python”]
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)
[/sourcecode]