List:General Discussion« Previous MessageNext Message »
From:Naveen Nalam Date:March 12 1999 11:34am
Subject:stuck/locked queries
View as plain text  
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

Thread
stuck/locked queriesNaveen Nalam12 Mar
  • stuck/locked queriesMichael Widenius15 Mar