详细分析mysql MDL元数据锁

前言:

当你在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锁。

详细分析mysql MDL元数据锁

扫一扫手机访问