{"id":629,"date":"2021-01-22T20:48:02","date_gmt":"2021-01-22T12:48:02","guid":{"rendered":"https:\/\/blog.espnlol.com\/?p=629"},"modified":"2022-04-20T18:20:26","modified_gmt":"2022-04-20T10:20:26","slug":"mysql%e4%ba%8b%e5%8a%a1%e9%94%81%e5%a4%84%e7%90%86","status":"publish","type":"post","link":"https:\/\/blog.espnlol.com\/?p=629","title":{"rendered":"mysql\u4e8b\u52a1\u9501\u5904\u7406"},"content":{"rendered":"\n<p>MySQL\u5728\u8fdb\u884calter table\u7b49DDL\u64cd\u4f5c\u65f6\uff0c\u6709\u65f6\u4f1a\u51fa\u73b0Waiting for table metadata lock\u7684\u7b49\u5f85\u573a\u666f\u3002\u800c\u4e14\uff0c\u4e00\u65e6alter table TableA\u7684\u64cd\u4f5c\u505c\u6ede\u5728Waiting for table metadata lock\u7684\u72b6\u6001\uff0c\u540e\u7eed\u5bf9TableA\u7684\u4efb\u4f55\u64cd\u4f5c\uff08\u5305\u62ec\u8bfb\uff09\u90fd\u65e0\u6cd5\u8fdb\u884c\uff0c\u56e0\u4e3a\u4ed6\u4eec\u4e5f\u4f1a\u5728Opening tables\u7684\u9636\u6bb5\u8fdb\u5165\u5230Waiting for table metadata lock\u7684\u9501\u7b49\u5f85\u961f\u5217\u3002\u5982\u679c\u662f\u4ea7\u54c1\u73af\u5883\u7684\u6838\u5fc3\u8868\u51fa\u73b0\u4e86\u8fd9\u6837\u7684\u9501\u7b49\u5f85\u961f\u5217\uff0c\u5c31\u4f1a\u9020\u6210\u707e\u96be\u6027\u7684\u540e\u679c\u3002<\/p>\n\n\n\n<p>\u9020\u6210alter table\u4ea7\u751fWaiting for table metadata lock\u7684\u539f\u56e0\u5176\u5b9e\u5f88\u7b80\u5355\uff0c\u4e00\u822c\u662f\u4ee5\u4e0b\u51e0\u4e2a\u7b80\u5355\u7684\u573a\u666f\uff1a<\/p>\n\n\n\n<p><strong>\u573a\u666f\u4e00\uff1a\u957f\u4e8b\u7269\u8fd0\u884c\uff0c\u963b\u585eDDL\uff0c\u7ee7\u800c\u963b\u585e\u6240\u6709\u540c\u8868\u7684\u540e\u7eed\u64cd\u4f5c<\/strong><\/p>\n\n\n\n<p>\u901a\u8fc7show processlist\u53ef\u4ee5\u770b\u5230TableA\u4e0a\u6709\u6b63\u5728\u8fdb\u884c\u7684\u64cd\u4f5c\uff08\u5305\u62ec\u8bfb\uff09\uff0c\u6b64\u65f6alter table\u8bed\u53e5\u65e0\u6cd5\u83b7\u53d6\u5230metadata \u72ec\u5360\u9501\uff0c\u4f1a\u8fdb\u884c\u7b49\u5f85\u3002<\/p>\n\n\n\n<p>\u8fd9\u662f\u6700\u57fa\u672c\u7684\u4e00\u79cd\u60c5\u5f62\uff0c\u8fd9\u4e2a\u548cmysql 5.6\u4e2d\u7684online ddl\u5e76\u4e0d\u51b2\u7a81\u3002\u4e00\u822calter table\u7684\u64cd\u4f5c\u8fc7\u7a0b\u4e2d\uff08\u89c1\u4e0b\u56fe\uff09\uff0c\u5728after create\u6b65\u9aa4\u4f1a\u83b7\u53d6metadata \u72ec\u5360\u9501\uff0c\u5f53\u8fdb\u884c\u5230altering table\u7684\u8fc7\u7a0b\u65f6\uff08\u901a\u5e38\u662f\u6700\u82b1\u65f6\u95f4\u7684\u6b65\u9aa4\uff09\uff0c\u5bf9\u8be5\u8868\u7684\u8bfb\u5199\u90fd\u53ef\u4ee5\u6b63\u5e38\u8fdb\u884c\uff0c\u8fd9\u5c31\u662fonline ddl\u7684\u8868\u73b0\uff0c\u5e76\u4e0d\u4f1a\u50cf\u4e4b\u524d\u5728\u6574\u4e2aalter table\u8fc7\u7a0b\u4e2d\u963b\u585e\u5199\u5165\u3002\uff08\u5f53\u7136\uff0c\u4e5f\u5e76\u4e0d\u662f\u6240\u6709\u7c7b\u578b\u7684alter\u64cd\u4f5c\u90fd\u80fdonline\u7684\uff0c\u5177\u4f53\u53ef\u4ee5\u53c2\u89c1\u5b98\u65b9\u624b\u518c\uff1a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-create-index-overview.html\">http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-create-index-overview.html<\/a>\uff09<br><strong>\u5904\u7406\u65b9\u6cd5\uff1a<\/strong>&nbsp;kill \u6389 DDL\u6240\u5728\u7684session.<\/p>\n\n\n\n<p><strong>\u573a\u666f\u4e8c\uff1a\u672a\u63d0\u4ea4\u4e8b\u7269\uff0c\u963b\u585eDDL\uff0c\u7ee7\u800c\u963b\u585e\u6240\u6709\u540c\u8868\u7684\u540e\u7eed\u64cd\u4f5c<\/strong><\/p>\n\n\n\n<p>\u901a\u8fc7show processlist\u770b\u4e0d\u5230TableA\u4e0a\u6709\u4efb\u4f55\u64cd\u4f5c\uff0c\u4f46\u5b9e\u9645\u4e0a\u5b58\u5728\u6709\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1\uff0c\u53ef\u4ee5\u5728&nbsp;<strong>information_schema.innodb_trx<\/strong>\u4e2d\u67e5\u770b\u5230\u3002\u5728\u4e8b\u52a1\u6ca1\u6709\u5b8c\u6210\u4e4b\u524d\uff0cTableA\u4e0a\u7684\u9501\u4e0d\u4f1a\u91ca\u653e\uff0calter table\u540c\u6837\u83b7\u53d6\u4e0d\u5230metadata\u7684\u72ec\u5360\u9501\u3002<\/p>\n\n\n\n<p>\u5904\u7406\u65b9\u6cd5\uff1a\u901a\u8fc7 select * from&nbsp;<strong>information_schema.innodb_trx<\/strong>\\G, \u627e\u5230\u672a\u63d0\u4ea4\u4e8b\u7269\u7684sid, \u7136\u540e kill \u6389\uff0c\u8ba9\u5176\u56de\u6eda\u3002<\/p>\n\n\n\n<p><strong>\u573a\u666f\u4e09\uff1a<\/strong><\/p>\n\n\n\n<p>\u901a\u8fc7show processlist\u770b\u4e0d\u5230TableA\u4e0a\u6709\u4efb\u4f55\u64cd\u4f5c\uff0c\u5728information_schema.innodb_trx\u4e2d\u4e5f\u6ca1\u6709\u4efb\u4f55\u8fdb\u884c\u4e2d\u7684\u4e8b\u52a1\u3002\u8fd9\u5f88\u53ef\u80fd\u662f\u56e0\u4e3a\u5728\u4e00\u4e2a\u663e\u5f0f\u7684\u4e8b\u52a1\u4e2d\uff0c\u5bf9TableA\u8fdb\u884c\u4e86\u4e00\u4e2a\u5931\u8d25\u7684\u64cd\u4f5c\uff08\u6bd4\u5982\u67e5\u8be2\u4e86\u4e00\u4e2a\u4e0d\u5b58\u5728\u7684\u5b57\u6bb5\uff09\uff0c\u8fd9\u65f6\u4e8b\u52a1\u6ca1\u6709\u5f00\u59cb\uff0c\u4f46\u662f\u5931\u8d25\u8bed\u53e5\u83b7\u53d6\u5230\u7684\u9501\u4f9d\u7136\u6709\u6548\uff0c\u6ca1\u6709\u91ca\u653e\u3002\u4ece<strong>performance_schema.events_statements_current<\/strong>\u8868\u4e2d\u53ef\u4ee5\u67e5\u5230\u5931\u8d25\u7684\u8bed\u53e5\u3002<\/p>\n\n\n\n<p>\u5b98\u65b9\u624b\u518c\u4e0a\u5bf9\u6b64\u7684\u8bf4\u660e\u5982\u4e0b\uff1a<\/p>\n\n\n\n<p>If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.<\/p>\n\n\n\n<p>\u4e5f\u5c31\u662f\u8bf4\u9664\u4e86\u8bed\u6cd5\u9519\u8bef\uff0c\u5176\u4ed6\u9519\u8bef\u8bed\u53e5\u83b7\u53d6\u5230\u7684\u9501\u5728\u8fd9\u4e2a\u4e8b\u52a1\u63d0\u4ea4\u6216\u56de\u6eda\u4e4b\u524d\uff0c\u4ecd\u7136\u4e0d\u4f1a\u91ca\u653e\u6389\u3002because the failed statement is written to the binary log and the locks protect log consistency \u4f46\u662f\u89e3\u91ca\u8fd9\u4e00\u884c\u4e3a\u7684\u539f\u56e0\u5f88\u96be\u7406\u89e3\uff0c\u56e0\u4e3a\u9519\u8bef\u7684\u8bed\u53e5\u6839\u672c\u4e0d\u4f1a\u88ab\u8bb0\u5f55\u5230\u4e8c\u8fdb\u5236\u65e5\u5fd7\u3002<\/p>\n\n\n\n<p><strong>\u5904\u7406\u65b9\u6cd5\uff1a<\/strong>\u901a\u8fc7<strong>performance_schema.events_statements_current<\/strong>\u627e\u5230\u5176sid, kill \u6389\u8be5session. \u4e5f\u53ef\u4ee5 kill \u6389DDL\u6240\u5728\u7684session.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL\u5728\u8fdb\u884calter table\u7b49DDL\u64cd\u4f5c\u65f6\uff0c\u6709\u65f6\u4f1a\u51fa\u73b0Waiting  &hellip; <a href=\"https:\/\/blog.espnlol.com\/?p=629\">\u7ee7\u7eed\u9605\u8bfb <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[],"class_list":["post-629","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts\/629","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=629"}],"version-history":[{"count":1,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts\/629\/revisions"}],"predecessor-version":[{"id":630,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts\/629\/revisions\/630"}],"wp:attachment":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=629"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=629"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=629"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}