{"id":222,"date":"2013-07-03T18:11:02","date_gmt":"2013-07-03T10:11:02","guid":{"rendered":"http:\/\/www.gpfeng.com\/?p=222"},"modified":"2013-08-23T23:04:00","modified_gmt":"2013-08-23T15:04:00","slug":"flush-tables-with-read-lock","status":"publish","type":"post","link":"http:\/\/www.gpfeng.com\/?p=222","title":{"rendered":"FLUSH TABLES WITH READ LOCK"},"content":{"rendered":"<p>\u672c\u5206\u6790\u57fa\u4e8e Percona Server 5.5.18<\/p>\n<h3>FLUSH TABLES WITH READ LOCK \u4f5c\u7528<\/h3>\n<p>1. \u5bf9\u4e8emyisam\u5b58\u50a8\u5f15\u64ce\uff0c\u4ece\u6570\u636e\u5e93\u5916\u90e8\u62f7\u8d1d.frm\uff0c.fyd\uff0c.fyi\u6587\u4ef6\u540e\uff0c\u6267\u884cFLUSH TABLES\/FLUSH TABLES WITH READ LOCK\u540e\uff0c\u5c31\u53ef\u4ee5\u4f7f\u7528\u65b0\u8868\u4e86\uff0c\u8fd9\u4e2a\u529f\u80fd\u975e\u5e38\u6709\u7528\uff0c\u5907\u4efd\u53d8\u5f97\u683c\u5916\u7b80\u5355<br \/>\n2. mysqldump\u4e2d\u4ece\u4e3b\u5e93\u4e2d\u62c9\u51fa\u4e00\u4e2a\u526f\u672c\u540e\u901a\u8fc7change master\u540e\u5c31\u53ef\u4ee5\u5feb\u901f\u642d\u5efaslave\uff0c\u4e3a\u4e86\u83b7\u5f97\u4e00\u4e2a\u4e00\u81f4\u6027\u70b9\uff0c\u4e00\u822c\u4f1a\u4f7f\u7528\u53c2\u6570&#8211;single-transaction\u548c&#8211;master-data\uff0c\u5b83\u4f1a\u4f7f\u7528FLUSH TABLES WITH READ LOCK\u963b\u585e\u4e8b\u52a1commit\u5199binlog\u6765\u83b7\u5f97transaction\u5f00\u59cb\u65f6\u4e3b\u5e93binlog\u4f4d\u7f6e<br \/>\n3. \u4e0eFLUSH TABLES\u76f8\u4f3c\uff0c\u6e05\u7a7atable_cache<\/p>\n<p>\u9664\u4e86\u8fd9\u51e0\u4e2a\u91cd\u8981\u4f5c\u7528\u5916\uff0c\u76ee\u524d\u8fd8\u6ca1\u6709\u53d1\u73b0\u5176\u4ed6\u7528\u5904\uff0c\u800c\u4e14\u5bf9\u4e8eInnoDB\u5b58\u50a8\u5f15\u64ce\uff0c\u7b2c\u4e00\u6761\u5c31\u6ca1\u7528\u4e86<br \/>\n<!--more--><\/p>\n<h3>FLUSH TABLES WITH READ LOCK vs. FLUSH TABLES<\/h3>\n<p>1. \u540e\u9762\u591a\u4e86 WITH READ LOCK\u4f7f\u5f97\u8fd9\u4e24\u4e2aSQL\u547d\u4ee4\u5dee\u5f88\u5927\uff0cFLUSH TABLES WITH READ LOCK\u4f1a\u5728global read lock\u4e0a\u52a0S\u9501\uff0c\u8fd9\u4f1a\u5bfc\u81f4\u6574\u4e2a\u7cfb\u7edf\u53d8\u5f97\u53ea\u8bfb<br \/>\n2. FLUSH TABLES WITH READ LOCK\u540e<strong>\u5fc5\u987b\u663e\u793a\u6267\u884c<\/strong>UNLOCK TABLES\uff0c\u5426\u5219<strong>\u6574\u4e2a\u7cfb\u7edf\u4f1a\u4e00\u76f4\u5904\u4e8e\u53ea\u8bfb\u72b6\u6001<\/strong>\uff0c\u8fd9\u4e2a\u95ee\u9898\u975e\u5e38\u9690\u6666\uff0c\u56e0\u4e3aFLUSH TABLES WITH READ LOCK\u5b8c\u6210\u540e\u5728\u201cshow processlist\u201d\u4e2d\u65e0\u6cd5\u88ab\u89c2\u5bdf\u5230\uff0c\u9664\u975e\u8fd9\u4e2asession\u81ea\u5df1\u6267\u884cunlock tables\u6216\u8005\u62e5\u6709root\u6743\u9650\u7528\u6237kill\u8fd9\u4e2asession\uff0c\u91cd\u590d\u4e00\u6b21\uff1a<strong>FLUSH TABLES WITH READ LOCK\u540e\u4e00\u5b9a\u8981UNLOCK TABLES<\/strong>\uff0c\u9664\u975e\u4f60\u60f3\u8ba9\u7cfb\u7edf\u53ea\u8bfb\uff01<br \/>\n3. FLUSH TABLES\u5728\u4ee5\u4e0a\u4e09\u70b9\u90fd\u88ab\u7528\u5230\uff0c\u5bf9\u4e8e1\u548c3\u5f88\u597d\u7406\u89e3\uff0c2\u4e2d\u600e\u4e48\u4f1a\u7528\u5230\u5462\uff1f\u67e5\u770bmysqldump.c\u7684\u4ee3\u7801\u53ca\u6ce8\u91ca\uff1a<\/p>\n<pre lang=\"c\">\r\nstatic int do_flush_tables_read_lock(MYSQL *mysql_con)\r\n{\r\n  \/*\r\n    We do first a FLUSH TABLES. If a long update is running, the FLUSH TABLES\r\n    will wait but will not stall the whole mysqld, and when the long update is\r\n    done the FLUSH TABLES WITH READ LOCK will start and succeed quickly. So,\r\n    FLUSH TABLES is to lower the probability of a stage where both mysqldump\r\n    and most client connections are stalled. Of course, if a second long\r\n    update starts between the two FLUSHes, we have that bad stall.\r\n  *\/\r\n  return\r\n    ( mysql_query_with_error_report(mysql_con, 0, \r\n                                    ((opt_master_data != 0) ? \r\n                                        \"FLUSH \/*!40101 LOCAL *\/ TABLES\" : \r\n                                        \"FLUSH TABLES\")) ||\r\n      mysql_query_with_error_report(mysql_con, 0,\r\n                                    \"FLUSH TABLES WITH READ LOCK\") );\r\n}\r\n<\/pre>\n<p>\u89e3\u91ca\u5f88\u6e05\u695a\uff1aFLUSH TABLES WITH READ LOCK\u5f00\u59cb\u540e\u6574\u4e2a\u7cfb\u7edf\u5c31\u662fread only\u72b6\u6001\u4e86\uff0c\u4e3a\u4e86\u4f7f\u5f71\u54cd\u6700\u5c0f\uff0c\u5148\u4f7f\u7528FLUSH TABLES \u201c\u5c1d\u8bd5\u4e00\u6b21\u201d\uff0c\u5047\u8bbe\u5728FLUSH TABLES\u548cFLUSH TABLES WITH READ LOCK\u4e4b\u95f4\u5b58\u5728DML\/DDL\u7684\u53ef\u80fd\u6027\u8f83\u5c0f\uff0cFLUSH TABLES WITH READ LOCK\u6267\u884c\u65f6\u95f4\u4f1a\u5f88\u77ed\uff0c\u82e5\u4e0d\u5e78\u4e00\u4e2a\u5927DML\/DDL\u6b63\u597d\u5728\u8fd9\u4e4b\u95f4\u53d1\u751f\u4e86\uff0c\u4e8b\u60c5\u5c31\u4f1a\u53d8\u5f97\u5f88\u7cdf\u7cd5\u4e86\uff1aFLUSH TABLES WITH READ LOCK\u9700\u8981\u7b49\u5f85DML\/DDL\u6267\u884c\u5b8c\uff0c\u4e4b\u540e\u5f00\u542f\u7684DML\/DDL\u9700\u8981\u7b49\u5f85FLUSH TABLES WITH READ LOCK\u5bf9\u5e94\u7684UNLOCK TABLES\u88ab\u6267\u884c<\/p>\n<h3>FLUSH TABLES WITH READ LOCK\u5b9e\u73b0\u7ec6\u8282<\/h3>\n<p>\u5b9e\u73b0\u5206\u4e09\u6b65\uff1a(\u51fd\u6570\uff1areload_acl_and_cache)<\/p>\n<pre lang=\"mysql\">\r\n1. lock_global_read_lock\r\n--> mdl_request.init(MDL_key::GLOBAL, \"\", \"\", MDL_SHARED, MDL_EXPLICIT);\r\n2. close_cached_tables\uff08FLUSH TABLES\u53ea\u505a\u8fd9\u4e00\u6b65\uff09\r\n3. make_global_read_lock_block_commit\r\n--> mdl_request.init(MDL_key::COMMIT, \"\", \"\", MDL_SHARED, MDL_EXPLICIT);\r\n<\/pre>\n<p>\u6ce8\uff1a<br \/>\n1. \u5bf9\u4e8e\u4efb\u4f55DDL\/DML\uff0c\u4f1a\u9996\u5148\u7533\u8bf7MDL_INTENTION_EXCLUSIVE\u7c7b\u578b\u7684global read lock\uff1a<br \/>\n&#8211;> mdl_request.init(MDL_key::GLOBAL, &#8220;&#8221;, &#8220;&#8221;, <strong>MDL_INTENTION_EXCLUSIVE<\/strong>, MDL_EXPLICIT); \u7136\u540e\u7533\u8bf7\u5bf9\u5e94\u8868\u4e0a\u7684\u76f8\u5e94\u5143\u6570\u636e\u9501<br \/>\n2. \u5bf9\u4e8e\u4efb\u4f55DDL\/DML\uff0c\u63d0\u4ea4\u65f6\u4f1a\u7533\u8bf7MDL_INTENTION_EXCLUSIVE\u7c7b\u578b\u7684global commit lock:<br \/>\n&#8211;> mdl_request.init(MDL_key::COMMIT, &#8220;&#8221;, &#8220;&#8221;, <strong>MDL_INTENTION_EXCLUSIVE<\/strong>, MDL_EXPLICIT);(\u4ee3\u7801\u89c1\uff1aha_commit_trans)<br \/>\n3. select\u64cd\u4f5c\u4e0d\u4f1a\u7533\u8bf7\u4efb\u4f55\u7c7b\u578b\u7684global read lock\u6216\u8005global commit lock<\/p>\n<p>\u56e0\u6b64FLUSH TABLES WITH READ LOCK\u7684\u4e09\u4e2a\u6b65\u9aa4\u4e2d\uff1a<br \/>\n1 \u4f1a\u7b49\u5f85DDL\/DML\u7ed3\u675f\u540e\u624d\u4f1a\u5f00\u59cb\uff0c\u5e76\u4e14\u4e00\u65e6\u5f00\u59cb\u540e\uff0c\u6574\u4e2a\u7cfb\u7edf\u53d8\u6210\u53ea\u8bfb<br \/>\n2 \u8fd9\u4e2a\u6b65\u9aa4\u53ef\u80fd\u5f88\u957f\uff0c\u56e0\u4e3a\u4f1a\u7b49\u5f85\u6240\u6709\u7684\u8868\u88ab\u5173\u95ed\uff0c\u5982\u679c\u7cfb\u7edf\u4e2d\u6709\u5927\u67e5\u8be2\uff0c\u4f1a\u4e00\u76f4\u7b49\u5f85&#8230;<br \/>\n3 \u4f1a\u963b\u585e\u7cfb\u7edf\u4e2d\u5df2\u7ecf\u5f00\u59cb\u7684\u4e8b\u52a1\u63d0\u4ea4\uff0c\u4ece\u800c\u4fdd\u8bc1binlog\u4e0d\u589e\u957f\uff0c\u901a\u8fc7show master status\u5c31\u53ef\u4ee5\u83b7\u53d6\u5f53\u524d\u7684binlog file\u548cbinglog pos<\/p>\n<h3>FLUSH TABLES WITH READ LOCK vs. set global read_only=1<\/h3>\n<p>lock_global_read_lock\u88ab\u4e24\u5904\u8c03\u7528\uff1areload_acl_and_cache \u548c fix_read_only\uff0cfix_read_only\u505a\u7684\u4e8b\u60c5\u548cFLUSH TABLES WITH READ LOCK\u57fa\u672c\u4e00\u6837\uff0clock_global_read_lock(\u540c\u6837\u7684\uff0cMDL_SHARED\u7c7b\u578b\u7684global read lock)&#8211;>close_cached_tables&#8211;>make_global_read_lock_block_commit,\u9664\u6b64\u4e4b\u5916\uff0c\u8bbe\u7f6e\u5168\u5c40\u53d8\u91cfread_only=1\uff0c\u7531\u4e8e\u5b83\u4eec\u5728global read lock\u4e0a\u52a0\u7684\u90fd\u662fMDL_SHARED\u9501\uff0c\u56e0\u6b64\u8fd9\u4e24\u4e2a\u547d\u4ee4\u662f\u517c\u5bb9\u7684\uff0c\u53ea\u662fset global read_only=1\u540e\uff0c\u5982\u679c\u6709DML\u4f1a\u62a5\u9519\uff1athe mysql server is running with the read-only option<\/p>\n<h3>\u4e00\u4e2a\u7ebf\u4e0a\u6848\u4f8b<\/h3>\n<p>\u7ebf\u4e0a\u51fa\u73b0\u4e86\u4e00\u4e2a\u95ee\u9898\uff0c\u6839\u636eshow processlist\u53d1\u73b0\uff1a<br \/>\n1. \u6240\u6709\u7684DML\u90fd\u65e0\u6cd5\u6267\u884c\uff0csession\u72b6\u6001\u4e3a\uff1aWaiting for global read lock<br \/>\n2. set global read_only=1\u963b\u585e\uff0c session\u72b6\u6001\u4e3a\uff1aWaiting for table flush<br \/>\n\u51fa\u73b01\u8bf4\u660eglobal read lock\u88ab\u67d0\u4e2asession\u83b7\u53d6\u4e86\u6216\u8005\u88ab\u67d0\u4e2asession\u7b49\u5f85\uff0c\u6bd4\u5982\u4ee5\u4e0b\u573a\u666f\uff1a<br \/>\n<div class=\"table-responsive\"><table  style=\"width:600px; \"  class=\"easy-table easy-table-default \" border=\"1\">\n<caption>global read lock test<\/caption>\n<thead>\r\n<tr><th  style=\"text-align:center\" >session 1<\/th>\n<th  style=\"text-align:center\" > session 2<\/th>\n<\/tr>\n<\/thead>\n<tbody>\r\n<tr><td  style=\"text-align:center\" >start transaction;<\/td>\n<td  style=\"text-align:center\" > start transaction;<\/td>\n<\/tr>\n\r\n<tr><td  style=\"text-align:center\" >select * from test.t1 for update;<\/td>\n<td  style=\"text-align:center\" ><\/td>\n<\/tr>\n\r\n<tr><td  style=\"text-align:center\" >[select not finished&#8230;]<\/td>\n<td  style=\"text-align:center\" >FLUSH TABLES WITH READ LOCK\/set global read_only=1<\/td>\n<\/tr>\n<\/tbody><\/table><\/div><br \/>\n\u8fd9\u65f6\u518d\u5f00\u4e00\u4e2asession\uff0c\u6267\u884c\u4efb\u4f55DML\u90fd\u4f1a\u51fa\u73b0\uff1aWaiting for global read lock\uff0c\u4f46\u662f\u6b64\u65f6session 2\u7684\u72b6\u6001\u4e5f\u662f\uff1aWaiting for global read lock\uff0c\u4e0e2\u4e2d\u73b0\u8c61\u4e0d\u4e00\u81f4\uff0c\u51fa\u73b02\u8bf4\u660eset global read_only=1\u7684\u7b2c\u4e00\u9636\u6bb5\u5df2\u7ecf\u901a\u8fc7\uff0c\u5373MDL_SHARED\u7c7b\u578b\u7684global read lock\u5df2\u7ecf\u88abgrant\uff0c\u8fd9\u6837\u53ef\u4ee5\u63a8\u6d4b\u51fa\u7cfb\u7edf\u4e2d\u5df2\u7ecf\u6709session\u83b7\u53d6\u4e86MDL_SHARED\u7c7b\u578b\u7684global read lock\uff0c\u800c\u8fd9\u53ea\u53ef\u80fd\u662fFLUSH TABLES WITH READ LOCK\u9020\u6210\u7684\uff0c\u56e0\u4e3a\u5982\u679c\u5176\u4ed6session\u662f\u4e5f\u662f\u901a\u8fc7set global read_only=1\u83b7\u53d6\u7684MDL_SHARED\u7c7b\u578b\u7684global read lock\uff0c\u90a3\u4e48DML\u6267\u884c\u5c31\u4f1a\u62a5\u9519\uff0c\u800c\u4e0d\u4f1a\u51fa\u73b01<\/p>\n<h3>\u6545\u969c\u603b\u7ed3<\/h3>\n<p>\u7cfb\u7edf\u4e2d\u7684global read lock\u88ab\u5176\u5b83\u7ebf\u7a0b\u83b7\u53d6\u4e86\uff08\u88ab\u52a0\u4e0a\u4e86S\u9501\uff09\uff0c\u56e0\u6b64DML\u5728grl\u4e0a\u9762\u52a0IX\u9501\u65f6\u4f1a\u51fa\u73b0Waiting for global read lock\uff0c\u800cset read_only\u5728grl\u4e0a\u52a0\u7684\u662fS\u9501\uff0c\u56e0\u6b64\u6b64\u65f6\u662f\u53ef\u4ee5grant\u7684\uff0c\u800c\u6b64\u65f6\u7cfb\u7edf\u4e2d\u5b58\u5728\u5927\u67e5\u8be2\uff0cset global read_only=1\u9700\u8981\u7b49\u5f85\u8868\u88ab\u5173\u95ed\uff0c\u56e0\u6b64\u72b6\u6001\u662f\uff1aWaiting for table flush<\/p>\n<p>\u56e0\u6b64\u539f\u56e0\u53ef\u4ee5\u786e\u5b9a\u4e86\uff1a\u7cfb\u7edf\u4e2d\u5b58\u5728flush tables with read lock\u4f46\u662f\u6ca1\u6709unlock tables<\/p>\n<h3>\u5c0f\u6280\u5de7<\/h3>\n<p>\u5728\u624b\u52a8\u505aFLUSH TABLES WITH READ LOCK\/set global read_only=1\u4e4b\u524d\uff0c\u5148\u6267\u884c\u4e00\u4e0bFLUSH TABLES\uff0c\u80fd\u591f\u5f88\u5927\u6982\u7387\u5730\u51cf\u5c0f\u7cfb\u7edf\u53d8\u6210\u53ea\u8bfb\u7684\u65f6\u95f4<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u672c\u5206\u6790\u57fa\u4e8e Percona Server 5.5.18 FLUSH TABLES WITH READ LOCK &hellip; <a href=\"http:\/\/www.gpfeng.com\/?p=222\" class=\"more-link\">\u7ee7\u7eed\u9605\u8bfb<span class=\"screen-reader-text\">FLUSH TABLES WITH READ LOCK<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[1],"tags":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3tPZp-3A","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/222"}],"collection":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=222"}],"version-history":[{"count":13,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/222\/revisions"}],"predecessor-version":[{"id":249,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/222\/revisions\/249"}],"wp:attachment":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=222"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=222"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}