Почему чтения блокируют другие чтения в MyISAM?
Просто посмотрите исходный код regsvr32.exe
2 ответа
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)
Вы можете использовать серверную опцию --low-priority-updates, чтобы дать операторам SELECT приоритет над операциями изменения таблицы (INSERT, REPLACE, DELETE и UPDATE)
Вы также можете использовать LOW_PRIORITY модификатор после вашего утверждения (например, UPDATE LOW_PRIORITY)
Изменить: здесь есть дополнительная информация: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html