锐客网

  1. 首页 > 睿知 > it技术 > >

MySQL中找出谁持有MDL锁

IT知识mysql

一年好景君须记，最是橙黄橘绿时。这篇文章主要讲述MySQL中找出谁持有MDL锁相关的知识，希望能为你提供帮助。
一、MDL锁的介绍

mysql 5.7版本之前并没有提供一个方便的途径来查看MDL锁，github上有一名为mysql-plugin-mdl-info的项目，通过插件的方式来查看，于是在MySQL 5.7中的performance_schea库下新增了一张表metadata_locks，用其来查看MDL锁那是相当的方便:


不过默认PS并没有打开此功能，需要手工将wait/lock/metadata/sql/mdl监控给打开:
?

?
UPDATE performance_schema.setup_consumers SET ENABLED = \'YES\' WHERE NAME =\'global_instrumentation\';

UPDATE performance_schema.setup_instruments SET ENABLED = \'YES\' WHERE NAME =\'wait/lock/metadata/sql/mdl\';



目前MDL有如下锁模式：

锁模式对应SQL
MDL_INTENTION_EXCLUSIVEGLOBAL对象、SCHEMA对象操作会加此锁
MDL_SHAREDFLUSH TABLES with READ LOCK
MDL_SHARED_HIGH_PRIO仅对MyISAM存储引擎有效
MDL_SHARED_READSELECT查询
MDL_SHARED_WRITEDML语句
MDL_SHARED_WRITE_LOW_PRIO仅对MyISAM存储引擎有效
MDL_SHARED_UPGRADABLEALTER TABLE
MDL_SHARED_READ_ONLYLOCK xxx READ
MDL_SHARED_NO_WRITEFLUSH TABLES xxx,yyy,zzz READ
MDL_SHARED_NO_READ_WRITEFLUSH TABLE xxx WRITE
MDL_EXCLUSIVEALTER TABLE xxx PARTITION BY …

?
?
root@tidb06 08:33:[test001]> 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)

root@tidb06 08:44:[test001]> select * fromperformance_schema.setup_consumerswhere NAME =\'global_instrumentation\';
+------------------------+---------+
| NAME| ENABLED |
+------------------------+---------+
| global_instrumentation | YES|
+------------------------+---------+
1 row in set (0.00 sec)



二、模拟MDL锁

演示环境:mysql5.7.22  
为了演示 下面参数设置时间如下:
?


innodb_lock_wait_timeout=600
interactive_timeout= 600
wait_timeout=600



创建测试表和插入测试数据:



CREATE TABLE `test001` (

`id` int(8) NOT NULL AUTO_INCREMENT,

`username` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,

`password` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,

`create_time` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


insert into test001(username,password,create_time)value(\'小花\',\'abc123\',now());
insert into test001(username,password,create_time)value(\'王五\',\'ccc123\',now());



 
会话1 开启事务更新1条sql:


\'tidb03\' root@localhost 09:04:56 test001> begin;

Query OK, 0 rows affected (0.00 sec)



\'tidb03\' root@localhost 09:05:03 test001> update test001 set username=\'zhangsan\' where id=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1Changed: 1Warnings: 0






会话2   更新同一个记录: 产生行锁等待
\'tidb03\' root@localhost 12:40:02 test001> update test001 set username=\'王五\' where id=2;



会话3   给test001表添加索引 : 产生DML表锁等待

?
\'tidb03\' root@localhost 12:42:36 test001> alter table test001 add index idx_username(username);





【MySQL中找出谁持有MDL锁】会话4   给test001表删除id=2的记录数:同样产生MDL锁等待
\'tidb03\' root@localhost 12:42:42 (none)> show full processlist;

+--------+------+-----------------+---------+---------+------+---------------------------------+------------------------------------------------------+

| Id| User | Host| db| Command | Time | State| Info|

+--------+------+-----------------+---------+---------+------+---------------------------------+------------------------------------------------------+

| 134004 | root | localhost:40418 | test001 | Sleep|111 || NULL|

| 134122 | root | localhost:40806 | test001 | Query|89 | updating| update test001 set username=\'王五\' where id=2|

| 134336 | root | localhost:41542 | test001 | Query|14 | Waiting for table metadata lock | alter table test001 add index idx_username(username) |

| 134384 | root | localhost

    推荐阅读


    上一篇：SpringCloud怎么使用Nacos做注册中心+配置中心（）

    下一篇：Redis核心原理与实践--字符串实现原理