List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:May 24 2011 2:30pm
Subject:Re: After long semaphore waits MySQL becomes unresponsive.
View as plain text  
----- Original Message -----
> From: "Rik Wasmus" <rik@stripped>
> 
> 2 simultaneous queries with the same thread-id? Is that possible?

No, not with the same thread-id. Deadlock occurs when two threads attempt to lock the same
resources, but do so in an order which causes both sides to hold part of the resources the
other needs.

Say you have tables a and b, both wanted by threads 1 and 2. At roughly the same time,
thread 1 locks a while thread 2 locks b. The next obvious step for both is that 1 tries
to lock b and 2 tries to lock a; but those resources are both in use, so at this point
both threads must wait for the other to free up the resources. Since neither of them can
complete and free the held locks for the other to use, deadlock occurs.

In theory, this will last until one of them times out or gives up. In practice, the engine
will (mostly...) notice that the second thread is trying to initiate a deadlock and
unceremoniously shoot the bastard in the head.

> I always thought (based on
> http://dev.mysql.com/doc/refman/5.5/en/too-many-
> connections.html) I'd get 1 super-connection without question, and I

That should be the case, and I used to understand it that way, too; but experience has
taught me not to rely on that :-)

> didn't get any 'too many connections' errors, but timeouts on connecting.
> But let's say that in this case (MySQL needing a kill -9) all bets about proper
> connecting are sort of off ;) ). Nevertheless, according to the
> manual, max_user_connections configures "The maximum number of simultaneous
> connections permitted to any given MySQL user account.", which would

Hmm. you're right. Still, I'd say that it's unlikely that connection swamping happens from
more than one account at once, so it should help in most instances (and has saved me
before).

> mean (and a test shows) it is per-user name, and it would only work if we have
> only 1 non-admin/non-super-user, while in reality, we have several users
> with specific privileges. Not an incredible lot, but about 20 nonetheless,

'super' refers to a very specific privilege, not a random combination of administrative
stuff like create/drop, and 20 users is a whole lot to give that to. You should really
consider if that many need it.

> Ack, I assume this means the 'SHOW ENGINE INNODB STATUS;' at the
> moment of the problem, rather then just the output I get now?

Yep. The output is a combination of current data and since-start counters.

> Hmm, we're never using explicit (table) locks, only implicit by the
> innodb-transaction, ans usually even then the transaction is just the single
> statement (autocommit is usually on save for a few instances). Does
> order in joins matter in terms of setting them alphabetically? That would mean

Uhh... Unsure, but it might.

> checking & rewriting a heck of a lot of codebase... Also, taking for instance
> the latest detected deadlock from the innodb status, I don't know how to
> actually prevent it on an SQL-query level:

Strictly speaking, the engine should detect it and kill the thread initiating deadlock -
as seen in your output there.


-- 
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