详细分析mysql MDL元数据锁

2023-07-19 04:26:03 178

前言：

当你在MySQL中执行一条SQL时，语句并没有在你预期的时间内执行完成，这时候我们通常会登陆到MySQL数据库上查看是不是出了什么问题，通常会使用的一个命令就是showprocesslist，看看有哪些session，这些session在做什么事情。当你看到waitingfortablemetadatalock时，那就是遇到MDL元数据锁了。本篇文章将会介绍MDL锁的产生与排查过程。

1.什么是MDL锁

MDL全称为metadatalock，即元数据锁。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引擎表，都会出现MetadataLockWait等待现象。一旦出现MetadataLockWait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

2.模拟与查找MDL锁

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

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

UPDATEperformance_schema.setup_instruments
SETENABLED='YES',TIMED='YES'
WHERENAME='wait/lock/metadata/sql/mdl';

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

#会话1事务中执行DML操作
mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>insertintostudent_tb(stu_id,stu_name)values(1009,'xin');
QueryOK,1rowaffected(0.00sec)

mysql>select*fromstudent_tb;
+--------------+--------+----------+---------------------+---------------------+
|increment_id|stu_id|stu_name|create_time|update_time|
+--------------+--------+----------+---------------------+---------------------+
|1|1001|from1|2019-11-2816:36:14|2019-11-2816:36:14|
|2|1002|dfsfd|2019-11-2816:36:14|2019-11-2816:36:14|
|3|1003|fdgfg|2019-11-2816:36:14|2019-11-2816:36:14|
|4|1004|sdfsdf|2019-11-2816:36:14|2019-11-2816:36:14|
|5|1005|dsfsdg|2019-11-2816:36:14|2019-11-2816:36:14|
|6|1006|fgd|2019-11-2816:36:14|2019-11-2816:36:14|
|7|1007|fgds|2019-11-2816:36:14|2019-11-2816:36:14|
|8|1008|dgfsa|2019-11-2816:36:14|2019-11-2816:36:14|
|9|1009|xin|2019-11-2817:05:29|2019-11-2817:05:29|
+--------------+--------+----------+---------------------+---------------------+

#会话2对该表加字段执行DDL操作发现DDL挂起
mysql>altertablestudent_tbaddstu_ageintafterstu_name;

#会话3查询所有会话发现发生MDL锁
mysql>showprocesslist;
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
|Id|User|Host|db|Command|Time|State|Info|
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
|31|root|localhost|testdb|Sleep|125||NULL|
|32|root|localhost|testdb|Query|7|Waitingfortablemetadatalock|altertablestudent_tbaddstu_ageintafterstu_name|
|33|root|localhost|testdb|Query|0|starting|showprocesslist|
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+

#会话3查看metadata_locks表记录发现student_tb表有MDL锁冲突
mysql>select*fromperformance_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>selectm.*,t.PROCESSLIST_IDfromperformance_schema.metadata_locksmleftjoinperformance_schema.threadstonm.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锁有更清晰的认识。

以上就是详细分析mysqlMDL元数据锁的详细内容，更多关于mysqlMDL元数据锁的资料请关注毛票票其它相关文章！

详细分析mysql MDL元数据锁

热门推荐

随机推荐