前言：

当你在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_instrumentsSET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'wait/lock/metadata/sql/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 联合其他系统表 查找出会话IDmysql> 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锁。

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

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

总结：

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

更多相关文章

  1. mybatisplus的坑 insert标签insert into select无参数问题的解决
  2. Pycharm安装PyQt5的详细教程
  3. python起点网月票榜字体反爬案例
  4. 《Android开发从零开始》——25.数据存储(4)
  5. Android系统配置数据库注释(settings.db)
  6. Android中不同应用间实现SharedPreferences数据共享
  7. android图表ichartjs
  8. android 分辨率及密度详细
  9. Android内容提供者源码

随机推荐

  1. c语言数据类型转换的方法
  2. c语言中“或”怎么表示？
  3. c语言怎么实现动态内存分配
  4. c语言二进制如何表示
  5. c语言中的关键字有哪些类型？
  6. c语言中long是什么意思
  7. c语言0x什么意思
  8. printf在c语言中什么意思
  9. c语言中void的含义
  10. c语言的基本组成单位是什么