mysql的元数据锁：metadata_locks
#############################
metadata lock是表级锁，是在server层加的，适用于所有存储引擎。所有的dml操作都会在表上加一个metadata读锁；所有的ddl操作都会在表上加一个metadata写锁。读锁和写锁的阻塞关系如下：
- 读锁和写锁之间相互阻塞，即同一个表上的dml和ddl之间互相阻塞。
- 写锁和写锁之间互相阻塞，即两个session不能对表同时做表定义变更，需要串行操作。
- 读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞，可以并发执行，日常工作中大家看到的dml之间的锁等待是innodb行锁引起的，和metadata lock无关。
熟悉innodb行锁的同学这里可能有点困惑，因为行锁分类和metadata lock很类似，也主要分为读锁和写锁，或者叫共享锁和排他锁，读写锁之间阻塞关系也一致。二者最重要的区别一个是表锁，一个是行锁，且行锁中的读写操作对应在metadata lock中都属于读锁。
作者：京东云开发者
链接：https://juejin.cn/post/7209319092514209853
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
链接：https://juejin.cn/post/7209319092514209853
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
简介： 当你在MySQL中执行一条SQL时，语句并没有在你预期的时间内执行完成，这时候我们通常会登陆到MySQL数据库上查看是不是出了什么问题，通常会使用的一个命令就是 show processlist，看看有哪些session，这些session在做什么事情。当你看到 waiting for table metadata lock 时，那就是遇到MDL元数据锁了。本篇文章将会介绍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';
# 会话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锁。
如何优化与避免MDL锁
MDL锁一旦发生会对业务造成极大影响，因为后续所有对该表的访问都会被阻塞，造成连接积压。我们日常要尽量避免MDL锁的发生，下面给出几点优化建议可供参考：
- 开启metadata_locks表记录MDL锁。
- 设置参数lock_wait_timeout为较小值，使被阻塞端主动停止。
- 规范使用事务，及时提交事务，避免使用大事务。
- 增强监控告警，及时发现MDL锁。
- DDL操作及备份操作放在业务低峰期执行。
- 少用工具开启事务进行查询，图形化工具要及时关闭。
快速解决问题永远是第一位的，一旦出现长时间的metadata lock，尤其是在访问频繁的业务表上产生，通常会导致表无法访问，读写全被阻塞，此时找到阻塞源头是第一位的。这里最重要的表就是前面提到过的
performance_schema.metadata_locks表。
metadata_locks是5.7中被引入，记录了metadata lock的相关信息，包括持有对象、类型、状态等信息。但5.7默认设置是关闭的（8.0默认打开），需要通过下面命令打开设置：
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'wait/lock/metadata/sql/mdl';
如果要永久生效，需要在配置文件中加入如下内容：
[mysqld] performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
单纯查询这个表无法得出具体的阻塞关系，也无法得知什么语句造成的阻塞，这里要关联另外两个表performance_schema.thread和
performance_schema.events_statements_history,thread表可以将线程id和show processlist中id关联，events_statements_history表可以得到事务的历史sql，关联后的完整sql如下：
performance_schema.events_statements_history,thread表可以将线程id和show processlist中id关联，events_statements_history表可以得到事务的历史sql，关联后的完整sql如下：
SELECT locked_schema, locked_table, locked_type, waiting_processlist_id, waiting_age, waiting_query, waiting_state, blocking_processlist_id, blocking_age, substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query, sql_kill_blocking_connection FROM ( SELECT b.OWNER_THREAD_ID AS granted_thread_id, a.OBJECT_SCHEMA AS locked_schema, a.OBJECT_NAME AS locked_table, "Metadata Lock" AS locked_type, c.PROCESSLIST_ID AS waiting_processlist_id, c.PROCESSLIST_TIME AS waiting_age, c.PROCESSLIST_INFO AS waiting_query, c.PROCESSLIST_STATE AS waiting_state, d.PROCESSLIST_ID AS blocking_processlist_id, d.PROCESSLIST_TIME AS blocking_age, d.PROCESSLIST_INFO AS blocking_query, concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection FROM performance_schema.metadata_locks a JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA AND a.OBJECT_NAME = b.OBJECT_NAME AND a.lock_status = 'PENDING' AND b.lock_status = 'GRANTED' AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID AND a.lock_type = 'EXCLUSIVE' JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID ) t1, ( SELECT thread_id, group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text FROM performance_schema.events_statements_history GROUP BY thread_id ) t2 WHERE t1.granted_thread_id = t2.thread_id \G
对于前面的例子执行此sql，得到一个清晰的阻塞关系：
locked_schema: db1 locked_table: t1 locked_type: Metadata Lock waiting_processlist_id: 28 waiting_age: 227 waiting_query: alter table t1 add cl3 int waiting_state: Waiting for table metadata lock blocking_processlist_id: 27 blocking_age: 252 blocking_query: select * from t1 sql_kill_blocking_connection: KILL 27 1 row in set, 1 warning (0.00 sec)
根据显示结果，processlist_id为27的线程阻塞了28的线程，我们需要kill 27即可解锁。
实际上，MySQL也提供了一个类似的视图来解决metadata lock问题，视图名称为sys.schema_table_lock_waits，但此视图查询结果有bug，不是很准确，建议大家还是参考上面sql。
为了尽可能避免类似问题，下面是几个小建议：
- 生产环境的任何大表或频繁操作的小表，ddl都要非常慎重，最好在业务低峰期执行。
- 设计上要尽可能避免大事务，大事务不仅仅会带来各种锁问题，还好引起复制延迟/回滚空间爆满等各类问题。
- 要及时提交事务，经常发现客户端设置了事务手工提交，但sql执行后忘记点击提交按钮，导致事务长时间无法提交。建议监控实例中的长事务，避免由于各种原因导致事务没有及时提交。
###################################
igoodful@qq.com
