AI 助理
备案控制台
开发者社区 数据库 文章 正文

深入理解MDL元数据锁

2022-05-28 853
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 当你在MySQL中执行一条SQL时，语句并没有在你预期的时间内执行完成，这时候我们通常会登陆到MySQL数据库上查看是不是出了什么问题，通常会使用的一个命令就是 show processlist，看看有哪些session，这些session在做什么事情。当你看到 waiting for table metadata lock 时，那就是遇到MDL元数据锁了。本篇文章将会介绍MDL锁的产生与排查过程。

1.什么是MDL锁


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


对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。


元数据锁是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥），申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放）。


注： 支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。


2.模拟与查找MDL锁


MDL锁通常发生在DDL操作挂起的时候，原因是有未提交的事务对该表进行DML操作。而MySQL的会话那么多，不知道哪个会话的操作没有及时提交影响了DDL。通常我们排查这类问题，往往需要从information_schema.innodb_trx表中查询当前在执行的事务，但当SQL已经执行过了，没有commit，这个时候这个表中是看不到SQL的。


在MySQL5.7中，performance_schema库中新增了metadata_locks表，专门记录MDL的相关信息。首先要开启MDL锁记录，执行如下SQL开启：

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

下面展示下模拟及查找MDL锁的过程：

# 会话1 事务中执行DML操作
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student_tb (stu_id,stu_name) values (1009,'xin');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time         | update_time         |
+--------------+--------+----------+---------------------+---------------------+
|            1 |   1001 | from1    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            2 |   1002 | dfsfd    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            3 |   1003 | fdgfg    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            4 |   1004 | sdfsdf   | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            5 |   1005 | dsfsdg   | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            6 |   1006 | fgd      | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            7 |   1007 | fgds     | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            8 |   1008 | dgfsa    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            9 |   1009 | xin      | 2019-11-28 17:05:29 | 2019-11-28 17:05:29 |
+--------------+--------+----------+---------------------+---------------------+
# 会话2 对该表加字段 执行DDL操作 发现DDL挂起
mysql> alter table student_tb add stu_age int after stu_name;
# 会话3 查询所有会话 发现发生MDL锁
mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| Id | User | Host      | db     | Command | Time | State                           | Info                                                  |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| 31 | root | localhost | testdb | Sleep   |  125 |                                 | NULL                                                  |
| 32 | root | localhost | testdb | Query   |    7 | Waiting for table metadata lock | alter table student_tb add stu_age int after stu_name |
| 33 | root | localhost | testdb | Query   |    0 | starting                        | show processlist                                      |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
# 会话3 查看metadata_locks表记录 发现student_tb表有MDL锁冲突
mysql> select * from performance_schema.metadata_locks;  
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| TABLE       | testdb             | student_tb     |        94189250717664 | SHARED_WRITE        | TRANSACTION   | GRANTED     |        |              56 |             34 |
| GLOBAL      | NULL               | NULL           |       139764477045472 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |              57 |             18 |
| SCHEMA      | testdb             | NULL           |       139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |              57 |             18 |
| TABLE       | testdb             | student_tb     |       139764477697904 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |              57 |             18 |
| TABLE       | testdb             | student_tb     |       139764477697696 | EXCLUSIVE           | TRANSACTION   | PENDING     |        |              57 |             18 |
| TABLE       | performance_schema | metadata_locks |       139764544135120 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              58 |             20 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
# 会话3 联合其他系统表 查找出会话ID
mysql> select m.*,t.PROCESSLIST_ID from performance_schema.metadata_locks m left join performance_schema.threads t on m.owner_thread_id=t.thread_id;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | PROCESSLIST_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
| TABLE       | testdb             | student_tb     |        94189250717664 | SHARED_WRITE        | TRANSACTION   | GRANTED     |        |              56 |             34 |             31 |
| GLOBAL      | NULL               | NULL           |       139764477045472 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |              57 |             18 |             32 |
| SCHEMA      | testdb             | NULL           |       139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |              57 |             18 |             32 |
| TABLE       | testdb             | student_tb     |       139764477697904 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |              57 |             18 |             32 |
| TABLE       | testdb             | student_tb     |       139764477697696 | EXCLUSIVE           | TRANSACTION   | PENDING     |        |              57 |             18 |             32 |
| TABLE       | performance_schema | metadata_locks |       139764544135120 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              58 |             22 |             33 |
| TABLE       | performance_schema | threads        |       139764549217280 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              58 |             22 |             33 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
# 结果解读：从上面结果明显可以看出会话31持有student_tb表的SHARED_WRITE锁，
# 需要等待其提交后或手动杀掉该会话方可解除MDL锁。


