Comments on: Killing InnoDB idle transactions https://shlomi-noach.github.io/blog/mysql/killing-innodb-idle-transactions Blog by Shlomi Noach Fri, 07 Dec 2012 00:35:23 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: Marc https://shlomi-noach.github.io/blog/mysql/killing-innodb-idle-transactions/comment-page-1#comment-144233 Fri, 07 Dec 2012 00:35:23 +0000 https://shlomi-noach.github.io/blog/?p=5422#comment-144233 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?

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/killing-innodb-idle-transactions/comment-page-1#comment-143059 Tue, 04 Dec 2012 16:58:48 +0000 https://shlomi-noach.github.io/blog/?p=5422#comment-143059 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.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/killing-innodb-idle-transactions/comment-page-1#comment-143058 Tue, 04 Dec 2012 16:56:10 +0000 https://shlomi-noach.github.io/blog/?p=5422#comment-143058 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.

]]> By: Peter Laursen https://shlomi-noach.github.io/blog/mysql/killing-innodb-idle-transactions/comment-page-1#comment-143041 Tue, 04 Dec 2012 16:09:19 +0000 https://shlomi-noach.github.io/blog/?p=5422#comment-143041 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

]]>