sql – 为什么读取会阻止MyISAM中的其他读取?

前端之家收集整理的这篇文章主要介绍了sql – 为什么读取会阻止MyISAM中的其他读取?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个很长时间的阅读.它是一个每天运行一次的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)

猜你在找的MsSQL相关文章