3.如何优化与避免MDL锁


MDL锁一旦发生会对业务造成极大影响，因为后续所有对该表的访问都会被阻塞，造成连接积压。我们日常要尽量避免MDL锁的发生，下面给出几点优化建议可供参考：


  • 开启metadata_locks表记录MDL锁。
  • 设置参数lock_wait_timeout为较小值，使被阻塞端主动停止。
  • 规范使用事务，及时提交事务，避免使用大事务。
  • 增强监控告警，及时发现MDL锁。
  • DDL操作及备份操作放在业务低峰期执行。
  • 少用工具开启事务进行查询，图形化工具要及时关闭。


总结：


本篇文章主要分三方面来详解MDL锁，首先介绍了MDL锁产生的原因及作用，然后我们模拟出MDL锁，并给出查找及解决方法，最后给出几点避免MDL锁的建议。其实，MDL锁在DB运维过程中经常遇到，它不是洪水猛兽，只是为了保护数据库对象，保证数据一致性。希望大家看完这篇文章后能对MDL锁有更清晰的认识。


参考：


文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
监控
运维
MySQL
数据库
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
MySQL技术
目录
相关文章
古城小白
|
2月前
锁记录
锁记录
古城小白
38 8
sky_qiyi
|
5月前
|
SQL 存储 关系型数据库
MySQL数据库——锁-表级锁（表锁、元数据锁、意向锁）
MySQL数据库——锁-表级锁（表锁、元数据锁、意向锁）
sky_qiyi
230 0
程序媛地瓜
|
SQL 数据库
初识MDL锁
初识MDL锁
程序媛地瓜
111 1
1910801116781953
|
关系型数据库 MySQL 数据库
Mysql 的全局锁和表级锁
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。行级锁比较复杂，这篇文章就先分享全局锁和表级锁。还有就是需要说明的是，锁的设计比较复杂，文章中基本不会出现锁的具体实现细节，主要介绍的是碰到锁时的现象和其背后的原理。
1910801116781953
144 0
Mysql 的全局锁和表级锁
mysql-1
|
6月前
|
SQL 运维 数据库
如何排查MDL锁
分享数据库故障处理的微信公众号:MySQL_DBA，欢迎关注，谢谢！
mysql-1
48 1
基层搬砖的Panda
|
6月前
|
SQL 关系型数据库 MySQL
Mysql锁之——全局锁、表锁
Mysql锁之——全局锁、表锁
基层搬砖的Panda
88 0
Rsun04551
|
6月前
|
数据库 数据安全/隐私保护
什么是行级锁和表级锁
什么是行级锁和表级锁
Rsun04551
120 0
.29.
|
6月前
|
SQL 关系型数据库 MySQL
⑩⑦【MySQL】锁：全局锁、表级锁、行级锁
⑩⑦【MySQL】锁：全局锁、表级锁、行级锁
.29.
79 0
Python知识大全
|
JSON 算法 安全
全局锁，锁住怎么办？？？
全局锁，锁住怎么办？？？
Python知识大全
78 0
wljslmz
|
SQL 存储 算法
MySQL的锁机制，包括锁分类、锁级别、锁粒度、锁冲突等方面
MySQL的锁机制，包括锁分类、锁级别、锁粒度、锁冲突等方面
wljslmz
157 0