From: Johan De Meersman Date: May 24 2011 12:10pm Subject: Re: After long semaphore waits MySQL becomes unresponsive. List-Archive: http://lists.mysql.com/mysql/225085 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit ----- Original Message ----- > From: rik@stripped > > I may be wrong here, but I tend to interpret this as '140054029002496' is > trying to get an exclusive lock on 0x78733f8, on which it already has an > exclusive lock, and hence is deadlocked in some manner. Am I right there? How > can this happen? I'm not too hot on the internals, but yes, that seems likely. > I certainly cannot reproduce a query which causes this, You'd need at least two :-p > and I had to kill -9 > the process, so nothing no running/long queries were written to the slow-query > log. (On a side note: not even root / superuser could connect to the MySQL check the max_user_connections setting, and set it a couple of notches lower than the max_connections one. It basically says "only this much non-super users may connect" and leaves the rest for super privileged users - which should only be admins, not applications. > instance, so there was no way to check which queries were actually running) If > not, what should I look for in trying to determine the cause? (Added some > extra monitor output below sig in case it's needed). Well... Your innodb status, if you can connect :-) Can't be bothered to write down the reasoning, but the simple way to avoid deadlocks is to always, in all processes, lock all tables in the same order - alphabetically, for instance. that way deadlock gets pre-empted before it can occur. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel