架构师技能6：深入MySQL原理-Waiting for table metadata lock引发系统崩溃

一、背景我们在某个下午三点执行了某个库zone表的ddl操作，导致线上服务出现灾难性的后果：1、系统核心服务无法访问。2、系统基础服务依赖zone表的服务C出现线程阻塞进而导致服务C无法响应连接，使用apache的httpClient在获取链接超时报错：Read timed out。（不要被字面意思误解，httpClient的Read timed out不一定是响应超时，而是socket conn

hguisu

6194人浏览 · 2022-03-27 14:54:44

hguisu · 2022-03-27 14:54:44 发布

开篇语录：以架构师的能力标准去分析每个问题，过后由表及里分析问题的本质，复盘总结经验，并把总结内容记录下来。当你解决各种各样的问题，也就积累了丰富的解决问题的经验，解决问题的能力也将自然得到极大的提升。

励志做架构师的撸码人，认知很重要，可以订阅：架构设计专栏

我们码农平时大多数时间都在撸码或者撸码的路上，很少关注mysql的一些底层原理，当出现问题时没能力第一时间解决问题，出现问题后不去层层剖析问题产生的原因，后续也就可能无法避免或者绕开同类的问题。因此不要单纯做Ctrl+c和Ctrl+V，而是一边仰望星空(目标规划)，一边脚踏实地去分析每个问题。
在mysql系列专栏里面，我深入浅出的总结了mysql相关知识，感兴趣的话可以去阅读，有问题就可以随时相互交流学习。

1、MySQL架构原理(详解)：了解mysql原理机制，如何执行sql。

2、MySQL并发控制：锁机制熟悉mysql锁，合理在代码设置事务。

3、 mysql索引原理：B-树和B+树的应用：数据搜索和数据库索引如何高效建表索引。

4、mysql优化详解：高效的写sql语句

一、背景

我们在最近某个下午三点执行了某个库zone表的ddl操作引起的metadata lock，后续查询或者更新zone表的线程都会阻塞，导致线上服务出现灾难性的后果：

1、系统核心服务无法访问。

2、系统基础服务依赖zone表的服务C出现线程阻塞进而导致服务C无法响应连接，使用apache的httpClient在获取链接超时报错：Read timed out。（在此顺便提一下：不要被Read timed out的字面意思误解，httpClient的Read timed out不一定是响应超时，而也可能是socket connect timeout，就是服务C无法接收请求连接）。

由于此前从未遇到过Waiting for table metadata lock异常，原因是之前东家的架构针对数据库操作有平台工具和完善发布流程来确保ddl操作不会影响线上服务。在此针对MDL简单做个总结(部分内容摘录网上), 也对线上的服务时刻保持敬畏并引以为戒，警钟长鸣。

二、MySQL出现Waiting for table metadata lock的原因

1、MDL锁（metadata lock)的原因

mysql为了在并发环境下维护表元数据的数据一致性，在表上执行事务（显式或隐式）的时候，不可以对表元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。

MDL (metadata lock) 是表级锁，在访问一个表的时候会被自动加上，以保证读写的正确性。当对一个表做 DML 操作的时候，加 MDL 读锁；当做 DDL 操作时候，加 MDL 写锁。

MDL是在mysql5.5之前也有类似保护元数据的机制，只是没有明确提出MDL概念而已。但是5.5之前版本(比如5.1)与5.5之后版本在保护元数据这块有一个显著的不同点是，5.1对于元数据的保护是语句级别的，5.5对于metadata的保护是事务级别的。所谓语句级别，即语句执行完成后，无论事务是否提交或回滚，其表结构可以被其他会话更新；而事务级别则是在事务结束后才释放MDL。

对于引入MDL，其主要解决了2个问题：

1）一是解决事务隔离的问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；

2）二是解决数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在Metadata lock wait 。

支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续对该表的所有访问操作(包括读)都被会阻塞在，因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。结果导致连接堆积，业务受影响。

虽然MySQL 5.6推出了online ddl特性，解决执行ddl锁表的问题，但是也会存在Waiting for table metadata lock的情况，具体先看看MySQL Online DDL 原理：

三、MySQL Online DDL 原理

为了解决了执行ddl锁表的问题，mysql 5.6推出来的online ddl特性。online ddl特性保证了在进行表变更时，不会堵塞线上业务读写，保障在变更时，库依然能正常对外提供访问。

在 MySQL 5.6 之前，MySQL 的 DDL 操作会按照原来的表复制一份，并做相应的修改，例如，对表 A 进行 DDL 的具体过程如下：

按照表 A 的定义新建一个表 B
对表 A 加写锁
在表 B 上执行 DDL 指定的操作
将 A 中的数据拷贝到 B
释放 A 的写锁
删除表 A
将表 B 重命名为 A

在 2-4 的过程中，如果表 A 数据量比较大，拷贝到表 B 的过程会消耗大量时间，并占用额外的存储空间。此外，由于 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。

因此，MySQL 5.6 增加了 Online DDL，允许在不中断数据库服务的情况下进行 DDL 操作。

1、Online ddl用法

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的方式和 DDL 期间 DML 的兵法控制

ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）。
如果设置 ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的方式，采用表拷贝的方式进行，过程中会阻塞所有的DML。另外也可以设置 ALGORITHEM=DAFAULT，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。
LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）

