这篇文章将为大家详细讲解有关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阶段.
关于“MySQL中查询事物与DDL引发Waiting for table metadata lock的两个阶段是什么”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。