我有一个很长时间的阅读.它是一个每天运行一次的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`.`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`,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`.`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`.`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.`shortname`,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也被锁定了？
解决方法
所有选择都被锁定,因为它们正在等待您的UPDATE完成,即使更新尚未运行
我想你的查询顺序是：
> SELECT COUNT(*)FROM …
> UPDATE Metaward_award …
> SELECT Metaward_alias …
> ……
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)
