{"id":10006500,"date":"2022-09-16T11:31:16","date_gmt":"2022-09-16T03:31:16","guid":{"rendered":"https:\/\/ityet.com\/?p=10006500"},"modified":"2022-09-16T11:31:16","modified_gmt":"2022-09-16T03:31:16","slug":"%e4%b8%80%e6%ad%a5%e6%ad%a5%e6%90%9e%e6%87%82mysql%e5%85%83%e6%95%b0%e6%8d%ae%e9%94%81%ef%bc%88mdl%ef%bc%89","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10006500","title":{"rendered":"\u4e00\u6b65\u6b65\u641e\u61c2MySQL\u5143\u6570\u636e\u9501\uff08MDL\uff09"},"content":{"rendered":"<p>\u67d0\u65e5\uff0c\u8def\u4e0a\u6536\u5230\u7528\u6237\u54a8\u8be2\uff0c\u4e3a\u4e86\u6e05\u9664\u7a7a\u95f4\uff0c\u60f3\u5220\u9664\u67d0200\u591aG\u5927\u8868\u6570\u636e\uff0c\u4e14\u5df2\u7ecf\u786e\u8ba4\u6b64\u8868\u4e0d\u518d\u6709\u4e1a\u52a1\u8bbf\u95ee\uff0c\u4e8e\u662f\u6267\u884c\u4e86\u4e00\u6761\u547d\u4ee4\u2018delete from bigtable\u2019\uff0c\u4f46\u597d\u957f\u65f6\u95f4\u4e5f\u6ca1\u5220\u5b8c\uff0c\u7ecf\u8fc7\u54a8\u8be2\u540e\uff0c\u83b7\u77e5drop table\u5220\u9664\u8868\u901f\u5ea6\u5feb\uff0c\u800c\u4e14\u80fd\u5f7b\u5e95\u91ca\u653e\u7a7a\u95f4\uff0c\u4e8e\u662f\u53c8\u5728\u53e6\u5916\u4e00\u4e2asession\u4e2d\u6267\u884c\u4e86\u2018drop table bigtable\u2019\u547d\u4ee4\uff0c\u4f46\u662f\u8fd9\u4e2a\u547d\u4ee4\u5e76\u6ca1\u6709\u5feb\u901f\u8fd4\u56de\u7ed3\u679c\uff0c\u5149\u6807\u4e00\u76f4hang\u5728\u539f\u5730\u4e0d\u52a8\u3002\u6700\u540e\u627e\u6211\u4eec\u534f\u52a9\uff0c\u5728\u767b\u5f55\u6570\u636e\u5e93\u6267\u884c\u2018show processlist\u2019\u540e\u53d1\u73b0drop\u8bed\u53e5\u7684\u72b6\u6001\u662f\u2018waiting for table metadata lock\u2019\uff0c\u800c\u4e4b\u524d\u6267\u884c\u7684\u53e6\u5916\u4e00\u4e2adelete\u8bed\u53e5\u4f9d\u65e7\u80fd\u770b\u5230\uff0c\u72b6\u6001\u4e3a\u2018updating\u2019\uff0c\u622a\u56fe\u5982\u4e0b\uff1a<br \/><img decoding=\"async\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVc2tnA\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<p>\u5230\u5e95\u4ec0\u4e48\u662fmetadata lock\uff1f\u8fd9\u4e2a\u9501\u7b49\u5f85\u662f\u5982\u4f55\u4ea7\u751f\u7684\uff1f\u4f1a\u5e26\u6765\u4ec0\u4e48\u5f71\u54cd\uff1f\u6700\u540e\u53c8\u5982\u4f55\u6765\u89e3\u51b3\uff1f\u4eca\u5929\u6211\u4eec\u6311<strong>6\u4e2a\u5e38\u89c1\u95ee\u9898<\/strong>\u7ed9\u5927\u5bb6\u89e3\u7b54\u4e00\u4e0b\u3002<\/p>\n<p><strong>\u4e00\u3001\u4ec0\u4e48\u662fmetadata lock\uff1f<\/strong><\/p>\n<p>\u5728MySQL5.5.3\u4e4b\u524d\uff0c\u6709\u4e00\u4e2a\u8457\u540d\u7684bug#989\uff0c\u5927\u81f4\u5982\u4e0b:<\/p>\n<pre><code> session1:  \n BEGIN;\n INSERT INTO t ... ;\n COMMIT;  \n\n session2:  \n DROP TABLE t;<\/code><\/pre>\n<p>\u7136\u800c\u4e0a\u9762\u7684\u64cd\u4f5c\u6d41\u7a0b\u5728binlog\u8bb0\u5f55\u7684\u987a\u5e8f\u662f<\/p>\n<pre><code> DROP TABLE t; \n BEGIN;  \n INSERT INTO t ... ; \n COMMIT;<\/code><\/pre>\n<p>\u5f88\u663e\u7136\u5907\u5e93\u6267\u884cbinlog\u65f6\u4f1a\u5148\u5220\u9664\u8868t\uff0c\u7136\u540e\u6267\u884cinsert \u4f1a\u62a51032 error\uff0c\u5bfc\u81f4\u590d\u5236\u4e2d\u65ad\u3002\u4e3a\u4e86\u89e3\u51b3\u8be5bug,MySQL \u57285.5.3\u5f15\u5165\u4e86MDL\u9501\uff08metadata lock\uff09\uff0c\u6765\u4fdd\u62a4\u8868\u7684\u5143\u6570\u636e\u4fe1\u606f\uff0c\u7528\u4e8e\u89e3\u51b3\u6216\u8005\u4fdd\u8bc1DDL\u64cd\u4f5c\u4e0eDML\u64cd\u4f5c\u4e4b\u95f4\u7684\u4e00\u81f4\u6027\u3002<\/p>\n<p>\u518d\u4e3e\u4e00\u4e2a\u7b80\u5355\u7684\u4f8b\u5b50\uff0c\u5982\u679c\u4f60\u5728\u67e5\u8be2\u4e00\u4e2a\u8868\u7684\u8fc7\u7a0b\u4e2d\uff0c\u53e6\u5916\u4e00\u4e2asession\u5bf9\u8be5\u8868\u5220\u9664\u4e86\u4e00\u4e2a\u5217\uff0c\u90a3\u524d\u9762\u7684\u67e5\u8be2\u5230\u5e95\u8be5\u663e\u793a\u4ec0\u4e48\u5462\uff1f\u5982\u679c\u5728RR\u9694\u79bb\u7ea7\u522b\u4e0b\uff0c\u4e8b\u7269\u4e2d\u518d\u6b21\u6267\u884c\u76f8\u540c\u7684\u8bed\u53e5\u8fd8\u4f1a\u548c\u4e4b\u524d\u7ed3\u679c\u4e00\u81f4\u5417\uff1f\u4e3a\u4e86\u9632\u6b62\u8fd9\u79cd\u60c5\u51b5\uff0c\u8868\u67e5\u8be2\u5f00\u59cbMySQL\u4f1a\u5728\u8868\u4e0a\u52a0\u4e00\u4e2a\u9501\uff0c\u6765\u9632\u6b62\u88ab\u522b\u7684session\u4fee\u6539\u4e86\u8868\u5b9a\u4e49\uff0c\u8fd9\u4e2a\u9501\u5c31\u53eb\u2018metadata lock\u2019\uff0c\u7b80\u79f0MDL\uff0c\u7ffb\u8bd1\u6210\u4e2d\u6587\u4e5f\u53eb\u2018\u5143\u6570\u636e\u9501\u2019\u3002<\/p>\n<p><strong>\u4e8c\u3001MDL\u548c\u884c\u9501\u6709\u4ec0\u4e48\u533a\u522b\uff1f<\/strong><\/p>\n<p><strong>metadata lock\u662f\u8868\u7ea7\u9501\uff0c\u662f\u5728server\u5c42\u52a0\u7684\uff0c\u9002\u7528\u4e8e\u6240\u6709\u5b58\u50a8\u5f15\u64ce\u3002<\/strong>\u6240\u6709\u7684dml\u64cd\u4f5c\u90fd\u4f1a\u5728\u8868\u4e0a\u52a0\u4e00\u4e2ametadata\u8bfb\u9501\uff1b\u6240\u6709\u7684ddl\u64cd\u4f5c\u90fd\u4f1a\u5728\u8868\u4e0a\u52a0\u4e00\u4e2ametadata\u5199\u9501\u3002\u8bfb\u9501\u548c\u5199\u9501\u7684\u963b\u585e\u5173\u7cfb\u5982\u4e0b\uff1a<\/p>\n<p>\u8bfb\u9501\u548c\u5199\u9501\u4e4b\u95f4\u76f8\u4e92\u963b\u585e\uff0c\u5373\u540c\u4e00\u4e2a\u8868\u4e0a\u7684dml\u548cddl\u4e4b\u95f4\u4e92\u76f8\u963b\u585e\u3002<br \/>\u5199\u9501\u548c\u5199\u9501\u4e4b\u95f4\u4e92\u76f8\u963b\u585e\uff0c\u5373\u4e24\u4e2asession\u4e0d\u80fd\u5bf9\u8868\u540c\u65f6\u505a\u8868\u5b9a\u4e49\u53d8\u66f4\uff0c\u9700\u8981\u4e32\u884c\u64cd\u4f5c\u3002<br \/>\u8bfb\u9501\u548c\u8bfb\u9501\u4e4b\u95f4\u4e0d\u4f1a\u4ea7\u751f\u963b\u585e\u3002\u4e5f\u5c31\u662f\u589e\u5220\u6539\u67e5\u4e0d\u4f1a\u56e0\u4e3ametadata lock\u4ea7\u751f\u963b\u585e\uff0c\u53ef\u4ee5\u5e76\u53d1\u6267\u884c\uff0c\u65e5\u5e38\u5de5\u4f5c\u4e2d\u5927\u5bb6\u770b\u5230\u7684dml\u4e4b\u95f4\u7684\u9501\u7b49\u5f85\u662finnodb\u884c\u9501\u5f15\u8d77\u7684\uff0c\u548cmetadata lock\u65e0\u5173\u3002<\/p>\n<p>\u719f\u6089innodb\u884c\u9501\u7684\u540c\u5b66\u8fd9\u91cc\u53ef\u80fd\u6709\u70b9\u56f0\u60d1\uff0c\u56e0\u4e3a\u884c\u9501\u5206\u7c7b\u548cmetadata lock\u5f88\u7c7b\u4f3c\uff0c\u4e5f\u4e3b\u8981\u5206\u4e3a\u8bfb\u9501\u548c\u5199\u9501\uff0c\u6216\u8005\u53eb\u5171\u4eab\u9501\u548c\u6392\u4ed6\u9501\uff0c\u8bfb\u5199\u9501\u4e4b\u95f4\u963b\u585e\u5173\u7cfb\u4e5f\u4e00\u81f4\u3002\u4e8c\u8005\u6700\u91cd\u8981\u7684\u533a\u522b\u4e00\u4e2a\u662f\u8868\u9501\uff0c\u4e00\u4e2a\u662f\u884c\u9501\uff0c\u4e14\u884c\u9501\u4e2d\u7684\u8bfb\u5199\u64cd\u4f5c\u5bf9\u5e94\u5728metadata lock\u4e2d\u90fd\u5c5e\u4e8e\u8bfb\u9501\u3002<\/p>\n<p>\u5927\u5bb6\u4e5f\u8bb8\u4f1a\u5947\u602a\uff0c\u4ee5\u524d\u542c\u8bf4\u666e\u901a\u67e5\u8be2\u4e0d\u52a0\u9501\u7684\uff0c\u600e\u4e48\u8fd9\u91cc\u53c8\u8bf4\u8981\u52a0\u8868\u9501\uff0c\u6211\u4eec\u505a\u4e00\u4e2a\u7b80\u5355\u6d4b\u8bd5\uff1a<\/p>\n<p>session1\uff1a\u67e5\u8be2\u524d\uff0c\u5148\u770b\u4e00\u4e0bmetadata_locks\u8868\uff0c\u8fd9\u4e2a\u8868\u4f4d\u4e8eperformance_schema\u4e0b\uff0c\u8bb0\u5f55\u4e86metadata lock\u7684\u52a0\u9501\u4fe1\u606f\u3002<\/p>\n<pre><code>mysql&gt; select * from performance_schema.metadata_locks ;\n+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+\n| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |\n+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+\n| TABLE       | performance_schema | metadata_locks | NULL        |       139776223308432 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6014 |              54 |             12 |\n+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+\n1 row in set (0.00 sec)\n<\/code><\/pre>\n<p>session2\uff1a\u6267\u884c\u7b80\u5355\u67e5\u8be2\uff0c\u4e3a\u4e86\u8ba9\u8868\u5904\u4e8e\u6267\u884c\u72b6\u6001\uff0c\u8fd9\u91cc\u4f7f\u7528\u4e86sleep\u51fd\u6570\u3002<\/p>\n<pre><code>mysql&gt; select sleep(10) from t1;\n+-----------+\n| sleep(10) |\n+-----------+\n|         0 |\n|         0 |\n|         0 |\n+-----------+\n3 rows in set (30.00 sec)<\/code><\/pre>\n<p>session1\uff1a<\/p>\n<pre><code>mysql&gt; select * from performance_schema.metadata_locks ;\n+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+\n| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |\n+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+\n| TABLE       | db1                | t1             | NULL        |       139776154308336 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6014 |              53 |             22 |\n| TABLE       | performance_schema | metadata_locks | NULL        |       139776223308432 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6014 |              54 |             13 |\n+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n<p>\u6b64\u65f6\u518d\u6b21\u67e5\u770bmetadata_lock\u8868\uff0c\u53d1\u73b0\u591a\u4e86\u4e00\u6761t1\u7684\u52a0\u9501\u8bb0\u5f55\uff0c\u52a0\u9501\u7c7b\u578b\u4e3aSHARED_READ,\u4e14\u72b6\u6001\u662f\u5df2\u6388\u4e88\uff08GRANTED)\u3002\u5927\u5bb6\u901a\u5e38\u7406\u89e3\u7684\u67e5\u8be2\u4e0d\u52a0\u9501\uff0c\u662f\u6307\u4e0d\u5728\u8868\u4e0a\u52a0innodb\u884c\u9501\u3002<\/p>\n<p>\u5982\u679c\u5728\u6267\u884csleep\u671f\u95f4\uff0c\u53e6\u5916\u4e00\u4e2asession\u6267\u884c\u4e86\u4e00\u4e2a\u52a0\u5b57\u6bb5\u64cd\u4f5c\uff0c\u6b64\u65f6\u5c31\u4f1a\u4ea7\u751fmetadata lock\u9501\u7b49\u5f85\uff1a<\/p>\n<p>session2:<\/p>\n<p>mysql&gt; select sleep(10) from t1;<\/p>\n<p>\u6267\u884c\u4e2d&#8230;&#8230;<\/p>\n<p>session3\uff1a<\/p>\n<p>mysql&gt; alter table t1 add col1 int;<\/p>\n<p>\u963b\u585e\u4e2d&#8230;&#8230;<\/p>\n<p>session1:<\/p>\n<pre><code>mysql&gt; show processlist;\n+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+\n| Id | User            | Host      | db   | Command | Time   | State                           | Info                        |\n+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+\n|  4 | event_scheduler | localhost | NULL | Daemon  | 861577 | Waiting on empty queue          | NULL                        |\n| 18 | root            | localhost | db1  | Sleep   |     50 |                                 | NULL                        |\n| 19 | root            | localhost | NULL | Query   |      0 | starting                        | show processlist            |\n| 20 | root            | localhost | db1  | Query   |     11 | Waiting for table metadata lock | alter table t1 add col1 int |\n+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+\n4 rows in set (0.00 sec)<\/code><\/pre>\n<p>\u663e\u7136\uff0cid\u4e3a20\u7684\u7ebf\u7a0b\u8fd8\u672a\u6267\u884calter\u64cd\u4f5c\uff0c\u72b6\u6001\u4e3a\u2018Waiting for table metadata lock\u2019\uff0c\u4e5f\u5c31\u662f\u5728\u7b49\u5f85session2\u7684sleep\u64cd\u4f5c\u5b8c\u6210\u3002<\/p>\n<p><strong>\u4e09\u3001MDL\u4e3a\u4ec0\u4e48\u4f1a\u9020\u6210\u7cfb\u7edf\u5d29\u6e83\uff1f<\/strong><\/p>\n<p>\u4e3e\u4e00\u4e2a\u7b80\u5355\u4f8b\u5b50:<\/p>\n<ul>\n<li>session1\u542f\u52a8\u4e00\u4e2a\u4e8b\u52a1\uff0c\u5bf9\u8868t1\u6267\u884c\u4e00\u4e2a\u7b80\u5355\u7684\u67e5\u8be2\uff1b<\/li>\n<li>session2\u5bf9t1\u52a0\u4e00\u4e2a\u5b57\u6bb5\uff1b<\/li>\n<li>session3\u6765\u5bf9t1\u505a\u4e00\u4e2a\u67e5\u8be2\uff1b<\/li>\n<li>session4\u6765\u5bf9t1\u505a\u4e00\u4e2aupdate;<\/li>\n<li>\u5404\u4e2asession\u4e32\u884c\u64cd\u4f5c\u3002<\/li>\n<\/ul>\n<p>session1\uff1a<\/p>\n<pre><code>mysql&gt; begin;\nQuery OK, 0 rows affected (0.00 sec)\n\n\nmysql&gt; select * from t1 where id=1;\n+----+------+------+-------+\n| id | name | age  | birth |\n+----+------+------+-------+\n|  1 | aa   |   10 | NULL  |\n+----+------+------+-------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<p>session2\uff1a<\/p>\n<p>mysql&gt; alter table t1 add col1 int;<\/p>\n<p>\u963b\u585e\u4e2d&#8230;<\/p>\n<p>session3:<\/p>\n<p>mysql&gt; select sleep(10) from t1 ;<\/p>\n<p>\u963b\u585e\u4e2d&#8230;<\/p>\n<p>session4:<\/p>\n<p>mysql&gt; update t1 set name=&#8217;aaaa&#8217; where id=2;<\/p>\n<p>\u963b\u585e\u4e2d&#8230;<\/p>\n<p>\u4e5f\u5c31\u662f\u7531\u4e8esession1\u7684\u4e00\u4e2a\u4e8b\u52a1\u6ca1\u6709\u63d0\u4ea4\uff0c\u5bfc\u81f4session2\u7684ddl\u64cd\u4f5c\u88ab\u963b\u585e\uff0csession3\u548csession4\u672c\u8eab\u4e0d\u4f1a\u88absession1\u963b\u585e\uff0c\u4f46\u7531\u4e8e\u5728\u9501\u961f\u5217\u4e2d\uff0csession2\u6392\u961f\u66f4\u65e9\uff0c\u5b83\u51c6\u5907\u52a0\u7684\u662fmetadata lock\u5199\u9501\uff0c\u963b\u585e\u4e86session3\u548csession4\u7684\u8bfb\u9501\u3002\u5982\u679ct1\u662f\u4e00\u4e2a\u6267\u884c\u9891\u7e41\u7684\u8868\uff0cshow processlist\u4f1a\u53d1\u73b0\u5927\u91cf\u2018waiting for table metadata lock\u2019\u7684\u7ebf\u7a0b\uff0c\u6570\u636e\u5e93\u8fde\u63a5\u5f88\u5feb\u5c31\u4f1a\u6d88\u8017\u5b8c\uff0c\u5bfc\u81f4\u4e1a\u52a1\u7cfb\u7edf\u65e0\u6cd5\u6b63\u5e38\u54cd\u5e94\u3002<\/p>\n<p>\u6b64\u65f6\u5982\u679csession1\u63d0\u4ea4\uff0c\u662fsession2\u7684alter\u8bed\u53e5\u5148\u6267\u884c\u8fd8\u662fsession3\u548csession4\u5148\u6267\u884c\u5462\uff1f\u4e4b\u524d\u4e00\u76f4\u4ee5\u4e3a\u5148\u5230\u7684\u5148\u6267\u884c\uff0c\u5f53\u7136\u662fsession2\u5148\u6267\u884c\uff0c\u4f46\u7ecf\u8fc7\u6d4b\u8bd5\uff0c\u57285.7\u4e2d\uff0csession3\u548csession4\u5148\u6267\u884c\uff0csession2\u6700\u540e\u6267\u884c\uff0c\u4e5f\u5c31\u4f1a\u51fa\u73b0alter\u957f\u65f6\u95f4\u65e0\u6cd5\u6267\u884c\u7684\u60c5\u51b5\uff1b\u800c\u57288.0\u4e2d\uff0csession2\u5148\u6267\u884c\uff0csession3\u548csession4\u540e\u6267\u884c\uff0c\u7531\u4e8e5.6\u4ee5\u540eddl\u662fonline\u7684\uff0csession2\u5e76\u4e0d\u4f1a\u963b\u585esession3\u548csession4\uff0c\u611f\u89c9\u8fd9\u6837\u624d\u662f\u5408\u7406\u7684\uff0calter\u4e0d\u4f1a\u88ab\u2018\u997f\u6b7b\u2019\u3002<\/p>\n<p><strong>\u56db\u3001MDL\u7684\u751f\u547d\u5468\u671f\u6709\u591a\u957f\uff1f<\/strong><\/p>\n<p>\u4e8b\u52a1\uff01\u4e8b\u52a1\uff01\u4e8b\u52a1\uff01 \u91cd\u8981\u7684\u4e8b\u60c5\u8bf4\u4e09\u904d\uff0c\u8868\u4e0a\u7684metadata lock\u7684\u751f\u547d\u5468\u671f\u4ece\u4e8b\u52a1\u4e2d\u7684\u7b2c\u4e00\u6761\u6d89\u53ca\u81ea\u8eab\u7684\u8bed\u53e5\u5f00\u59cb\uff0c\u5230\u6574\u4e2a\u4e8b\u52a1\u7ed3\u675f\u800c\u7ed3\u675f\u3002\u800c5.5\u4e4b\u524d\u662f\u57fa\u4e8e\u8bed\u53e5\u7684\uff0c\u4e8b\u52a1\u4e2d\u6267\u884c\u5b8c\u8bed\u53e5\u5c31\u91ca\u653e\uff0c\u5982\u679c\u6b64\u65f6\u53e6\u5916\u4e00\u4e2asession\u5bf9\u8868\u505a\u4e86\u4e00\u4e2a\u5220\u5b57\u6bb5\u64cd\u4f5c\uff0c\u90a3\u4e48\u5c31\u4f1a\u9020\u6210\u4e24\u4e2a\u95ee\u9898\uff1a<\/p>\n<ul>\n<li>ddl\u64cd\u4f5c\u5982\u679c\u5148\u4e8e\u4e8b\u52a1\u5b8c\u6210\uff0c\u90a3\u4e48binlog\u4e2dddl\u5c31\u4f1a\u6392\u5728\u4e8b\u52a1\u4e4b\u524d\uff0c\u660e\u663e\u548c\u903b\u8f91\u4e0d\u7b26\uff0c\u89e6\u53d1\u4e86\u672c\u6587\u5f00\u59cb\u63d0\u5230\u7684bug\u3002<\/li>\n<li>\u5982\u679c\u662fRR\u9694\u79bb\u7ea7\u522b\uff0c\u90a3\u4e48\u4e8b\u52a1\u4e2d\u6b64\u8868\u7b2c\u4e8c\u6b21\u6267\u884c\u5c06\u65e0\u6cd5\u8fd4\u56de\u540c\u6837\u7684\u7ed3\u679c\uff0c\u65e0\u6cd5\u6ee1\u8db3\u53ef\u91cd\u590d\u8bfb\u7684\u8981\u6c42\u3002<\/li>\n<\/ul>\n<p>\u6240\u4ee5\uff0c\u5982\u679c\u8981\u964d\u4f4emetadata lock\u7684\u9501\u7b49\u5f85\u65f6\u95f4\uff0c\u6700\u597d\u8981\u53ca\u65f6\u63d0\u4ea4\u4e8b\u52a1\uff0c\u540c\u65f6\u5c3d\u91cf\u907f\u514d\u5927\u4e8b\u52a1\u3002<\/p>\n<p>\u90a3\u4e48\u5982\u679c\u53d1\u751fmetadata lock\u9501\u7b49\u5f85\uff0c\u7b49\u5f85\u9501\u7684session\u4f1a\u7b49\u5f85\u591a\u957f\u65f6\u95f4\u5462\uff1f\u5927\u5bb6\u90fd\u77e5\u9053MySQL\u91cc\u9762\u884c\u9501\u7b49\u5f85\u6709\u4e2a\u8d85\u65f6\u65f6\u95f4\uff08\u53c2\u6570innodb_lock_wait_timeout\uff09\uff0c\u9ed8\u8ba450s\u3002metadata lock\u4e5f\u6709\u7c7b\u4f3c\u53c2\u6570\u63a7\u5236\uff1a<\/p>\n<pre><code>mysql&gt; show variables like 'lock_wait_timeout'      ;\n+-------------------+----------+\n| Variable_name     | Value    |\n+-------------------+----------+\n| lock_wait_timeout | 31536000 |\n+-------------------+----------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<p>\u8fd9\u4e48\u957f\u7684\u6570\u5b57\uff0c\u63b0\u7740\u6307\u5934\u7b97\u4e86\u534a\u5929\uff0c\u5c45\u7136\u771f\u7684\u662f&#8230;&#8230;\u4e00\u5e74\uff0c\u73af\u6e38\u4e16\u754c\u4e00\u5708\u56de\u6765\u8fd8\u5f97\u63a5\u7740\u7b49!!!<\/p>\n<p>\u5f53\u7136\uff0c\u751f\u4ea7\u73af\u5883\u4e2d\uff0c\u6211\u4eec\u5f88\u5c11\u4f1a\u7b49\u5f85metadata lock\u8d85\u65f6\uff0c\u66f4\u591a\u7684\u662f\u8981\u60f3\u529e\u6cd5\u628a\u4ea7\u751fmetadata lock\u7684\u6e90\u5934\u627e\u5230\uff0c\u5feb\u901f\u63d0\u4ea4\u6216\u8005\u56de\u6eda\uff0c\u6216\u8005\u60f3\u529e\u6cd5kill\u6389\u3002\u90a3\u4e48\u5982\u4f55\u627e\u5230\u963b\u585e\u7684\u6e90\u5934\u5462\uff1f<\/p>\n<p><strong>\u4e94\u3001\u5982\u4f55\u5feb\u901f\u627e\u5230\u963b\u585e\u6e90\u5934\uff1f<\/strong><\/p>\n<p><strong>\u5feb\u901f\u89e3\u51b3\u95ee\u9898\u6c38\u8fdc\u662f\u7b2c\u4e00\u4f4d\u7684<\/strong>\uff0c\u4e00\u65e6\u51fa\u73b0\u957f\u65f6\u95f4\u7684metadata lock\uff0c\u5c24\u5176\u662f\u5728\u8bbf\u95ee\u9891\u7e41\u7684\u4e1a\u52a1\u8868\u4e0a\u4ea7\u751f\uff0c\u901a\u5e38\u4f1a\u5bfc\u81f4\u8868\u65e0\u6cd5\u8bbf\u95ee\uff0c\u8bfb\u5199\u5168\u88ab\u963b\u585e\uff0c\u6b64\u65f6\u627e\u5230\u963b\u585e\u6e90\u5934\u662f\u7b2c\u4e00\u4f4d\u7684\u3002\u8fd9\u91cc\u6700\u91cd\u8981\u7684\u8868\u5c31\u662f\u524d\u9762\u63d0\u5230\u8fc7\u7684<br \/>performance_schema.metadata_locks\u8868\u3002<\/p>\n<p>metadata_locks\u662f5.7\u4e2d\u88ab\u5f15\u5165\uff0c\u8bb0\u5f55\u4e86metadata lock\u7684\u76f8\u5173\u4fe1\u606f\uff0c\u5305\u62ec\u6301\u6709\u5bf9\u8c61\u3001\u7c7b\u578b\u3001\u72b6\u6001\u7b49\u4fe1\u606f\u3002\u4f465.7\u9ed8\u8ba4\u8bbe\u7f6e\u662f\u5173\u95ed\u7684\uff088.0\u9ed8\u8ba4\u6253\u5f00\uff09\uff0c\u9700\u8981\u901a\u8fc7\u4e0b\u9762\u547d\u4ee4\u6253\u5f00\u8bbe\u7f6e\uff1a<\/p>\n<pre><code>UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'wait\/lock\/metadata\/sql\/mdl';<\/code><\/pre>\n<p>\u5982\u679c\u8981\u6c38\u4e45\u751f\u6548\uff0c\u9700\u8981\u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u52a0\u5165\u5982\u4e0b\u5185\u5bb9\uff1a<\/p>\n<pre><code>[mysqld]\nperformance-schema-instrument='wait\/lock\/metadata\/sql\/mdl=ON'<\/code><\/pre>\n<p>\u5355\u7eaf\u67e5\u8be2\u8fd9\u4e2a\u8868\u65e0\u6cd5\u5f97\u51fa\u5177\u4f53\u7684\u963b\u585e\u5173\u7cfb\uff0c\u4e5f\u65e0\u6cd5\u5f97\u77e5\u4ec0\u4e48\u8bed\u53e5\u9020\u6210\u7684\u963b\u585e\uff0c\u8fd9\u91cc\u8981\u5173\u8054\u53e6\u5916\u4e24\u4e2a\u8868performance_schema.thread\u548c<br \/>performance_schema.events_statements_history,thread\u8868\u53ef\u4ee5\u5c06\u7ebf\u7a0bid\u548cshow processlist\u4e2did\u5173\u8054\uff0cevents_statements_history\u8868\u53ef\u4ee5\u5f97\u5230\u4e8b\u52a1\u7684\u5386\u53f2sql\uff0c\u5173\u8054\u540e\u7684\u5b8c\u6574sql\u5982\u4e0b\uff1a<\/p>\n<pre><code>SELECT\n    locked_schema,\n    locked_table,\n    locked_type,\n    waiting_processlist_id,\n    waiting_age,\n    waiting_query,\n    waiting_state,\n    blocking_processlist_id,\n    blocking_age,\n    substring_index(sql_text,&quot;transaction_begin;&quot; ,-1) AS blocking_query,\n    sql_kill_blocking_connection\nFROM\n    (\n        SELECT\n            b.OWNER_THREAD_ID AS granted_thread_id,\n            a.OBJECT_SCHEMA AS locked_schema,\n            a.OBJECT_NAME AS locked_table,\n            &quot;Metadata Lock&quot; AS locked_type,\n            c.PROCESSLIST_ID AS waiting_processlist_id,\n            c.PROCESSLIST_TIME AS waiting_age,\n            c.PROCESSLIST_INFO AS waiting_query,\n            c.PROCESSLIST_STATE AS waiting_state,\n            d.PROCESSLIST_ID AS blocking_processlist_id,\n            d.PROCESSLIST_TIME AS blocking_age,\n            d.PROCESSLIST_INFO AS blocking_query,\n            concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection\n        FROM\n            performance_schema.metadata_locks a\n        JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA\n        AND a.OBJECT_NAME = b.OBJECT_NAME\n        AND a.lock_status = 'PENDING'\n        AND b.lock_status = 'GRANTED'\n        AND a.OWNER_THREAD_ID &lt;&gt; b.OWNER_THREAD_ID\n        AND a.lock_type = 'EXCLUSIVE'\n        JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID\n        JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\n    ) t1,\n    (\n        SELECT\n            thread_id,\n            group_concat(   CASE WHEN EVENT_NAME = 'statement\/sql\/begin' THEN &quot;transaction_begin&quot; ELSE sql_text END ORDER BY event_id SEPARATOR &quot;;&quot; ) AS sql_text\n        FROM\n           performance_schema.events_statements_history\n        GROUP BY thread_id\n    ) t2\nWHERE\n    t1.granted_thread_id = t2.thread_id \/G<\/code><\/pre>\n<p>\u5bf9\u4e8e\u524d\u9762\u7684\u4f8b\u5b50\u6267\u884c\u6b64sql\uff0c\u5f97\u5230\u4e00\u4e2a\u6e05\u6670\u7684\u963b\u585e\u5173\u7cfb\uff1a<\/p>\n<pre><code> locked_schema: db1\n  locked_table: t1\n   locked_type: Metadata Lock\n      waiting_processlist_id: 28\n   waiting_age: 227\n waiting_query: alter table t1 add cl3 int\n waiting_state: Waiting for table metadata lock\n     blocking_processlist_id: 27\n  blocking_age: 252\nblocking_query: select * from t1\nsql_kill_blocking_connection: KILL 27\n1 row in set, 1 warning (0.00 sec)<\/code><\/pre>\n<p>\u6839\u636e\u663e\u793a\u7ed3\u679c\uff0cprocesslist_id\u4e3a27\u7684\u7ebf\u7a0b\u963b\u585e\u4e8628\u7684\u7ebf\u7a0b\uff0c\u6211\u4eec\u9700\u8981kill 27\u5373\u53ef\u89e3\u9501\u3002<\/p>\n<p>\u5b9e\u9645\u4e0a\uff0cMySQL\u4e5f\u63d0\u4f9b\u4e86\u4e00\u4e2a\u7c7b\u4f3c\u7684\u89c6\u56fe\u6765\u89e3\u51b3metadata lock\u95ee\u9898\uff0c\u89c6\u56fe\u540d\u79f0\u4e3asys.schema_table_lock_waits\uff0c\u4f46\u6b64\u89c6\u56fe\u67e5\u8be2\u7ed3\u679c\u6709bug\uff0c\u4e0d\u662f\u5f88\u51c6\u786e\uff0c\u5efa\u8bae\u5927\u5bb6\u8fd8\u662f\u53c2\u8003\u4e0a\u9762sql\u3002<\/p>\n<p><strong>\u516d\u3001\u672c\u6587\u5f00\u59cb\u7684\u6848\u4f8b\u6700\u7ec8\u5982\u4f55\u89e3\u51b3\uff1f<\/strong><\/p>\n<p>\u901a\u8fc7\u524d\u9762\u7684\u4ecb\u7ecd\uff0c\u672c\u6587\u5f00\u59cb\u7684\u6848\u4f8b\u4ea7\u751f\u7684\u8fc7\u7a0b\u5c31\u5f88\u7b80\u5355\u4e86\uff1a\u7528\u6237\u6267\u884c\u4e86\u4e00\u4e2a\u5168\u8868delete\uff0c\u5728\u76ee\u6807\u8868\u4e0a\u52a0\u4e86metadata\u8bfb\u9501\uff0c\u7531\u4e8e\u8868\u5f88\u5927\uff0c\u8bfb\u9501\u957f\u65f6\u95f4\u65e0\u6cd5\u91ca\u653e\uff0c\u540e\u6765\u53e6\u5916\u4e00\u4e2asession\u6267\u884c\u4e86drop table\u64cd\u4f5c\uff0c\u53c8\u9700\u8981\u5728\u8868\u4e0a\u52a0metadata\u5199\u9501\uff0c\u7531\u4e8e\u8bfb\u5199\u9501\u4e92\u76f8\u963b\u585e\uff0cdrop\u64cd\u4f5c\u53ea\u80fd\u7b49\u5f85delete\u64cd\u4f5c\u5b8c\u6210\u624d\u80fd\u83b7\u5f97\u5199\u9501\uff0c\u56e0\u6b64\u4ece\u8868\u9762\u6765\u770b\uff0c\u4e8c\u4e2a\u547d\u4ee4\u90fd\u957f\u65f6\u95f4\u6ca1\u6709\u54cd\u5e94\uff0c\u5176\u5b9e\u5185\u90e8\u4e00\u4e2a\u5728\u6267\u884c\uff0c\u4e00\u4e2a\u5728\u7b49\u5f85\u3002<\/p>\n<p>\u90a3\u600e\u4e48\u6765\u89e3\u51b3\u5462\uff1f\u56e0\u4e3a\u4eceshow processlist\u4ee5\u53ca\u5ba2\u6237\u63cf\u8ff0\u53ef\u4ee5\u5f88\u6e05\u695a\u7684\u77e5\u9053\u6545\u969c\u673a\u5236\uff0c\u5f53\u65f6\u5efa\u8bae\u5ba2\u6237\u5c06delete\u64cd\u4f5ckill\u6389\uff0c\u7b49\u6570\u636e\u56de\u6eda\u5b8c\u540e\u518d\u6267\u884cdrop\u64cd\u4f5c\u56e0\u4e3adelete\u5df2\u7ecf\u6267\u884c\u4e86\u4e00\u6bb5\u65f6\u95f4\uff0c\u56de\u6eda\u8fc7\u7a0b\u53ef\u80fd\u4f1a\u8f83\u957f\uff0c\u5ba2\u6237\u6700\u7ec8kill delete\u540e\u987a\u5229drop\u6210\u529f\u3002<\/p>\n<p><strong>\u5c0f\u7ed3<\/strong><\/p>\n<p>\u751f\u4ea7\u73af\u5883\u5927\u591a\u662fdml\u64cd\u4f5c\uff0cmetadata\u8bfb\u9501\u4e4b\u95f4\u4e0d\u4f1a\u4ea7\u751f\u9501\u7b49\u5f85\uff0c\u800c\u76ee\u524dMySQL\u7684ddl\u64cd\u4f5c\u5927\u591a\u53ef\u4ee5online\u6267\u884c\uff0c\u56e0\u6b64\u5373\u4f7f\u6709\u5199\u9501\uff0c\u4e5f\u4f1a\u5f88\u5feb\u964d\u7ea7\u4e3a\u8bfb\u9501\uff0c\u6240\u4ee5ddl\u6267\u884c\u671f\u95f4\u963b\u585edml\u7684\u51e0\u7387\u4e5f\u5f88\u5c0f\u3002\u6700\u5bb9\u6613\u51fa\u73b0\u7684\u60c5\u51b5\u662f\u7531\u4e8e\u6709\u672a\u5b8c\u6210\u7684\u4e8b\u52a1\uff0c\u5bfc\u81f4ddl metadata \u5199\u9501\u65e0\u6cd5\u52a0\u4e0a\uff0c\u53ea\u80fd\u5728\u9501\u961f\u5217\u7b49\u5f85\uff0c\u800c\u4e00\u65e6\u8fdb\u5165\u9501\u961f\u5217\uff0c\u5199\u9501\u53c8\u4f1a\u963b\u585e\u5176\u4ed6\u7684\u8bfb\u9501\uff0c\u5bfc\u81f4\u6570\u636e\u5e93\u8fde\u63a5\u5feb\u901f\u589e\u957f\uff0c\u76f4\u81f3\u6d88\u8017\u6b86\u5c3d\uff0c\u6700\u7ec8\u4e1a\u52a1\u53d7\u5230\u5f71\u54cd\u3002<\/p>\n<p>\u4e3a\u4e86\u5c3d\u53ef\u80fd\u907f\u514d\u7c7b\u4f3c\u95ee\u9898\uff0c\u4e0b\u9762\u662f\u51e0\u4e2a\u5c0f\u5efa\u8bae\uff1a<\/p>\n<ul>\n<li>\u751f\u4ea7\u73af\u5883\u7684\u4efb\u4f55\u5927\u8868\u6216\u9891\u7e41\u64cd\u4f5c\u7684\u5c0f\u8868\uff0cddl\u90fd\u8981\u975e\u5e38\u614e\u91cd\uff0c\u6700\u597d\u5728\u4e1a\u52a1\u4f4e\u5cf0\u671f\u6267\u884c\u3002<\/li>\n<li>\u8bbe\u8ba1\u4e0a\u8981\u5c3d\u53ef\u80fd\u907f\u514d\u5927\u4e8b\u52a1\uff0c\u5927\u4e8b\u52a1\u4e0d\u4ec5\u4ec5\u4f1a\u5e26\u6765\u5404\u79cd\u9501\u95ee\u9898\uff0c\u8fd8\u597d\u5f15\u8d77\u590d\u5236\u5ef6\u8fdf\/\u56de\u6eda\u7a7a\u95f4\u7206\u6ee1\u7b49\u5404\u7c7b\u95ee\u9898\u3002<\/li>\n<li>\u8981\u53ca\u65f6\u63d0\u4ea4\u4e8b\u52a1\uff0c\u7ecf\u5e38\u53d1\u73b0\u5ba2\u6237\u7aef\u8bbe\u7f6e\u4e86\u4e8b\u52a1\u624b\u5de5\u63d0\u4ea4\uff0c\u4f46sql\u6267\u884c\u540e\u5fd8\u8bb0\u70b9\u51fb\u63d0\u4ea4\u6309\u94ae\uff0c\u5bfc\u81f4\u4e8b\u52a1\u957f\u65f6\u95f4\u65e0\u6cd5\u63d0\u4ea4\u3002\u5efa\u8bae\u76d1\u63a7\u5b9e\u4f8b\u4e2d\u7684\u957f\u4e8b\u52a1\uff0c\u907f\u514d\u7531\u4e8e\u5404\u79cd\u539f\u56e0\u5bfc\u81f4\u4e8b\u52a1\u6ca1\u6709\u53ca\u65f6\u63d0\u4ea4\u3002<\/li>\n<\/ul>\n<p>\u4f5c\u8005\uff1a\u7fdf\u632f\u5174<\/p>\n<p><a href=\"https:\/\/segmentfault.com\/a\/1190000042496408\">https:\/\/segmentfault.com\/a\/1190000042496408<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u67d0\u65e5\uff0c\u8def\u4e0a\u6536\u5230\u7528\u6237\u54a8\u8be2\uff0c\u4e3a\u4e86\u6e05\u9664\u7a7a\u95f4\uff0c\u60f3\u5220\u9664\u67d0200\u591aG\u5927\u8868\u6570\u636e\uff0c\u4e14\u5df2\u7ecf\u786e\u8ba4\u6b64\u8868\u4e0d\u518d\u6709\u4e1a\u52a1\u8bbf\u95ee\uff0c\u4e8e\u662f\u6267\u884c\u4e86\u4e00\u6761 [&hellip;]<\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-10006500","post","type-post","status-publish","format-standard","hentry"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006500","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10006500"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006500\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10006500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10006500"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10006500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}