MySQL启用跟踪MDL(元数据锁)功能

 

MySQL启用跟踪MDL(元数据锁)功能

 

MDL锁:全称为meta data lock， 中文叫元数据锁，是从MySQL5.5开始引入的锁，是为了解决DDL操作和DML操作之间操作一致性。从锁的作用范围上来说，MDL算是一种表级锁，是一个server层的锁。

其实MDL加锁过程是系统自动控制，无法直接干预，也不需要直接干预，当我们对一个表做增删改查操作的时候，会自动加MDL读锁；当我们要更新表结构的时候，加MDL写锁。加读锁则所有线程可正常读表的元数据，并且读锁不影响表的增删改查操作，只是不能修改表结构；而加写锁只有拥有锁的线程可以读写元数据，即只拥有锁的线程才能更新表结构，其他线程不能修改结构也不能执行相应的增删改查。

即MDL中，读读共享，读写互斥，写写互斥。

出自：传送门

官方文档介绍：https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html

防爬虫：https://www.cnblogs.com/PiscesCanon/p/17301430.html 

 

有时候"show processlist"能看到 Waiting for table metadata lock 状态的会话，此状态意味着查询正在等待新类型的表锁：元数据锁 (MDL)。

这样的锁在第一次访问表时获取，并在查询完成执行或事务（如果使用）关闭时释放。MDL可以通过SELECT、DML（ Data Manipulation Language，特别是UPDATE、INSERT、DELETE）或DDL（Data Definition Language）查询来设置。它还会影响非事务表，例如使用MyISAM存储引擎的表。

 

如何找出持有锁的会话？

可以依靠性能模式来隔离长时间运行的事务并了解正在发生的事情：事务可能挂在一个很长的查询上，或者编码错误可能忘记提交显式事务。

为了跟踪MDL，请确保启用了正确的工具： （在我的MySQL8中，默认是启用的，MySQL5中默认是禁用的。）

8.0.30版本：
(root@localhost 23:10:15) [(none)]> select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+------------+------------+---------------+
| NAME                       | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION |
+----------------------------+---------+-------+------------+------------+---------------+
| wait/lock/metadata/sql/mdl | YES     | YES   |            |          0 | NULL          |
+----------------------------+---------+-------+------------+------------+---------------+
1 row in set (1.39 sec)

5.7.30版本：
mysql> select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | NO      | NO    |
+----------------------------+---------+-------+
1 row in set (0.00 sec)

 

 

开启方式1（重启失效）：

来源：文档 ID 2102004.1

mysql> update performance_schema.setup_instruments set enabled='yes', timed='yes' where name = 'wait/lock/metadata/sql/mdl';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--该视图可以不用commit
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | YES     | YES   |
+----------------------------+---------+-------+
1 row in set (0.00 sec)

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       | performance_schema | setup_instruments |       139744478585792 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              45 |             30 |
| TABLE       | performance_schema | metadata_locks    |       139744478603296 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              45 |             32 |
| TABLE       | zkm                | test              |       139744478570080 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              45 |             33 |
| GLOBAL      | NULL               | NULL              |       139744143008352 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |              47 |              8 |
| SCHEMA      | zkm                | NULL              |       139744143018304 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |              47 |              8 |
| TABLE       | zkm                | test              |       139744143025760 | EXCLUSIVE           | TRANSACTION   | PENDING     |        |              47 |              8 |
+-------------+--------------------+-------------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
6 rows in set (0.00 sec)

 

开启方式2（重启生效）：

来源：文档 ID 1473704.1

[mysqld]
performance_schema_instrument                              = wait/lock/metadata/sql/mdl=ON

 

可以同时采用两种方式，达到无需重启永久生效的效果。

 

以下SQL脚本可以查询当前正在连接会话持有MDL锁的情况，可以根据需要加条件(文档 ID 1365549.1)。

SELECT ps.*,lock_summary.lock_summary
FROM sys.processlist ps
INNER JOIN(
SELECT owner_thread_id, group_concat( DISTINCT concat(mdl.lock_status, ' ', mdl.lock_type, ' on ',IF(mdl.object_type='USER LEVEL LOCK', Concat(mdl.object_name, ' (user lock)'), Concat(mdl.object_schema, '.', mdl.object_name))) ORDER BY mdl.object_type ASC, mdl.lock_status ASC, mdl.lock_type ASC separator '\n' ) AS lock_summary
FROM performance_schema.metadata_locks mdl
GROUP BY owner_thread_id) lock_summary
ON (ps.thd_id=lock_summary.owner_thread_id)\G

 

 

 

参考文档：

https://blog.csdn.net/weixin_43189971/article/details/126436023

Metadata Locking -- Which Connection is Holding on to the Lock? (文档 ID 1473704.1)

How to Retrieve Table Level or Global Read Lock Status from Running MySQL Server (文档 ID 2102004.1)

What Does Thread Status "Waiting for table metadata lock" in the MySQL Server Processlist Mean? (文档 ID 1365549.1)