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');
Technical details
- 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.
Background details
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:
HI!
Could you please explain me the difference between an ‘idle transaction’ and an ‘idle connection’ (if there is any difference)?
If there is no difference then it is perfectly valid for some client environments (in particluar C/C++) to have an idle connection (in other words to keep the connection alive between queries). This applies to most GUI tools, for instance, that may be kept open and connected the whole day.
I have the impression that this whole discussion (I saw the term ‘idle transaction’ recently in some Percona blog too) that there are other client environments than PHP and JAVA whree an idle connection is not a problem.
— Peter
Hi Peter,
An idle transaction is one where a BEGIN or START TRANSACTION have been issues, some queries may have been issued, but then lunch arrived and everybody left their desktop as they were and never COMMITed nor ROLLBACKed.
Which means lock may be held by this open transaction, and are not being released.
For completeness, this is a special case of idle connection.
I should note that not all open transactions pose a problem. More interesting are open transactions which are known to hold locks. I will improve the view to include such info.
Nice trick. Didn’t know it, thank you. For those who are looking for nice explanation of “killing” internals, http://www.dbasquare.com/2012/05/15/why-do-threads-sometimes-stay-in-killed-state-in-mysql/.
Btw, do you know how it looks like in 5.6?