List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:May 24 2011 12:10pm
Subject:Re: After long semaphore waits MySQL becomes unresponsive.
View as plain text  
----- 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
Thread
After long semaphore waits MySQL becomes unresponsive.rik24 May
  • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman24 May
    • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus24 May
      • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman24 May
        • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus24 May
          • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman24 May
            • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus25 May
              • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman25 May
                • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus25 May
                  • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman25 May