为什么 MyISAM 中的读取会阻塞其他读取？

Question

我有一本很长的读物。这是一个每天运行一次的 cronjob，但是整个数据库在运行时被锁定：

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)

我可以理解更新被锁定，但为什么 SELECT 也被锁定？

Answer 1

所有选择都被锁定，因为它们正在等待您的 UPDATE 完成，即使更新尚未运行，

我猜您的查询顺序是：

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

query1正在执行..需要很长时间

query2来了，想获得写锁，但不能，因为有读，所以它等待发出信号正在等待锁定

query3 来，想要读取但不能，因为 query2 已经发出锁定信号，

现在您会说.. MyISAM 是并发读取，query3 应该在 query1 正在读取时读取.. 但如果是这种情况，并且 query3 采取了很长时间以来，您都可以对 query2 进行“饥饿”，这意味着如果 query1 & 则它永远不会被执行。 query3 花了很长时间并且互相追赶，例如：

假设 query1，query3 需要 5 秒来执行

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)

Answer 2

您可以使用服务器选项 --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