MySQL Bugs: #66469: RFE: innodb_txn_blocking

Bug #66469	RFE: innodb_txn_blocking_timeout
Submitted:	20 Aug 2012 20:59	Modified:	21 Sep 2012 6:56
Reporter:	Kevin Benton	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As a DBA, I need tools that help me figure out what SQL statements (executed by an application or user) are causing other statements to be held up, specifically under InnoDB. In our situation, there are times when we want to kill any long-running transactions (OLAP queries) that are run on a database that are only meant for OLTP. I'd like to see both a global and a session variable as above (or similar) that will force a long-running transaction to time out (and roll back) if other transactions are waiting on rows or tables that the offending transaction holds.

innodb_lock_wait_timeout affects the waiters but not the blocker. This would affect the blocker.

Yes, we're aware that by setting a variable like this, it could potentially prevent data loads from working. That's why we'd like to make the variable settable at both the session and at the global levels.

How to repeat:
See description.

Suggested fix:
See description.

Why not to monitor blocking locks yourself as described in our manual, http://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema-transactions.html#innodb-...? 

If you use 5.5 (or 5.1+InnoDB plugin, AFAIR) this is the way to identify sessions that blocks others and, having thread # you can kill it, or just kill current query, or whatever... Why we should introduce new feature/variable if there is a way to solve the problem using existing features?

I think this would be a good feature request, unless the code is too onerous or performance impact too high.  Even if there is a (complicated) way to script it.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This bug was closed due to "no feedback" when in actuality there was a response - entered by me but from an affected customer.  Please re-open.

Customer provides the following response:  "because that requires someone to actively monitor those. On the other hand, if we have a timeout, the server can force a long-running transaction to rollback - a very desirable feature. This keeps things moving and prevents locks from being held open too long in cases where the DBA determines that it's more important to keep things moving than to allow a long-running lock to block other changes. "

I would also add that, in addition, if it were possible to set this at a session or global level (so, session-level would mean, "don't let this session block others too long"), it would add functionality to the existing features.  (If that is possible.)