MySQL中查询事物与DDL引发Waiting for table metadata lock的两个阶段是什么

128次阅读

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

行业资讯
数据库
MySQL 数据库
MySQL 中查询事物与 DDL 引发 Waiting for table metadata lock 的两个阶段是什么

这篇文章将为大家详细讲解有关 MySQL 中查询事物与 DDL 引发 Waiting for table metadata lock 的两个阶段是什么，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

1. 现象描述：
SESSION1：

SESSION2：

SESSION3：

备注：（这里 SESSION1，SESSION2，SESSION3 按先后顺序执行）
当 SESSION1 未提交时，SESSION2 阻塞，SESSION3 阻塞
当 SESSION1 提交时，SESSION2 仍然阻塞，SESSION3 执行成功（这里限于篇幅，读者可以自行实验）

2. 现象质疑：
当 session1 未提交时，我们看看 metadata_locks, 由下图我们可以分析得出，是 session1 的 SHARE_READ 阻塞了 EXCLUSIVE，同时 SESSION3 的 SHARE_READ 被 EXCLUSIVE 给阻塞了

当 SESSION1 提交后，我们再来看 metadata_locks（如下图）：我们发现 SESSION2 被 SESSION3 阻塞了，且还是 SESSION3 的 EXCLUSIVE 被 SESSION2 的 SHARE_READ 阻塞了，这里我们不经疑惑，难道是 SESSION3 的 SHARD_READ 的优先级要高些？（但是本人查看 MDL_SHARE_READ 的源码注释，没有发现 MDL_SHARE_READ 的优先级要高于 MDL_EXCLUSIVE）

3. 现象分析：
带着上一步中标红部分的这个疑问，我们来查看下 sql 执行耗时的各个阶段，具体情况如下：

mysql show profile;
+——————————–+———-+
| Status | Duration |
+——————————–+———-+
| Waiting for table metadata loc | 1.001239 |
| After create | 0.000047 |
| Waiting for table metadata loc | 1.002126 |
| After create | 0.000047 |
| Waiting for table metadata loc | 1.000864 |
| After create | 0.000047 |
| Waiting for table metadata loc | 1.001443 |
| After create | 0.000047 |
| Waiting for table metadata loc | 1.001984 |
| After create | 0.000046 |
| Waiting for table metadata loc | 1.003780 |
| After create | 0.000049 |
| Waiting for table metadata loc | 1.003622 |
| After create | 0.000049 |
| Waiting for table metadata loc | 1.000299 |
| After create | 0.000051 |
| Waiting for table metadata loc | 1.001613 |
| After create | 0.000048 |
| Waiting for table metadata loc | 1.000226 |
| After create | 0.000077 |
| Waiting for table metadata loc | 1.000196 |
| After create | 0.000048 |
| Waiting for table metadata loc | 1.000574 |
| After create | 0.000049 |
| Waiting for table metadata loc | 1.001014 |
| After create | 0.000046 |
| Waiting for table metadata loc | 1.000834 |
| After create | 0.000047 |
| Waiting for table metadata loc | 1.001708 |
| After create | 0.000047 |
| Waiting for table metadata loc | 0.492941 |
| After create | 0.000130 |
| System lock | 0.000028 |
| preparing for alter table | 0.000184 |
| altering table | 0.000037 |
| Waiting for table metadata loc | 1.000922 |
| altering table | 0.000057 |
| Waiting for table metadata loc | 1.000320 |
| altering table | 0.000082 |
| Waiting for table metadata loc | 1.001329 |
| altering table | 0.000055 |
| Waiting for table metadata loc | 1.002728 |
| altering table | 0.000054 |
| Waiting for table metadata loc | 1.000887 |
| altering table | 0.000055 |
| Waiting for table metadata loc | 1.002754 |
| altering table | 0.000055 |
| Waiting for table metadata loc | 1.001484 |
| altering table | 0.000055 |
| Waiting for table metadata loc | 1.001034 |
| altering table | 0.000059 |
| Waiting for table metadata loc | 1.000547 |
| altering table | 0.000057 |
| Waiting for table metadata loc | 1.003391 |
| altering table | 0.000058 |
| Waiting for table metadata loc | 1.002230 |
| altering table | 0.000059 |
| Waiting for table metadata loc | 1.002789 |
| altering table | 0.000058 |
| Waiting for table metadata loc | 1.002071 |
| altering table | 0.000059 |
| Waiting for table metadata loc | 1.003891 |
| altering table | 0.000057 |
| Waiting for table metadata loc | 1.003908 |
| altering table | 0.000057 |
| Waiting for table metadata loc | 1.000404 |
| altering table | 0.000055 |
| Waiting for table metadata loc | 1.003572 |
| altering table | 0.000056 |
| Waiting for table metadata loc | 1.000270 |
| altering table | 0.000056 |
| Waiting for table metadata loc | 1.003832 |
| altering table | 0.000148 |
| Waiting for table metadata loc | 1.000791 |
| altering table | 0.000054 |
| Waiting for table metadata loc | 1.004019 |
| altering table | 0.000059 |
| Waiting for table metadata loc | 1.000523 |
| altering table | 0.000056 |
| Waiting for table metadata loc | 1.004071 |
| altering table | 0.000058 |
| Waiting for table metadata loc | 1.000656 |
| altering table | 0.000055 |
| Waiting for table metadata loc | 1.001957 |
| altering table | 0.000058 |
| Waiting for table metadata loc | 1.000260 |
| altering table | 0.000056 |
| Waiting for table metadata loc | 1.000440 |
| altering table | 0.000057 |
| Waiting for table metadata loc | 1.002061 |
| altering table | 0.000055 |
| Waiting for table metadata loc | 0.878074 |
| altering table | 0.000127 |
| committing alter table to stor | 0.031622 |
| end | 0.000078 |
| query end | 0.002045 |
| closing tables | 0.000041 |
| freeing items | 0.000143 |
| logging slow query | 0.000116 |
| cleaning up | 0.000043 |
+——————————–+———-+
100 rows in set, 1 warning (0.00 sec)

