From: Naveen Nalam Date: March 12 1999 11:34am Subject: stuck/locked queries List-Archive: http://lists.mysql.com/mysql/100 Message-Id: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII I have this event occur about once a day where it appears that a SELECT query will get stuck for some reason, then when UPDATE queries occur they block waiting for access to the table. Then after the UPDATEs get blocked, future SELECTs get blocked (I guess since SELECT is lower priority that UPDATE) until max-connections is reached - at which point nothing can occur. I've tried using no LOCK statements at all and the problem still occurs. Below is output of when I'm using LOCK statements before and after every query. The output of mysqladmin looks like: +-------+--------+-------------------------+----- | db | Command | Info +-------+--------+-------------------------+----- | media | Locked | LOCK TABLES HostInfo WRITE | | media | Locked | LOCK TABLES HostInfo WRITE | | media | Query | SELECT filename,size FROM HostInfo h | | media | Locked | LOCK TABLES HostInfo AS h READ| | media | Locked | LOCK TABLES HostInfo AS h READ| | media | Locked | LOCK TABLES HostInfo READ| | media | Locked | LOCK TABLES HostInfo AS h READ| | media | Locked | LOCK TABLES HostInfo AS h READ| ... Any thoughts on what might be the problem? For some reason the Query (on 3rd line) doesn't go away. After I noticed the problem, I waited a minute to see if it would go away, but it didn't - so I'm thinking for some reason it gets "stuck" in the mysql server. Is there a way to have mysqld terminate queries that last longer than 10 seconds? I think that by changing the WRITES to low priority, at least people would still be able to execute SELECTs, but since that one thread never release the READ lock, the WRITEs will never occur. I'm using 3.21.33c w/ Red Hat 5.2 (I can't upgrade to 3.22.x cus for some reason it runs about 5-10 times slower on the same query, same db data, same machine.) -naveen