不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行，具体的支持情况可以在 MySQL Reference Manual — Online DDL Operations 中查看。

例如 Table 14.10 中显示修改列的数据类型不支持 INPLACE

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Changing the column data type	No	Yes	No	No

这时尝试将原类型为 FLOAT 的 column_name 改为 INT

ALTER TABLE tbl_name MODIFY COLUMN column_name INT, ALGORITHM=INPLACE, LOCK=NONE;

会报错

ERROR: 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

2、Online ddl执行过程

可以参考官方文档：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html。online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。

1）Prepare初始化阶段：根据存储引擎、用户指定的操作、用户指定的 ALGORITHM 和 LOCK 计算 DDL 过程中允许的并发量，这个过程中会获取一个 shared metadata lock，用来保护表的结构定义。

创建新的临时frm文件
持有EXCLUSIVE-MDL锁，禁止读写
根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)
更新数据字典的内存对象
分配row_log对象记录增量
生成新的临时ibd文件

2）ddl执行阶段：根据第一步的情况决定是否将 shared metadata lock 升级为 exclusive metadata lock（仅在语句准备阶段），然后生成语句并执行。执行期间的 shared metadata lock 保证了不会同时执行其他的 DDL，但 DML 能可以正常执行。

降级EXCLUSIVE-MDL锁，允许读写
扫描old_table的聚集索引每一条记录rec
遍历新表的聚集索引和二级索引，逐一处理
根据rec构造对应的索引项
将构造索引项插入sort_buffer块
将sort_buffer块插入新的索引
处理ddl执行过程中产生的增量(仅rebuild类型需要)

3）commit提交：将 shared metadata lock 升级为 exclusive metadata lock，然后删除旧的表定义，提交新的表定义

升级到EXCLUSIVE-MDL锁，禁止读写
重做最后row_log中最后一部分增量
更新innodb的数据字典表
提交事务(刷事务的redo日志)
修改统计信息
rename临时idb文件，frm文件
变更完成

Online DDL 过程中占用 exclusive MDL 的步骤执行很快，所以几乎不会阻塞 DML 语句。

注意：

在 DDL 执行前或执行时，其他事务可以获取 MDL。由于需要用到 exclusive MDL，所以必须要等到其他占有 metadata lock 的事务提交或回滚后才能执行上面两个涉及到 MDL 的地方。

四、出现Metadata Lock的场景：

场景一：长事务运行，阻塞DDL（Data Definition Language包括增减字段、增减索引等操作），继而阻塞所有同表的后续操作

通过show processlist可以看到TableA上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。

这是最基本的一种情形，这个和mysql 5.6中的online ddl并不冲突，一般alter table的操作过程中（见下图），在after create步骤会获取metadata 独占锁，当进行到altering table的过程时（通常是最花时间的步骤），对该表的读写都可以正常进行，这就是online ddl的表现，并不会像之前在整个alter table过程中阻塞写入。（当然，也并不是所有类型的alter操作都能online的，具体可以参见官方手册：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html）
处理方法： kill 掉 DDL所在的session.

我们本次的故障就是业务线程在构建服务缓存，在此过程中使用事务处理，由于构建缓存时间比较长，结果事务长时间运行，阻塞后面ddl 操作。

例如：，Session 1 在事务中执行 SELECT 操作，此时会获取 shared MDL。由于是在事务中执行，所以这个 shared MDL 只有在事务结束后才会被释放。

# Session1

> START TRANSACTION;
> SELECT * FROM tbl_name;
# 正常执行

这时 Session2 想要执行 DML 操作也只需要获取 shared MDL，仍然可以正常执行。

# Session2
> SELECT * FROM tbl_name;
# 正常执行

但如果 Session 3 想执行 DDL 操作就会阻塞，因为此时 Session1 已经占用了 shared MDL，而 DDL 的执行需要先获取 exclusive MDL，因此无法正常执行。

# Session 3
> ALTER TABLE tbl_name ADD COLUMN n INT;
# 阻塞

通过 `show processlist` 可以看到 ALTER 操作正在等待 MDL。

由于 exclusive MDL 的获取优先于 shared MDL，后续尝试获取 shared MDL 的操作也将会全部阻塞

# Session4
> SELECT * FROM tbl_name;
# 阻塞

到此后续无论是 DML 和 DDL 都将阻塞，直到 Session1 提交或者回滚，Session1 占用的 shared MDL 被释放，后面的操作才能继续执行。

场景二：事务未提交/回滚(比如查询完成后未提交或者回滚），阻塞DDL，继而阻塞所有同表的后续操作

通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以在 information_schema.innodb_trx中查看到。在事务没有完成之前，TableA上的锁不会释放，alter table同样获取不到metadata的EXCLUSIVE独占锁。

可以模拟：

Session 1 ：

             set session autocommit =0 ;
             select * from table1 limit 1;
             select * from table2 limit 1;

session 2:

alter table table1 add column i int;

sesssion 3:

select * from table2 limit 1; --可以查询
select * from table1 limit 1; 卡住

处理方法：通过 select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉，让其回滚。

场景三：表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时DDL仍然会被堵住

通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。

官方手册上对此的说明如下：

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志。

处理方法：通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.

场景四：当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住

总之，alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的)，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。