{"id":5422,"date":"2012-12-04T14:23:12","date_gmt":"2012-12-04T12:23:12","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5422"},"modified":"2012-12-04T21:39:06","modified_gmt":"2012-12-04T19:39:06","slug":"killing-innodb-idle-transactions","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/killing-innodb-idle-transactions","title":{"rendered":"Killing InnoDB idle transactions"},"content":{"rendered":"<p>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 <a href=\"http:\/\/code.google.com\/p\/common-schema\/\">common_schema<\/a>.<\/p>\n<p><em>common_schema <strong>1.2<\/strong><\/em> provides with the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/innodb_transactions.html\"><strong>innodb_transactions<\/strong><\/a> view, which relies on <strong>INNODB_TRX<\/strong> &#8211; one of the InnoDB Plugin views in <strong>INFORMATION_SCHEMA<\/strong> &#8211; as well as on <strong>PROCESSLIST<\/strong>, and so is able to determine with certainty that a transaction has been idle for a long time.<\/p>\n<p><strong>innodb_transactions<\/strong> offers us with a <strong>sql_kill_query<\/strong> column, which produces a <strong>&#8216;KILL QUERY 12345&#8217;<\/strong> type of value. So we can:<\/p>\n<blockquote>\n<pre>SELECT <strong>sql_kill_query<\/strong> FROM <strong>innodb_transactions<\/strong> WHERE <strong>trx_idle_seconds &gt;= 10; \r\n<\/strong>+-------------------+\r\n| sql_kill_query\u00a0\u00a0\u00a0 |\r\n+-------------------+\r\n| KILL QUERY 292509 |\r\n| KILL QUERY 292475 |\r\n+-------------------+<strong> <\/strong><\/pre>\n<\/blockquote>\n<p><em>common_schema<\/em>&#8216;s useful <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/eval.html\"><strong>eval()<\/strong><\/a> routine allows us to actually invoke those <strong>KILL<\/strong> statements, all in a one-liner:<\/p>\n<blockquote>\n<pre>call <strong>eval<\/strong>(<span style=\"color: #003366;\">'SELECT <strong>sql_kill_query<\/strong> FROM innodb_transactions WHERE <strong>trx_idle_seconds &gt;= 10<\/strong>'<\/span>);<\/pre>\n<\/blockquote>\n<h4>Technical details<!--more--><\/h4>\n<ul>\n<li><strong>trx_idle_seconds<\/strong> notes the time, in seconds, the transaction has been idle, or 0 if the transaction is not idle at all.<\/li>\n<li><strong>sql_kill_query<\/strong> is a self-generated SQL query which kills the running query, e.g. <strong>&#8216;KILL QUERY 12345&#8217;<\/strong>.<\/li>\n<li><strong>eval()<\/strong> takes a query as text, retrieves the SQL resulting column, and executes it live.<\/li>\n<\/ul>\n<h4>Background details<\/h4>\n<p>The connection between <strong>INNODB_TRX<\/strong> and <strong>PROCESSLIST<\/strong> is not synchronous. It is possible that by the time one is querying <strong>INNODB_TRX<\/strong>, <strong>PROCESSLIST<\/strong> data may change (e.g. next query is already replacing the one you were considering in <strong>INNODB_TRX<\/strong>). But in our case it is of little consequence: we are interested in transactions that have been idle for quite some time. Say, <strong>10<\/strong> seconds. So we are not troubled by having <strong>200<\/strong> queries per second changing under our hands.<\/p>\n<p>If the transaction has been asleep for <strong>10<\/strong> seconds, and we decide to kill it, well, it is possible that just as we kill it it will turn active again. It&#8217;s a risk we take no matter what kind of solution we apply, since there&#8217;s no atomic &#8220;get-status-and-kill&#8221; operation on InnoDB transactions.<\/p>\n<p>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 <strong>10<\/strong> seconds, for example.<\/p>\n<p>See the many related and inspiring solutions below:<\/p>\n<ul>\n<li><a href=\"http:\/\/mysqlblog.fivefarmers.com\/2012\/08\/28\/identifying-and-killing-blocking-transactions-in-innodb\/\">Identifying and killing blocking transactions in InnoDB<\/a><\/li>\n<li><a href=\"http:\/\/www.markleith.co.uk\/2011\/05\/31\/finding-and-killing-long-running-innodb-transactions-with-events\/\">Finding and killing long running InnoDB transactions with Events<\/a><\/li>\n<li><a href=\"http:\/\/datacharmer.blogspot.co.il\/2008\/10\/using-event-scheduler-to-purge-process.html\">Using the event scheduler to purge the process list<\/a><\/li>\n<li><a href=\"http:\/\/www.mysqlperformanceblog.com\/2011\/03\/08\/how-to-debug-long-running-transactions-in-mysql\/\">How to debug long-running transactions in MySQL<\/a><\/li>\n<li><a href=\"http:\/\/yoshinorimatsunobu.blogspot.co.il\/2011\/04\/tracking-long-running-transactions-in.html\">Tracking long running transactions in MySQL<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8211; one of the InnoDB Plugin views in INFORMATION_SCHEMA &#8211; as well as on [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[67,24,14,50],"class_list":["post-5422","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-information_schema","tag-innodb","tag-scripts"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1ps","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5422","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=5422"}],"version-history":[{"count":33,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5422\/revisions"}],"predecessor-version":[{"id":5854,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5422\/revisions\/5854"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5422"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5422"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5422"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}