MySQL MDL锁
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 Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。