【Mysql】metadata lock鎖

可以看到，卡住的那一步是在rename result table，MySQL后臺(tái)將ALTER保存變成連續(xù)操作“創(chuàng)建臨時(shí)新表->插入老表的數(shù)據(jù)->臨時(shí)新表取到老表(RENAME)”，即實(shí)際的變化老表的操作在RENAME階段才發(fā)生。而這一步即是被阻塞的那一步。所以可以確認(rèn)，Metadata lock影響到的是要真實(shí)修改表結(jié)構(gòu)的動(dòng)作。（回想：和pt-osc那節(jié)，一個(gè)select未執(zhí)行完，alter 修改表結(jié)構(gòu)產(chǎn)生metadata lock一個(gè)道理）

基于此，我們可以驗(yàn)證ALTER TABLE,RENAME TABLE,DROP TABLE都會(huì)被Metadata lock影響，驗(yàn)證步驟與上類似，省略。

值得注意的是，創(chuàng)建一個(gè)已存在的表也會(huì)被Metadata lock影響。如果在一個(gè)事務(wù)中用到了表A的Metadata lock（如SELECT了該表）且事務(wù)未完成，那么創(chuàng)建同名的表也會(huì)被卡住。所參考的文章中提及這一點(diǎn)。有興趣可參看：討論頁  ，這里不作討論。

metadata lock的另一個(gè)副作用：如上例中，ALTER TABLE應(yīng)metadata lock被阻塞后，這導(dǎo)致后續(xù)其他事務(wù)針對(duì)該表的SELECT也會(huì)被阻塞！即ALTER TABLE的操作會(huì)影響到其他SELECT操作。根據(jù)上述示例：

Session2 mysql> alter table table1 add column c1 int;

此步一直處于等待狀態(tài)時(shí)，新會(huì)話中查詢table1。

Session3 mysql> select * from test1;  #一直等待

Session4 mysql>show full processlist;
mysql> show full processlist;
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
|  4 | root | localhost | test | Sleep   | 1857 |                                 | NULL                                |
|  5 | root | localhost | test | Query   |  504 | Waiting for table metadata lock | select * from test1                 |
|  6 | root | localhost | NULL | Query   |    0 | NULL                            | show full processlist               |
|  8 | root | localhost | test | Query   |  511 | Waiting for table metadata lock | alter table test1 add column c2 int |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+

Session1 mysql> commit; 

第一個(gè)會(huì)話提交后，Session2、Session3也成功執(zhí)行。看看Session3的select在哪個(gè)步驟需要等待：

Session3 mysql> show profile;
+——————————–+———–+
| Status                         | Duration  |
+——————————–+———–+
| starting                       |  0.000018 |
| Waiting for query cache lock   |  0.000003 |
| checking query cache for query |  0.000037 |
| checking permissions           |  0.000007 |
| Opening tables                 | 22.502591 |
| System lock                    |  0.000013 |
| Waiting for query cache lock   |  0.000024 |
| init                           |  0.000022 |
| optimizing                     |  0.000009 |
| statistics                     |  0.000011 |
| preparing                      |  0.000015 |
| executing                      |  0.000003 |
| Sending data                   |  0.000036 |
| end                            |  0.000009 |
| query end                      |  0.000005 |
| closing tables                 |  0.000006 |
| freeing items                  |  0.000007 |
| Waiting for query cache lock   |  0.000003 |
| freeing items                  |  0.000015 |
| Waiting for query cache lock   |  0.000002 |
| freeing items                  |  0.000002 |
| storing result in query cache  |  0.000004 |
| logging slow query             |  0.000002 |
| logging slow query             |  0.000028 |
| cleaning up                    |  0.000003 |
+——————————–+———–+

可以看到SELECT語句首先檢查查詢緩存，沒有命中，然后在Opening tables階段卡住。

這引出了另一個(gè)特點(diǎn)，基于上述場景，如果后續(xù)的SELECT能夠在查詢緩存中命中，那么不會(huì)被ALTER TABLE卡住，因?yàn)椴恍枰狾pening tables操作。命中緩存SELECT的SHOW PROFILE如下：

mysql>  show profile;
+——————————–+———-+
| Status                         | Duration |
+——————————–+———-+
| starting                       | 0.000017 |
| Waiting for query cache lock   | 0.000004 |
| checking query cache for query | 0.000006 |
| checking privileges on cached  | 0.000004 |
| checking permissions           | 0.000007 |
| sending cached result to clien | 0.000011 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+——————————–+———-+
8 rows in set (0.00 sec)