sql – 为什么读取会阻止MyISAM中的其他读取？
发布时间：2020-12-12 08:31:15 所属栏目：MsSql教程 来源：网络整理
导读：我有一个很长时间的阅读.它是一个每天运行一次的cronjob,但整个数据库在运行时被锁定： mysql show full processlist;+--------+------+-----------+------+---------+------+--------------+--------------------------------------------------------------
我有一个很长时间的阅读.它是一个每天运行一次的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 … 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) （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |