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