The issue of terminating long-time idle open InnoDB transaction has been discussed recently by many. I wish to add my share, by proposing a quick and clean solution via common_schema.
common_schema 1.2 provides with the innodb_transactions view, which relies on INNODB_TRX - one of the InnoDB Plugin views in INFORMATION_SCHEMA - as well as on PROCESSLIST, and so is able to determine with certainty that a transaction has been idle for a long time.
innodb_transactions offers us with a sql_kill_query column, which produces a 'KILL QUERY 12345' type of value. So we can:
SELECT sql_kill_query FROM innodb_transactions WHERE trx_idle_seconds >= 10; +-------------------+ | sql_kill_query | +-------------------+ | KILL QUERY 292509 | | KILL QUERY 292475 | +-------------------+
common_schema's useful eval() routine allows us to actually invoke those KILL statements, all in a one-liner:
call eval('SELECT sql_kill_query FROM innodb_transactions WHERE trx_idle_seconds >= 10');
- trx_idle_seconds notes the time, in seconds, the transaction has been idle, or 0 if the transaction is not idle at all.
- sql_kill_query is a self-generated SQL query which kills the running query, e.g. 'KILL QUERY 12345'.
- eval() takes a query as text, retrieves the SQL resulting column, and executes it live.
The connection between INNODB_TRX and PROCESSLIST is not synchronous. It is possible that by the time one is querying INNODB_TRX, PROCESSLIST data may change (e.g. next query is already replacing the one you were considering in INNODB_TRX). But in our case it is of little consequence: we are interested in transactions that have been idle for quite some time. Say, 10 seconds. So we are not troubled by having 200 queries per second changing under our hands.
If the transaction has been asleep for 10 seconds, and we decide to kill it, well, it is possible that just as we kill it it will turn active again. It's a risk we take no matter what kind of solution we apply, since there's no atomic "get-status-and-kill" operation on InnoDB transactions.
The above solution is manual: one must invoke the query which kills the idle transactions. This is as opposed to a built-in server feature which does the same. Events can used to semi-automate this: one can call upon this query once every 10 seconds, for example.
See the many related and inspiring solutions below: