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

2024-04-02 19:04:59 151人浏览八月长安

摘要

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

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

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阶段.