Bug #54455 innodb needs a way to limit consistent read snapshot age
Submitted: 12 Jun 2010 11:11 Modified: 12 Jun 2010 13:40
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: Sunny Bains CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request) / R3 (Medium) / E3 (Medium)

[12 Jun 2010 11:11] Shane Bester
Description:
too many times we see cases where long-running idle transactions are causing these symptoms: endlessly increasing ibdata* file size, climbing history list length,
and general slowing down of all transactions. e.g:

Trx id counter DB5DD
Purge done for trx's n:o < A66FD undo n:o < 3
History list length 87585
---TRANSACTION A66FC, ACTIVE 9256 sec, process no 18606, OS thread id 1192036672

How to repeat:
on any innodb server that is doing alot of DML, just run "start transaction with consistent snapshot" and leave the connection open.  to avoid wait_timeout, you can "select 1" every few minutes.  monitor innodb status..

Suggested fix:
i dunno, create some option to let dba configure maximum length of a transaction, so that innodb kills it and it returns 'Snapshot Too Old' or something.
it can be disabled by default to maintain existing compatibility.
[12 Jun 2010 13:40] Valeriy Kravchuk
Thank you for the feature request.
[16 Jun 2010 1:06] Mark Callaghan
I really, really want this feature. It is an intermittent source of problems and it is easy to forget to look at SHOW INNODB STATUS or SHOW INNODB TRANSACTION STATUS to find the long-open transaction.
[1 Aug 2010 11:39] Shane Bester
also reported as bug #34414 which demonstrates the problem is more widespread.
[5 Dec 2010 9:01] James Day
Mark, would handling of a thread that's blocking flush in a similar way to a lock wait timeout be sufficient or are there more cases that you think we should handle? Flush blocked is a clear "no choice but to act" situation because the server can't do any more DML work until the situation is resolved.

There are other possible cases, which ones are of interest to you and also have a fairly low chance of side effects, with decently reliable heuristics to use to decide "stop that thread now"?
[9 Dec 2010 5:32] James Day
My last comment should have been purge thread, not flush thread.
[6 Apr 2011 14:33] Shane Bester
http://yoshinorimatsunobu.blogspot.com/2011/04/tracking-long-running-transactions-in.html
[7 May 2011 12:22] Shane Bester
http://www.mysqlperformanceblog.com/2011/03/08/how-to-debug-long-running-transactions-in-m...
[3 Jun 2011 5:15] Shane Bester
http://www.mysqlperformanceblog.com/2011/06/02/active-with-locks-now-thats-a-problem/
[16 Nov 2012 16:06] Shane Bester
http://mysqlquicksand.wordpress.com/2012/11/15/runaway-history-list/
[15 Dec 2012 11:44] Lixun Peng
Mark, you can look at my patch in bug#67906(http://bugs.mysql.com/bug.php?id=67906)
[20 Apr 2014 14:16] Shane Bester
Also:
http://bugs.mysql.com/bug.php?id=72362
http://blog.jcole.us/2014/04/16/a-little-fun-with-innodb-multi-versioning/
[7 Jul 2015 17:09] Shane Bester
More related text:
http://smalldatum.blogspot.com/2015/07/the-impact-of-long-running-transactions.html
https://bugs.mysql.com/bug.php?id=74919 
(purge should remove intermediate rows between far snapshots)
[10 May 2017 11:46] Shane Bester
https://www.percona.com/blog/2017/05/08/chasing-a-hung-transaction-in-mysql-innodb-history...
[4 Feb 18:42] Shane Bester
With lagging purge you may very well expose this:

https://bugs.mysql.com/bug.php?id=84958
(InnoDB's MVCC has O(N^2) behaviors)