----- Original Message ----- > From: "Rik Wasmus" <r...@grib.nl> > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