从这里我们可以看出，After create 和 altering table 这两个阶段最耗时，同时这里两个阶段也出现了 Waiting for table metadata lock 的字眼，说明 alter table add/drop index 是阻塞在这两阶段（从时间上可以看出是一秒扫描一次是否能上锁）。
altering table 这个阶段我们知道修改完数据之后会上 MDL_EXCLUSIVE, 这样就会与 MDL_SHARE_READ 阻塞，那么 After create 又是什么阶段呢？
在 oracle 官方 bug 帖上看到 oracle 人员的一个回复：
At certain point ALTER TABLE needs to acquire exclusive lock on table to install a new version of .FRM and to get rid of outdated TABLE/TABLE_SHARE/handler instances in Table and Table Definition caches. At this point it will wait for existing SELECTs to stop and will block any new SELECTs.
意思是在某个点上(从源代码可以追踪到这是 After create 阶段)，alter table add/drop index 需要获得排他锁（MDL_EXCLUSIVE），目的是新建.FRM 并清除旧的 TABLE,TABLE_SHARE,handler 的实例，这些实例在表以及表定义缓存中；这样，这个阶段需要的排它锁（MDL_EXCLUSIZE）也会跟 MDL_SHARE_READ 互斥。

4. 总结：
当线上 alter table add /drop index，可能阻塞在两个阶段，一个是 After create，另外一个是 altering table;
通常情况下：大查询在 alter table 语句执行之前（大查询没执行完而 alter table 开始），alter table 语句会阻塞在 After create 阶段，大查询在 alter table 语句执行之后（alter table 事物没完成，大查询开始），alter table 会阻塞在 altering table 阶段.

关于“MySQL 中查询事物与 DDL 引发 Waiting for table metadata lock 的两个阶段是什么”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。

正文完