mysql 别名 metadata_MySQL表结构变更，不可不知的Metadata Lock

元数据锁(Metadata Lock，以下简称MDL)计划写三篇，这篇主要是介绍MDL的引入背景和基本概念，后两篇会着重介绍MySQL 5.7(包括8.0)及5.6中如何定位MDL问题。

在线上进行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|Waitingon empty queue          | NULL                               |

|9| root            | localhost | NULL      |Sleep|57|                                 | NULL                               |

|12| root            | localhost | employees |Query|40|Waitingfortable metadatalock| alter table slowtech.t1 add c1int|

|13| root            | localhost | employees |Query|35|Waitingfortable metadatalock|select*fromslowtech.t1          |

|14| root            | localhost | employees |Query|30|Waitingfortable metadatalock|select*fromslowtech.t1          |

|15| root            | localhost | employees |Query|19|Waitingfortable metadatalock|select*fromslowtech.t1          |

|16| root            | localhost | employees |Query|10|Waitingfortable metadatalock|select*fromslowtech.t1          |

|17| root            | localhost | employees |Query|0| starting                        | show processlist                   |

+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+

8rowsinset(0.00sec)

如果发生在线上，无疑会影响到业务。所以，一般建议将DDL操作放到业务低峰期做，其实有两方面的考虑，1. 避免对系统负载产生较大影响。2. 减少DDL被阻塞的概率。

MDL引入的背景

MDL是MySQL 5.5.3引入的，主要用于解决两个问题，

RR事务隔离级别下不可重复读的问题

如下所示，演示环境，MySQL 5.5.0。session1>begin;

QueryOK,0rows affected (0.00sec)

session1>select*fromt1;

+------+------+

| id  | name |

+------+------+

|1| a    |

|2| b    |

+------+------+

2rowsinset(0.00sec)

session2> alter table t1 add c1int;

QueryOK,2rows affected (0.02sec)

Records:2Duplicates:0Warnings:0

session1>select*fromt1;

Emptyset(0.00sec)

session1> commit;

QueryOK,0rows affected (0.00sec)

session1>select*fromt1;

+------+------+------+

| id  | name | c1  |

+------+------+------+

|1| a    | NULL |

|2| b    | NULL |

+------+------+------+

2rowsinset(0.00sec)

可以看到，虽然是RR隔离级别，但在开启事务的情况下，第二次查询却没有结果。

主从复制问题

包括主从数据不一致，主从复制中断等。

如下面的主从数据不一致。session1> create table t1(idint,name varchar(10)) engine=innodb;

QueryOK,0rows affected (0.00sec)

session1>begin;

QueryOK,0rows affected (0.00sec)

session1> insertintot1 values(1,'a');

QueryOK,1row affected (0.00sec)

session2> truncate table t1;

QueryOK,0rows affected (0.46sec)

session1> commit;

QueryOK,0rows affected (0.35sec)

session1>select*fromt1;

Emptyset(0.00sec)

再来看看从库的结果session1>select*fromslowtech.t1;

+------+------+------+

| id   | name | c1   |

+------+------+------+

|1| a    | NULL |

+------+------+------+

1rowinset(0.00sec)

看看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(idint,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/*!*/;

insertintot1 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 c1int|

+----+------+-----------+----------+---------+------+-------------------+---------------------------+

3rowsinset(0.00sec)

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 5.7中如何定位"Waiting for table metadata lock"》

欢迎阅读公众号其它文章