From: Johan De Meersman Date: May 24 2011 4:25pm Subject: Re: After long semaphore waits MySQL becomes unresponsive. List-Archive: http://lists.mysql.com/mysql/225092 Message-Id: <739908c9-bd8b-4438-99b5-571c6063d1a3@zimbra> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit ----- Original Message ----- > From: "Rik Wasmus" > > 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' :) Ah, misread that. Yes, the former behaviour seems more like a bug; which is not entirely impossible of course. > 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... Which is what kind of mystifies me - it should detect deadlocks as soon as they happen. > 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. Yups. A trending database (munin, cacti or something) may or may not offer much hindsight in this case (mostly a matter of luck at when it last checked); but it's definitely something useful to have at hand for plenty of other purposes. > 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). Uhuh. You may want to take this to the mysql-dev mailinglist, the good people there might have a bit more insight about the error runes you posted. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel