前言
想必玩過mysql的人對Waiting for table metadata lock肯定不會陌生，一般都是進行alter操作時被堵住了，導致了我們在show processlist 時，看到線程的狀態是在等metadata lock。本文會對MySQL表結構變更的Metadata Lock進行詳細的介紹。
在線上進行DDL操作時，相對于其可能帶來的系統負載，其實，我們最擔心的還是MDL其可能導致的阻塞問題。
一旦DDL操作因獲取不到MDL被阻塞，后續其它針對該表的其它操作都會被阻塞。典型如下，如阻塞稍久的話，我們會看到Threads_running飆升，CPU告警。
| mysql> show processlist;+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+| 4 | event_scheduler | localhost | NULL | Daemon | 122 | Waiting on empty queue | NULL || 9 | root | localhost | NULL | Sleep | 57 | | NULL || 12 | root | localhost | employees | Query | 40 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int || 13 | root | localhost | employees | Query | 35 | Waiting for table metadata lock | select * from slowtech.t1 || 14 | root | localhost | employees | Query | 30 | Waiting for table metadata lock | select * from slowtech.t1 || 15 | root | localhost | employees | Query | 19 | Waiting for table metadata lock | select * from slowtech.t1 || 16 | root | localhost | employees | Query | 10 | Waiting for table metadata lock | select * from slowtech.t1 || 17 | root | localhost | employees | Query | 0 | starting | show processlist |+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+rows in set (0.00 sec) |
如果發生在線上，無疑會影響到業務。所以，一般建議將DDL操作放到業務低峰期做，其實有兩方面的考慮，1. 避免對系統負載產生較大影響。2. 減少DDL被阻塞的概率。
MDL引入的背景
MDL是MySQL 5.5.3引入的，主要用于解決兩個問題，
RR事務隔離級別下不可重復讀的問題
如下所示，演示環境，MySQL 5.5.0。
| session1> begin;Query OK, 0 rows affected (0.00 sec)session1> select * from t1;+------+------+| id | name |+------+------+| 1 | a || 2 | b |+------+------+rows in set (0.00 sec)session2> alter table t1 add c1 int;Query OK, 2 rows affected (0.02 sec)Records: 2 Duplicates: 0 Warnings: 0session1> select * from t1;Empty set (0.00 sec)session1> commit;Query OK, 0 rows affected (0.00 sec)session1> select * from t1;+------+------+------+| id | name | c1 |+------+------+------+| 1 | a | NULL || 2 | b | NULL |+------+------+------+rows in set (0.00 sec) |
