MySQL表结构变更，不可不知的Metadata Lock

136次阅读

共计 6097 个字符，预计需要花费 16 分钟才能阅读完成。

在线上进行 DDL 操作时，相对于其可能带来的系统负载，其实，我们最担心的还是 MDL 其可能导致的阻塞问题。

一旦 DDL 操作因获取不到 MDL 被阻塞，后续其它针对该表的其它操作都会被阻塞。典型如下，如阻塞稍久的话，我们会看到 Threads_running 飙升，CPU 告警。

如果发生在线上，无疑会影响到业务。所以，一般建议将 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 |
+——+——+
2 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 |
+——+——+——+
2 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 |
+——+——+——+
1 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 参数限制。

首先，看看官方的说法，

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。

MySQL 表结构变更，不可不知的 Metadata Lock

为了提高数据库的并发度，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 锁，直到事务结束才释放。

：

正文完

星哥说事-微信公众号