List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 14 1999 12:54pm
Subject:stuck/locked queries
View as plain text  
>>>>> "Naveen" == Naveen Nalam <nalam@stripped> 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
Thread
stuck/locked queriesNaveen Nalam12 Mar
  • stuck/locked queriesMichael Widenius15 Mar