前言:
当你在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锁有更清晰的认识。
以上就是详细分析mysql mdl元数据锁的详细内容,更多关于mysql mdl元数据锁的资料请关注www.887551.com其它相关文章!