Why do reads block other reads in MyISAM?

Question

I have one really long running read. It is a cronjob run once a day, but the whole DB gets locked down when it is running :

mysql> show full processlist;
+--------+------+-----------+------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id     | User | Host      | db   | Command | Time | State        | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+--------+------+-----------+------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 464971 | paul | localhost | paul | Sleep   | 2264 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 
| 472177 | paul | localhost | paul | Sleep   |   96 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 
| 479527 | paul | localhost | paul | Sleep   | 1765 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 
| 479528 | paul | localhost | paul | Sleep   | 1765 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 
| 479623 | paul | localhost | paul | Query   |   83 | Locked       | SELECT `metaward_alias`.`id`, `metaward_alias`.`modified`, `metaward_alias`.`created`, `metaward_alias`.`string_id`, `metaward_alias`.`shortname`, `metaward_alias`.`remote_image`, `metaward_alias`.`image`, `metaward_alias`.`user_id`, `metaward_alias`.`type_id`, `metaward_alias`.`md5` FROM `metaward_alias` WHERE `metaward_alias`.`string_id` = 'http://profiles.us.playstation.com/playstation/psn/profiles/Kool_Aid_Dude27'  ORDER BY `metaward_alias`.`modified` DESC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | 
| 479624 | paul | localhost | paul | Sleep   |   82 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 
| 480057 | paul | localhost | paul | Query   |   88 | Locked       | UPDATE `metaward_award` SET `modified` = '2009-09-16 02:12:37', `created` = '2009-09-08 12:06:44', `string_id` = 'isaw5t', `owner_id` = 1135, `name` = '50 online matches won', `description` = 'Aim to win 50 online matches. (ranked match)', `owner_points` = 50, `url` = 'http://live.xbox.com/en-US/profile/Achievements/ViewAchievementDetails.aspx?tid=%09%5d%3a%13%1f%5d%1fGt%06', `remote_image` = 'http://tiles.xbox.com/tiles/6G/dm/1oCLiGJhbC9CCxtyGy1TVkRBL2FjaC8wLzE2AAAAAOfn5-lJZ-Q=.jpg', `image` = 'award/isaw5t.png', `parent_award_id` = 115242, `slug` = '50-online-matches-won-1', `points` = 43.9 WHERE `metaward_award`.`id` = 116054                                                                                                                                                                                                                                                                                                                                                                                                     | 
| 480571 | paul | localhost | paul | Query   |   84 | Locked       | SELECT `metaward_alias`.`id`, `metaward_alias`.`modified`, `metaward_alias`.`created`, `metaward_alias`.`string_id`, `metaward_alias`.`shortname`, `metaward_alias`.`remote_image`, `metaward_alias`.`image`, `metaward_alias`.`user_id`, `metaward_alias`.`type_id`, `metaward_alias`.`md5` FROM `metaward_alias` WHERE `metaward_alias`.`string_id` = 'http://live.xbox.com/en-US/profile/profile.aspx?GamerTag=Hendricks'  ORDER BY `metaward_alias`.`modified` DESC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | 
| 480578 | paul | localhost | paul | Query   |   86 | Locked       | UPDATE `metaward_alias` SET `modified` = '2009-09-16 02:12:38', `created` = '2009-09-09 01:21:08', `string_id` = 'http://live.xbox.com/en-US/profile/profile.aspx?GamerTag=jobbie%20man', `shortname` = 'jobbie man', `remote_image` = 'http://avatar.xboxlive.com/avatar/jobbie%20man/avatarpic-l.png', `image` = 'alias/2ec3d391a311be936d9603f99dcfa353.png', `user_id` = NULL, `type_id` = 1135, `md5` = '2ec3d391a311be936d9603f99dcfa353' WHERE `metaward_alias`.`id` = 705419                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 
| 480579 | paul | localhost | paul | Query   |   88 | Locked       | SELECT `metaward_award`.`id`, `metaward_award`.`modified`, `metaward_award`.`created`, `metaward_award`.`string_id`, `metaward_award`.`owner_id`, `metaward_award`.`name`, `metaward_award`.`description`, `metaward_award`.`owner_points`, `metaward_award`.`url`, `metaward_award`.`remote_image`, `metaward_award`.`image`, `metaward_award`.`parent_award_id`, `metaward_award`.`slug`, `metaward_award`.`points`, T3.`id`, T3.`modified`, T3.`created`, T3.`string_id`, T3.`owner_id`, T3.`name`, T3.`description`, T3.`owner_points`, T3.`url`, T3.`remote_image`, T3.`image`, T3.`parent_award_id`, T3.`slug`, T3.`points` FROM `metaward_award` LEFT OUTER JOIN `metaward_award` T3 ON (`metaward_award`.`parent_award_id` = T3.`id`) WHERE (`metaward_award`.`owner_id` = 1135  AND `metaward_award`.`owner_points` = 20  AND `metaward_award`.`name` = 'Marksman: Campaign'  AND `metaward_award`.`parent_award_id` = 27034  AND `metaward_award`.`description` = 'Kill 4 enemies with one clip of a sniper rifle.' ) ORDER BY `metaward_award`.`modified` DESC | 
| 480580 | paul | localhost | paul | Query   |   88 | Locked       | SELECT `metaward_award`.`id`, `metaward_award`.`modified`, `metaward_award`.`created`, `metaward_award`.`string_id`, `metaward_award`.`owner_id`, `metaward_award`.`name`, `metaward_award`.`description`, `metaward_award`.`owner_points`, `metaward_award`.`url`, `metaward_award`.`remote_image`, `metaward_award`.`image`, `metaward_award`.`parent_award_id`, `metaward_award`.`slug`, `metaward_award`.`points`, T3.`id`, T3.`modified`, T3.`created`, T3.`string_id`, T3.`owner_id`, T3.`name`, T3.`description`, T3.`owner_points`, T3.`url`, T3.`remote_image`, T3.`image`, T3.`parent_award_id`, T3.`slug`, T3.`points` FROM `metaward_award` LEFT OUTER JOIN `metaward_award` T3 ON (`metaward_award`.`parent_award_id` = T3.`id`) WHERE (`metaward_award`.`owner_id` = 1135  AND `metaward_award`.`owner_points` = 5  AND `metaward_award`.`name` = 'Headshot Honcho'  AND `metaward_award`.`parent_award_id` = 101442  AND `metaward_award`.`description` = 'Kill 10 enemies with headshots in a ranked free for all playlist or in campaign.' ) ORDER BY `metaward_award`.`modified` DESC | 
| 480581 | paul | localhost | paul | Query   |   86 | Locked       | SELECT `metaward_alias`.`id`, `metaward_alias`.`modified`, `metaward_alias`.`created`, `metaward_alias`.`string_id`, `metaward_alias`.`shortname`, `metaward_alias`.`remote_image`, `metaward_alias`.`image`, `metaward_alias`.`user_id`, `metaward_alias`.`type_id`, `metaward_alias`.`md5` FROM `metaward_alias` WHERE `metaward_alias`.`string_id` = 'http://www.wowarmory.com/character-sheet.xml?r=Aegwynn&cn=Fantazamor'  ORDER BY `metaward_alias`.`modified` DESC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | 
| 480626 | paul | localhost | paul | Query   |   88 | Sending data | SELECT COUNT(*) FROM `metaward_alias` INNER JOIN `metaward_achiever` ON (`metaward_alias`.`id` = `metaward_achiever`.`alias_id`) INNER JOIN `metaward_award` ON (`metaward_achiever`.`award_id` = `metaward_award`.`id`) WHERE `metaward_award`.`owner_id` = 9                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | 
| 480630 | paul | localhost | paul | Query   |   71 | Locked       | SELECT `metaward_alias`.`id`, `metaward_alias`.`modified`, `metaward_alias`.`created`, `metaward_alias`.`string_id`, `metaward_alias`.`shortname`, `metaward_alias`.`remote_image`, `metaward_alias`.`image`, `metaward_alias`.`user_id`, `metaward_alias`.`type_id`, `metaward_alias`.`md5`, T2.`id`, T2.`modified`, T2.`created`, T2.`string_id`, T2.`shortname`, T2.`remote_image`, T2.`image`, T2.`user_id`, T2.`type_id`, T2.`md5`, T3.`id`, T3.`modified`, T3.`created`, T3.`string_id`, T3.`shortname`, T3.`remote_image`, T3.`image`, T3.`user_id`, T3.`type_id`, T3.`md5`, T4.`id`, T4.`modified`, T4.`created`, T4.`string_id`, T4.`shortname`, T4.`remote_image`, T4.`image`, T4.`user_id`, T4.`type_id`, T4.`md5`, T5.`id`, T5.`modified`, T5.`created`, T5.`string_id`, T5.`shortname`, T5.`remote_image`, T5.`image`, T5.`user_id`, T5.`type_id`, T5.`md5`, T6.`id`, T6.`modified`, T6.`created`, T6.`string_id`, T6.`shortname`, T6.`remote_image`, T6.`image`, T6.`user_id`, T6.`type_id`, T6.`md5` FROM `metaward_alias` INNER JOIN `metaward_alias` T2 ON (`metaward_alias`.`type_id` = T2.`id`) INNER JOIN `metaward_alias` T3 ON (T2.`type_id` = T3.`id`) INNER JOIN `metaward_alias` T4 ON (T3.`type_id` = T4.`id`) INNER JOIN `metaward_alias` T5 ON (T4.`type_id` = T5.`id`) INNER JOIN `metaward_alias` T6 ON (T5.`type_id` = T6.`id`) WHERE `metaward_alias`.`string_id` = 'http://kongregate.com/accounts/SrGato'  ORDER BY `metaward_alias`.`modified` DESC | 
| 480632 | paul | localhost | paul | Query   |    0 | NULL         | show full processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | 
+--------+------+-----------+------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)

I can understand the updates being locked, but why are the SELECTs locked down too?

Edge

Table-level-locking is one of the many reasons not to use MyISAM. – Calistacalisthenics

I tried converting to InooDB and my site slowed down a ton. #1429178 – Edge

Answer 1

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:

1. SELECT COUNT(*) FROM ...
2. UPDATE metaward_award ...
3. SELECT metaward_alias...
4. ...

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)

Zimmerman

you can improve your performance here by running a slave and executing your long-running queries on there. – Behaviorism

Answer 2

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

Keaton

Can I make my big long SELECT run in a low priority? – Edge

LOW PRIORITY for SELECT doesn't make much sense. once a long SELECT starts, it's still going to take a long time. what would it take a back seat to? – Behaviorism

The OP has a problem, as explained in the accepted answer, where the update that is waiting for the select to finish is blocking all the other selects from starting. Setting the '--low-priority-updates' option in the my.cnf file would bump any update operations to the back of the queue and the selects waiting to happen would be allowed to start. – Keaton