详细分析mysql MDL元数据锁

吾爱主题阅读：299 2024-04-05 16:22:12 评论：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开启：

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 # 会话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锁。