有爱有恨的MDL锁

导读

作者:田帅萌 邮箱:tplinux@163.com,欢迎交流

一、前言

MySQL 5.5 中就引入了metadata lock(元数据锁)。用于对管理 database objects(数据库对象)的并发访问,保证数据的一致性。

二、了解MDL锁

1、 MDL锁消耗

MDL的引入会导致一定的性能的损耗,对同一个database objects的访问越多,就会导致该对象的MDL的争用。

2、了解MDL锁

为了维护表元数据的数据一致性,在表上有活动事务(显示或者隐式)的时候,不可以对元数据进行写入操作,MySQL引入了metadata lock,来保护表的元数据信息。因此在对表进行上述操作时,如果表上有活动事务(未提交或回滚),请求写入的会话,会等待在metadata lock wait。

三、什么场景会导致MDL?

场景一:

session1

session2

begin

null

select * from t

null

null

alter table t xx

|  6 | root        
| localhost | d1   
| Query   |  144 
| Waiting for table metadata lock                        
| alter table t1 ENGINE=InnoDB |

当session1 开启事务没有提交对t表进行查询,同时session2 对t表进行alter 修改。则会造成MDL锁

场景二:

session1

session2

begin

null

select * from t

null

null

drop table t

当session1开启事务没有提交对t表进行查询,同时session2 对t表进行dorp操作。则会造成MDL锁;

|  6 | root        
| localhost | d1   
| Query   |    6 
| Waiting for table metadata lock                        
| drop table t1

那些操作会获得metadata lock

1.表结构的更改(alter)

2.创建删除索引

3.删除表

4.对表加写锁,进行读操作。对表加读锁,进行写操作。

四、恨MDL

因为MDL锁,会导致表级别的锁,无论是读或者写操作,都无法进行,导致SQL的阻塞。 

如监控不到位,在高并发的情况下,就会造成大量的SQL阻塞。除非人工干预或者ddl结束。

五、爱MDL

MDL的主要目的是为了保护元数据,假如、假如没有MDL锁,会导致什么 读到的元数据不一致,写入的时候发生元数据冲突。 MDL不是洪水猛兽,不是可怕灾难,是有办法避免的。例如DDL可以在业务低峰期使用pt-osc进行操作或者gh-ost,来进行操作。备份也要放到业务低峰期去做,当然也要做好监控和报警,对于未提交的事务要尽快提交,或者kill到没有commit的事务。

六、如何监控MDL

1、MySQL 5.5:

select * from information_schema.processlist where state = 'Waiting for table metadata lock';

2、MySQL5.6:

select * from performance_schema.metadata_locks;

#需要开启performance_schema:

root@mysqldb 10:03:  [performance_schema]> update performance_schema.setup_instruments set ENABLED = 'YES',TIMED='YES' where name = 'wait/lock/metadata/sql/mdl';
root@mysqldb 10:02:  [performance_schema]> update performance_schema.setup_consumers set ENABLED = 'YES' where name in ('global_instrumentation');

3、MySQL5.7

select * from sys.schema_table_lock_waits

七、处理MDL造成的阻塞

1、第一种情况:处理掉长事务

(1) session1的事务没有执行完毕,则kill session2 可以通过下面的SQL进行观察,可以自行评估:

root@mysqldb 10:17:  [information_schema]> select * from information_schema.INNODB_TRX\G

(2) session1的事务没有提交,后面的事务没有对该表进行操作,则kill session1。

2、第二种情况:直接干掉DDL操作

则kill session1 选择业务低峰期执行 如果DDL执行到80%到90%。则kill session2 优先执行DDL 需要根据实际情况自行执行。

八、总结

MDL是保护数据库对象,保证数据一致性。MDL不是洪水猛兽,DDL和备份需要跟业务方沟通后,在业务低峰期去执行,不要给开发DDL权限哦~ 做对MySQL的监控信息包括锁信息或者死锁信息。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏MongoDB中文社区

MongoDB事务模型分析

在了解写操作的事务性之前,需要先了解mongo层的每一个table,是如何与wiredtiger层的table(btree)对应的。mongo层一个最简单的ta...

2142
来自专栏MySQL实战分享

MongoDB第二期:压缩与索引

MongoDB-3.2中 WiredTiger 存储引擎引入了压缩功能,出色的压缩了海量数据的存储空间。MongoDB很好的优化了建立索引的机制,对于海量数据,...

1.7K2
来自专栏码农阿宇

利用EF Core的Join进行多表查询

背景 话说有这么一家子,老公养了一条狗,老婆养了一只猫。 数据库的设计 人表 ? 宠物表 ? 通过表可以知道,宠物通过Owner指向主人的Id。 问题来了,我要...

3437
来自专栏hadoop学习笔记

pyhanlp用户自定义词典添加实例说明

pyhanlp是python版封装的的HanLP,项目地址:https://github.com/hankcs/pyhanlp

930
来自专栏极客慕白的成长之路

数据库系统概述必背知识点整理

1332
来自专栏小文章

使用mysql事务不同场景导致的死锁问题以及解决方法

MySQL各存储引擎使用了三种级别的锁定机制:table-level(表级锁定),row-level(行级锁定)和page-level(页级...

1544
来自专栏程序员的SOD蜜

让DBA去写数据访问层(DAL)代码--PDF.NET数据开发框架之SQL-MAP使用图解

面对一堆堆的复杂SQL语句,你需要把它写成.NET程序,是不是有点厌烦了? 我们的DAL代码和具体的数据库,SQL语句紧密耦合,有没有办法分离开? 如果你有一...

2176
来自专栏PHP在线

总结

1.安装完成后备份快照 2.不插网线使用虚拟机,查看vmware的IP网段,设置linux系统相同的网段。 3.rpm -qa 软件名字 //查询软件是...

3205
来自专栏PHP在线

MYSQL性能优化分享(分库分表)

MYSQL性能优化之分库分表与不停机修改mysql表结构,需要的朋友可以参考下 1、分库分表 很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严...

3915
来自专栏数据和云

【合理授权,安全第一】聊一聊Oracle数据库的用户权限

编辑手记:年底大家最关注数据安全,之前我们说过,数据库的风险分为外部风险和内部风险。外部风险无法预估但概率较小,平时发生最多的还是内部操作的风险,因此合理控制权...

2675

扫码关注云+社区

领取腾讯云代金券