MySQL MDL锁

十毛

发布于 2021-02-01 11:58:16

1.2K0

MDL全称为metadata lock，即元数据锁。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁，来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。

模拟和定位MDL锁

表结构

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

会话模拟

session A	session B
lock table t write
	select * from t where id =1

session A 通过 lock table 命令持有表 t 的 MDL 写锁，而 session B 的查询需要获取 MDL 读锁。所以，session B 进入等待状态。

mysql> select * from processlist;
+------+--------+-----------+--------+---------+------+---------------------------------+---------------------------+
| ID   | USER   | HOST      | DB     | COMMAND | TIME | STATE                           | INFO                      |
+------+--------+-----------+--------+---------+------+---------------------------------+---------------------------+
| 3582 | tenmao | localhost | tenmao | Sleep   |  115 |                                 | NULL                      |
| 3583 | tenmao | localhost | tenmao | Query   |   97 | Waiting for table metadata lock | select * from t where t=1 |
+------+--------+-----------+--------+---------+------+---------------------------------+---------------------------+

冲突解决

这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉。但是，由于在 show processlist 的结果里面，session A 的 Command 列是“Sleep”，导致查找起来很不方便。不过有了 performance_schema 和 sys 系统库以后，就方便多了。（MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失)通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

mysql> SELECT blocking_pid FROM sys.schema_table_lock_waits;
+--------------+
| blocking_pid |
+--------------+
|         3582 |
+--------------+

如何优化与避免MDL锁

MDL锁一旦发生会对业务造成极大影响，因为后续所有对该表的访问都会被阻塞，造成连接积压。我们日常要尽量避免MDL锁的发生，下面给出几点优化建议可供参考：

开启metadata_locks表记录MDL锁。
设置参数lockwaittimeout为较小值，使被阻塞端主动停止。
规范使用事务，及时提交事务，避免使用大事务。
增强监控告警，及时发现MDL锁。
DDL操作及备份操作放在业务低峰期执行。
少用工具开启事务进行查询，图形化工具要及时关闭。

参考

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

云数据库 SQL Server

sql

html

MySQL MDL锁

MySQL MDL锁

模拟和定位MDL锁

冲突解决

如何优化与避免MDL锁

参考

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