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

概述在线上进行DDL *** 作时，相对于其可能带来的系统负载，其实，我们最担心的还是MDL其可能导致的阻塞问题。 一旦DDL *** 作因获取不到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 | Waiting on empty queue          NulL                               9 | root            | Sleep   |   57 |                                 | 12 | employees | query   40 for table Metadata lock alter table slowtech.t1 add c1 int 13 35 select * from slowtech.t1          14 30 15 19 16 10 17 |    0 | starting                        | show processList                   8 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)session1from t1;----+------+| ID  | name 1 | a    2 | b    2 rows  sec)session2table t1 int2 rows affected (0.02 sec)Records: 2  Duplicates: 0  Warnings: 0session1 t1;Empty commit----+------+------+| c1  NulL 0.00 sec)

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

 

主从复制问题

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

session1create table t1(ID int,name varchar(10)) engine=innodb;query OK,1)">insert into t1 values(1,'a');query OK,1); Font-weight: bold">1 row affected (truncate table t1;query OK,1); Font-weight: bold">0.460.350.00 sec)

 

再来看看从库的结果

session1 slowtech.t1;| ID   | c1   1 row 0.00 sec)

 

看看binlog的内容，可以看到，truncate *** 作记录在前，insert *** 作记录在后。

# at 7140#180714 19:32:14 server ID 1  end_log_pos 7261    query    thread_ID=31    exec_time0    error_code0SET TIMESTAMP1531567934/*!*/;innodb;# at 726130 server ID 7333    query    thread_ID32    exec_time1531567950BEGIN;# at 73337417    query    thread_ID t174177444    XID = 422COMMIT744434 server ID 7516    query    thread_ID1531567954751624 server ID 7611    query    thread_ID1531567944)76117638    XID 421*/;

 

如果会话2执行的是drop table *** 作，还会导致主从中断。

有意思的是，如果会话2执行的是alter table *** 作，其依旧会被阻塞，阻塞时间受innodb_lock_wait_timeout参数限制。

MysqL--+------+-----------+----------+---------+------+-------------------+---------------------------+User | db       | State             | Info                      54 | root NulL     NulL              | show processList          58 | slowtech 1062 |                   NulL                      60 11 | copy to tmp table 3 rows 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全部内容，希望文章能够帮你解决MySQL表结构变更，不可不知的Metadata Lock所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。