Bug #67906 MySQL should kill idle transactions
Submitted: 14 Dec 2012 10:09 Modified: 14 Dec 2012 10:21
Reporter: Lixun Peng (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S4 (Feature request)
Version:5.* OS:Any
Assigned to: CPU Architecture:Any
Tags: Kill Idle Transactions, Long Transactions

[14 Dec 2012 10:09] Lixun Peng
Description:
In MySQL, if we start a transaction and not commit, this transaction maybe hold locks!

So I add 3 parameters for MySQL:
TRX_CHANGES_IDLE_TIMEOUT: how long a transaction that changed data can keep living
TRX_READONLY_IDLE_TIMEOUT: how long a transaction that readonly can keep living
TRX_IDLE_TIMEOUT: how long a transaction can keep living (include readonly and not-readonly)

For example:
SET TRX_CHANGES_IDLE_TIMEOUT= 10;

SET AUTOCOMMIT = 0;
UPDATE table_a SET col_a=100;
(after 10s, no other operations)

killed this session, rollback transaction.

How to repeat:
For example:

After 5.5,

Session 1                                                               SESSION 2
SET AUTOCOMMIT = 0;
SELECT * FROM table_a LIMIT 10;
                                                                             ALTER TABLE table_a ADD col_b int;
                                                                             (table_a will do "copying to tmp table" until wait for rename, then it will wait for metalock)

if Session1 doesn't commit, Session2 can't finished.

Suggested fix:
I will kill the idle transactions, it means a transaction has not continue to do other things from last operation.
[14 Dec 2012 10:10] Lixun Peng
this patch based on 5.1.61, and we used it on our production environment

Attachment: 5.1.61_server_kill_idle_trx.diff (application/octet-stream, text), 12.90 KiB.

[15 Dec 2012 8:38] MySQL Verification Team
this effectively deals with the old problem i reported :)

http://bugs.mysql.com/bug.php?id=54455
(innodb needs a way to limit consistent read snapshot age)