On 2011-05-24 16:30:13 you wrote:
> > 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.
OK, but that would mean that the answer to the question:
"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" is 'no there is
another query' (i.e.: it isn't locked on mistakingly acquiring a lock it
already has) rather then 'that seems likely' :)
> 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
And in my case, the server became unusable (kept running into semaphore locks
at 769 seconds before a kill & start was given). Query timeouts / crashes I
can live with, an unresponsive server I cannot...
> > 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).
Ack, could be, however, the rest of the users quickly eat away at the safety
margin. If the max_connections is set to 5000, and mainly 1 user eats it away
with for instance 4500 max_user_connections, I still have 19 other users which
can quickly gobble up the remaining 500 between them. A mere 26-27 per user
would already achieve that, and seeing as the 'runaway user' already has 4500
connections, it is very likely something is going on like a load / visitors
spike that other users also may be requiring / asking for more connections.
All in all, I don't really think the max_user_connections would/will help the
> > 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.
I know that, that's why i said NON-super users :). There are but 2 SUPER
users, 1 solely is used by the OS for stuff like 'flush-logs' after
logrotating (may connect and do something maybe 5 times a day max), 1 is
solely used by the likes of me monitoring / debugging / administrating the
server, never by code, and hopefully rarely connects :)
> > 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.
OK, let's hope I never get to show that output (i.e: that the problem doesn't
reoccur). Since the server has been restarted since-start counters will
probably be pretty useless.
> > 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.
Yup, right there it did, And that's the way I like it: kill the/a query, which
issues an error somewhere else we know if and how to handle in some
application, rather then letting a database server with a light load grind to
My main problem at hand is why the server did nothing but seize up
gracelessly, rather then either dying (a last resort, but something we have
failovers for) or killing queries (which we can handle).