Bug #68129 A new column shows the active time of current transaction
Submitted: 21 Jan 2013 3:55 Modified: 23 Jan 2013 17:46
Reporter: xiaobin lin (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:all OS:Any
Assigned to: CPU Architecture:Any
Tags: active time, show processlist, transaction

[21 Jan 2013 3:55] xiaobin lin 
Description:
Some clients will run "begin; select * from t limit n",and then idle, which will affect the server. Such as the InnoDB share systemspace will become large. 

Such clients maybe bug from programs or some unreasonable operations from manual, monitors can get information from the output,warning or kill idle threads. 

How to repeat:
as above

Suggested fix:
Adding a new column in the result of "show processlist".
[21 Jan 2013 3:57] xiaobin lin 
patch for new column in show processlist

Attachment: processlist_trans_active.diff (application/octet-stream, text), 3.06 KiB.
[21 Jan 2013 11:18] Hartmut Holzgraefe 
Apache ActiveMQ even does this on purpose, doing repeated SELECT FOR UPDATEs on a special row in a never ending transaction as a locking mechanism so that only one ActiveMQ instance can be active at any given time ...
which lead to the discovery of bug #65037
[23 Jan 2013 17:46] MySQL Verification Team 
Thank you for the feature request and contribution.