Why do reads block other reads in MyISAM?
All the selects are locked because they are waiting for your UPDATE to finish, even though the update is not running yet
I guess the order of your queries was:
- SELECT COUNT(*) FROM ...
- UPDATE
metaward_award
... - SELECT
metaward_alias
... - ...
query1 is executing.. it takes long time
query2 comes, want to get a write lock, but cant, because there is a read, so it waits and signal it's waiting to lock
query3 comes, want to read but cant, because query2 already signal a lock
now you will say.. MyISAM is concurrent reads, query3 should read while query1 is reading.. but if that was the case, and query3 took long time too, you could make a "starvation" on query2, meaning it will never get executed if query1 & query3 took a long time and go after each other, example:
assuming query1, query3 takes 5 secs to exec
second | action
1 | query1 starting
2 | query2 cant start, waiting
3 | query3 starting
4 |
5 |
6 | qurey1 finished (query2 cant start because query3 is still reading)
7 | another call for query1 starting
8 | qurey3 finished (query2 cant start because the new query1 is still reading)
You can use the server option --low-priority-updates to give SELECT
statements priority over table modifying operations (INSERT
, REPLACE
, DELETE
and UPDATE
)
You can also use the LOW_PRIORITY
modifier after your statement (e.g. UPDATE LOW_PRIORITY
)
Edit: there is more information here: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html