服务器之家

服务器之家 > 正文

MySQL 实战笔记 第02期:MySQL 元数据锁

时间:2020-12-25 00:09     来源/作者:杨建荣的学习笔记

MySQL 实战笔记 第02期:MySQL 元数据锁

当我们在 MySQL 中执行 DDL 语句时,经常会发现语句没有在你预期的时间完成,这时候我们通常会使用 show full processlist ,来看看发生了什么状况。当你看到 waiting for table metadata lock 时,那就碰到元数据锁了。那元数据锁是怎样产生的又应该怎样避免呢?让我们从这篇文章开始了解它。

1、什么是元数据锁

MDL 全称为 metadata lock,即元数据锁,一般也可称为字典锁。MDL 的主要作用是为了管理数据库对象的并发访问和确保元数据一致性。元数据锁适用对象包含:table、schema、procedures, functions, triggers, scheduled events、tablespaces 。

2、加锁规则

获取规则:

  • 语句逐个( one by one )获取元数据锁,不是同时获取,并在获取过程中执行死锁检测。
  • DML 语句获取锁按照语句中 table 出现的顺序来获取锁。
  • DDL 语句、LOCK TABLES 和其他类似语句按名称顺序获取锁,对于隐式使用的表(例如外键关系中也必须锁定的表)可能会以不同的顺序获取锁。
  • DDL 的写锁请求优先级高于 DML

3、模拟加锁规则

两个相同表结构的表 t 和 t_new 开始。三个线程来操作这些表:

场景一

线程 1:

LOCK TABLE t WRITE, t_new WRITE; 

该语句按表名顺序在 t 和 t_new 上获取写锁

线程 2:

INSERT INTO t VALUES(1); 

该语句处于也需要获取表 t 上的 MDL 所以处于等待状态

线程 3:

RENAME TABLE t TO t_old, t_new TO t; 

该语句需要按表名顺序在 t 、t_new、t_old 上获取互斥锁,所以也处于等待状态

线程 1:

UNLOCK TABLES; 

该语句释放对 t 和 t_new 的写锁定。线程 3 对 t 加写锁的优先级高于 线程 2 ,因此线程 3 在 t 上优先获得互斥锁,然后依次在 t_new、t_old 上获取互斥锁,执行重命名后释放其锁定。线程 2 获得 t 上的写锁,执行插入操作,然后释放其锁定。rename 操作在 insert 之前执行。

场景二

两个具有相同表结构的表 t 和 new_t ,同样是三个线程来操作这些表

线程 1:

LOCK TABLE t WRITE, new_t WRITE; 

该语句按表名顺序在 new_t 和 t 上获取写锁

线程 2:

INSERT INTO t VALUES(1); 

该语句处于也需要获取表 t 上的 MDL 所以处于等待状态

线程 3:

RENAME TABLE t TO old_t, new_t TO t; 

该语句需要按表名顺序在 new_t 、old_t、t 上获取互斥锁,所以也处于等待状态

该语句释放对 t 和 new_t 的写锁定。对于 t 首先发起锁请求的是线程 2 ,因此线程 2 优先获得了 t 上的元数据写锁,执行完插入操作,然后释放该锁。线程 3 首先获取的是 new_t 、old_t 的互斥锁,最后才会请求 t 上的互斥锁,所以线程 3 在线程 2 执行完毕之前都是处于等待状态的。rename 操作在 insert 操作之后。

4、 如何监控元数据锁

performance_schema.metadata_locks 表中记录了元数据锁相关的信息,开启方式如下:在线开启 metadata_locks,操作如下:

--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'

若可停库维护,则在 my.cnf 中添加如下:

[mysqld] 

performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

5、如何优化元数据锁

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

  • 开启 metadata_locks 表记录 MDL 锁。
  • 设置参数 lock_wait_timeout 为较小值,使被阻塞端主动停止。
  • 规范使用事务,及时提交事务,避免使用大事务。
  • 增强监控告警,及时发现 MDL 锁。
  • DDL 操作及备份操作放在业务低峰期执行。 

原文地址:https://mp.weixin.qq.com/s/nwNjlxsff82Bwq0FspZDfw

标签:

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址 2020-08-12
返回顶部