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 > head. 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 current problem. > > 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 a halt. 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). -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
