深入理解MDL元数据锁

矫情吗；* 2023-06-19 11:09 22阅读 0赞

**前言：**

当你在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锁有更清晰的认识。

**参考：**

*  [https://blog.csdn.net/wanbf123/article/details/80697787][https_blog.csdn.net_wanbf123_article_details_80697787]
 *  [https://blog.csdn.net/finalkof1983/article/details/88063328][https_blog.csdn.net_finalkof1983_article_details_88063328]
 *  [https://www.jianshu.com/p/c2efdde99e2f][https_www.jianshu.com_p_c2efdde99e2f]

![公众号.jpg][.jpg]

[https_blog.csdn.net_wanbf123_article_details_80697787]: https://blog.csdn.net/wanbf123/article/details/80697787
[https_blog.csdn.net_finalkof1983_article_details_88063328]: https://blog.csdn.net/finalkof1983/article/details/88063328
[https_www.jianshu.com_p_c2efdde99e2f]: https://www.jianshu.com/p/c2efdde99e2f
[.jpg]: https://img-blog.csdnimg.cn/20191206101917823.jpeg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdrdW5fag==,size_16,color_FFFFFF,t_70