[MySQL 5.7 metadata lock] 测试

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

介绍:
Metadata:事物内的表级锁
5.5开始引入进来,5.6.6前,事物开启后,会锁定表的meta data lock,其他会话对表有DDL操作时,均需要等待DML释放后方可继续、5.6.6后,不再阻塞其他会话执行的DDL,但原来的会话再次访问数据表时,会有error提示:table definition has changed , please retry transaction

是想要对一个表执行DDL时,会查看表有没有DML如果有则等待修改数据结构之前如何产生MDL锁,都会产生MDL 锁,meta data lock 锁,执行过程中避免修改,使用lock_wait_time 超时时间为1年,需要进行调小 

mysql@3306>[rds_test]>show variables like "%lock_wait_timeout%";
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
+--------------------------+----------+
2 rows in set (0.03 sec)
AI 代码解读

案例:
DDL操作被大查询block(阻塞)
当应用上线进入维护阶段,则开始会有较多的数据库变更操作,比如:添加字段,添加索引等操作,这一类操作导致锁故障也非常频繁,下面将会介绍一则案例,一个DDL操作被查询block,导致数据库连接堆积。

mysql@3306>[rds_test]>show processlist;
+-----------+--------------+---------------------+----------+---------+------+---------------------------------+----------------------------------------+
| Id        | User         | Host                | db       | Command | Time | State                           | Info                                   |
+-----------+--------------+---------------------+----------+---------+------+---------------------------------+----------------------------------------+
| 218380240 | dba_test     | 10.173.185.41:60754 | test_sql | Query   |   12 | Waiting for table metadata lock | update a set id=8888888 where id=1     |
| 219013568 | dba_test     | 10.173.185.41:60746 | test_sql | Query   |  746 | User sleep                      | select id , sleep(200) from a          |
| 268877248 | dba_test     | 10.173.185.41:60752 | test_sql | Query   |  688 | Waiting for table dba_test      | select * from a limit 1                |
| 269152246 |dba_test      | 10.173.185.41:60748 | test_sql | Query   |  742 | Waiting for table metadata lock | alter table a  add column gmt_c1  date |
| 269224487 | dba_test      | 10.173.185.41:60750 | rds_test | Query   |    0 | init                            | show processlist                       |
+-----------+--------------+---------------------+----------+---------+------+---------------------------------+----------------------------------------+
5 rows in set (0.02 sec)
AI 代码解读

metadata lock wait含义:为了在并发环境下维护表元数据的数据一致性,在表上有活动事物(显示或者隐式)的时候,不可以对元数据进行写入操作,因此MySQL引入了metadata lock,来饱和表的元数据信息。因此在对表进行上述操作时,如果表上有活动事物(未提交或回滚),请求写入的会话,会等待在metadata lock wait。

导致 Metadata lock wait 等待的常见因素包括:活动事务,当前有对表的长时间查询,显示或者隐式开启事务后未提交或回滚,比如查询完成后未提交或者回滚,表上有失败的查询事务等。

查询,更新和DDL操作的线程状态都为Waiting for table metadata lock,对表t的操作全部被阻塞,前端源源不断的请求到达数据库,这个时候数据库的连接很容易被打满,那我们来分析一下为什么有这这些锁等待:

1.alter 操作的锁等待:由于在表t上做了一个添加字段的操作,该操作会在结束前对表获取一个metadata lock,但是该表上已经有一个查询一直未结束,导致metadata lock一直获取不到,所以添加字段操作只能等待查询结束,这就解释了alter操作为什么状态为Waiting for table metadata lock。
2.查询和更新的锁等待:由于前面进行的alter操作已经在a表上试图获取metadata lock,所以后续对表a的的查询和更新操作在获取metadata lock的时候会被alter操作所阻塞,进而导致这些线程状态为Waiting for table metadata lock。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
32444
分享
相关文章
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
65 0
MySQL8.4 Enterprise安装Firewall及测试
MySQL8.4 Enterprise安装Firewall及测试
97 0
MySQL8使用物理文件恢复MyISAM表测试
MySQL8使用物理文件恢复MyISAM表测试
58 0
使用崖山YMP 迁移 Oracle/MySQL 至YashanDB 23.2 验证测试
这篇文章是作者尚雷关于使用崖山YMP迁移Oracle/MySQL至YashanDB 23.2的验证测试分享。介绍了YMP的产品信息,包括架构、版本支持等,还详细阐述了外置库部署、YMP部署、访问YMP、数据源管理、任务管理(创建任务、迁移配置、离线迁移、校验初始化、一致性校验)及MySQL迁移的全过程。
【赵渝强老师】MySQL的基准测试与sysbench
本文介绍了MySQL数据库的基准测试及其重要性,并详细讲解了如何使用sysbench工具进行测试。内容涵盖sysbench的安装、基本使用方法,以及具体测试MySQL数据库的步骤,包括创建测试数据库、准备测试数据、执行测试和清理测试数据。通过这些步骤,可以帮助读者掌握如何有效地评估MySQL数据库的性能。
275 5
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
105 1
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
本文详细介绍了在 MySQL 中创建数据库和表的方法。包括安装 MySQL、用命令行和图形化工具创建数据库、选择数据库、创建表(含数据类型介绍与选择建议、案例分析、最佳实践与注意事项)以及查看数据库和表的内容。文章专业、严谨且具可操作性,对数据管理有实际帮助。
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
MySQL下载安装全攻略!小白也能轻松上手,从此数据库不再难搞!
这是一份详细的MySQL安装与配置教程,适合初学者快速上手。内容涵盖从下载到安装的每一步操作,包括选择版本、设置路径、配置端口及密码等。同时提供基础操作指南,如数据库管理、数据表增删改查、用户权限设置等。还介绍了备份恢复、图形化工具使用和性能优化技巧,帮助用户全面掌握MySQL的使用方法。附带常见问题解决方法,保姆级教学让你无忧入门!
MySQL下载安装全攻略!小白也能轻松上手,从此数据库不再难搞!

热门文章

最新文章

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问