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:
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
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
[7 May 2011 12:22] Shane Bester
[3 Jun 2011 5:15] Shane Bester
[16 Nov 2012 16:06] Shane Bester
[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
[7 Jul 2015 17:09] Shane Bester
More related text:
(purge should remove intermediate rows between far snapshots)
[10 May 2017 11:46] Shane Bester
[4 Feb 2018 18:42] Shane Bester
With lagging purge you may very well expose this:

(InnoDB's MVCC has O(N^2) behaviors)