From: Michael Widenius Date: March 14 1999 12:54pm Subject: stuck/locked queries List-Archive: http://lists.mysql.com/mysql/232 Message-Id: <14059.45214.69008.156325@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit >>>>> "Naveen" == Naveen Nalam writes: Naveen> I have this event occur about once a day where it appears that a SELECT Naveen> query will get stuck for some reason, then when UPDATE queries occur they Naveen> block waiting for access to the table. Then after the UPDATEs get blocked, Naveen> future SELECTs get blocked (I guess since SELECT is lower priority that Naveen> UPDATE) until max-connections is reached - at which point nothing can Naveen> occur. I've tried using no LOCK statements at all and the problem still Naveen> occurs. Below is output of when I'm using LOCK statements before and after Naveen> every query. Naveen> The output of mysqladmin looks like: Naveen> +-------+--------+-------------------------+----- Naveen> | db | Command | Info Naveen> +-------+--------+-------------------------+----- Naveen> | media | Locked | LOCK TABLES HostInfo WRITE | Naveen> | media | Locked | LOCK TABLES HostInfo WRITE | Naveen> | media | Query | SELECT filename,size FROM HostInfo h | Naveen> | media | Locked | LOCK TABLES HostInfo AS h READ| Naveen> | media | Locked | LOCK TABLES HostInfo AS h READ| Naveen> | media | Locked | LOCK TABLES HostInfo READ| Naveen> | media | Locked | LOCK TABLES HostInfo AS h READ| Naveen> | media | Locked | LOCK TABLES HostInfo AS h READ| Naveen> ... Naveen> Any thoughts on what might be the problem? For some reason the Query (on Naveen> 3rd line) doesn't go away. After I noticed the problem, I waited a minute Naveen> to see if it would go away, but it didn't - so I'm thinking for some Naveen> reason it gets "stuck" in the mysql server. Is there a way to have mysqld Naveen> terminate queries that last longer than 10 seconds? Not for the moment; You can however do this with a simple script (perl ?) that monitors MySQL and uses 'kill' to kill queries that has been going on for a long time. Naveen> I think that by changing the WRITES to low priority, at least people would Naveen> still be able to execute SELECTs, but since that one thread never release Naveen> the READ lock, the WRITEs will never occur. Naveen> I'm using 3.21.33c w/ Red Hat 5.2 (I can't upgrade to 3.22.x cus for some Naveen> reason it runs about 5-10 times slower on the same query, same db data, Naveen> same machine.) The right way to solve this is to use MySQL 3.22; I will shortly comment about your problem with MySQL 3.22 and 3.21 when it comes to speed; I can see no reason why MySQL 3.22 would be slower; If this is the case for you, this can probable be fixed with some simple optimizer hints for your case... Regards, Monty