共计 6534 个字符，预计需要花费 17 分钟才能阅读完成。
本篇文章为大家展示了 MySQL 表结构怎样变更 Metadata Lock，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
想必玩过 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: 0
session1 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)可以看到，虽然是 RR 隔离级别，但在开启事务的情况下，第二次查询却没有结果。
主从复制问题
包括主从数据不一致，主从复制中断等。
如下面的主从数据不一致。
session1 create table t1(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
session1 begin;
Query OK, 0 rows affected (0.00 sec)
session1 insert into t1 values(1, a
Query OK, 1 row affected (0.00 sec)
session2 truncate table t1;
Query OK, 0 rows affected (0.46 sec)
session1 commit;
Query OK, 0 rows affected (0.35 sec)
session1 select * from t1;
Empty set (0.00 sec)再来看看从库的结果
session1 select * from slowtech.t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
+------+------+------+
row in set (0.00 sec)看看 binlog 的内容，可以看到，truncate 操作记录在前，insert 操作记录在后。
# at 7140
#180714 19:32:14 server id 1 end_log_pos 7261 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567934/*!*/;
create table t1(id int,name varchar(10)) engine=innodb
/*!*/;
# at 7261
#180714 19:32:30 server id 1 end_log_pos 7333 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
BEGIN
/*!*/;
# at 7333
#180714 19:32:30 server id 1 end_log_pos 7417 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
truncate table t1
/*!*/;
# at 7417
#180714 19:32:30 server id 1 end_log_pos 7444 Xid = 422
COMMIT/*!*/;
# at 7444
#180714 19:32:34 server id 1 end_log_pos 7516 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567954/*!*/;
BEGIN
/*!*/;
# at 7516
#180714 19:32:24 server id 1 end_log_pos 7611 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567944/*!*/;
insert into t1 values(1, a)
/*!*/;
# at 7611
#180714 19:32:34 server id 1 end_log_pos 7638 Xid = 421
COMMIT/*!*/;如果会话 2 执行的是 drop table 操作，还会导致主从中断。
有意思的是，如果会话 2 执行的是 alter table 操作，其依旧会被阻塞，阻塞时间受 innodb_lock_wait_timeout 参数限制。
mysql show processlist;
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| 54 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 58 | root | localhost | slowtech | Sleep | 1062 | | NULL |
| 60 | root | localhost | slowtech | Query | 11 | copy to tmp table | alter table t1 add c1 int |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
rows in set (0.00 sec)MDL 的基本概念
首先，看看官方的说法，
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session.
The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends.
A metadata lock on a table prevents changes to the table s structure.
This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.
从上面的描述可以看到，
1. MDL 出现的初衷就是为了保护一个处于事务中的表的结构不被修改。
2. 这里提到的事务包括两类，显式事务和 AC-NL-RO（auto-commit non-locking read-only）事务。显式事务包括两类：1. 关闭 AutoCommit 下的操作，2. 以 begin 或 start transaction 开始的操作。AC-NL-RO 可理解为 AutoCommit 开启下的 select 操作。
3. MDL 是事务级别的，只有在事务结束后才会释放。在此之前，其实也有类似的保护机制，只不过是语句级别的。
需要注意的是，MDL 不仅仅适用于表，同样也适用于其它对象，如下表所示，其中，等待状态 对应的是 show processlist 中的 State。
为了提高数据库的并发度，MDL 被细分为了 11 种类型。
MDL_INTENTION_EXCLUSIVE
MDL_SHARED
MDL_SHARED_HIGH_PRIO
MDL_SHARED_READ
MDL_SHARED_WRITE
MDL_SHARED_WRITE_LOW_PRIO
MDL_SHARED_UPGRADABLE
MDL_SHARED_READ_ONLY
MDL_SHARED_NO_WRITE
MDL_SHARED_NO_READ_WRITE
MDL_EXCLUSIVE
常用的有 MDL_SHARED_READ,MDL_SHARE D_WRITE 及 MDL_EXCLUSIVE，其分别用于 SELECT 操作，DML 操作及 DDL 操作。其它类型的对应操作可参考源码 sql/mdl.h。
对于 MDL_EXCLUSIVE，官方的解释是，
/*
An exclusive metadata lock.
A connection holding this lock can modify both table s metadata and data.
No other type of metadata lock can be granted while this lock is held.
To be used for CREATE/DROP/RENAME TABLE statements and for execution of
certain phases of other DDL statements.
*/
简而言之，MDL_EXCLUSIVE 是独占锁，在其持有期间是不允许其它类型的 MDL 被授予，自然也包括 SELECT 和 DML 操作。
这也就是为什么 DDL 操作被阻塞时，后续其它操作也会被阻塞。
关于 MDL 的补充
1. MDL 的最大等待时间由 lock_wait_timeout 参数决定，其默认值为 31536000（365 天）。在使用工具进行 DDL 操作时，这个值就不太合理。事实上，pt-online-schema-change 和 gh-ost 对其就进行了相应的调整，其中，前者 60s，后者 3s。
2. 如果一个 SQL 语法上有效，但执行时报错，如，列名不存在，其同样会获取 MDL 锁，直到事务结束才释放。
上述内容就是 MySQL 表结构怎样变更 Metadata Lock，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。
