MySQL的MDL元数据锁

最新推荐文章于 2024-03-05 14:54:09 发布

bohu83

最新推荐文章于 2024-03-05 14:54:09 发布

阅读量5.4k

点赞数 1

分类专栏：数据库 MYSQL 文章标签： MDL Metadata Locking

本文链接：https://blog.csdn.net/bohu83/article/details/82493737

版权

数据库同时被 2 个专栏收录

87 篇文章 13 订阅

订阅专栏

MYSQL

84 篇文章 23 订阅

订阅专栏

一序

本来在看mysql.taobao介绍InnoDB 事务子系统介绍，真的头大，名词都看不懂，还是分开来学习的好，本文主要整理MDL（metadata locking）锁。作为尝试学习人新人，发现困惑很多，应该是没有买本书系统的去看的事，只是突然的去看技术博客。官网的文档介绍的比较少，英语不好。所以我自己觉得有几个层次，：有个功能干啥的，怎么配置参数或者使用，能看源码明白怎么实现的，最后是明白为啥这样设计，不然就部分代码觉得好长啊。不忘初心，对于产品型的内核团队，会修复bug提升性能，对于RD还是开发会写高效SQL与设计表方案。

先看官网介绍：

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas, stored programs (procedures, functions, triggers, and scheduled events), and tablespaces.

Metadata locking does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.

Metadata locking is not a replacement for the table definition cache, and its mutexes and locks differ from the LOCK_open mutex. The following discussion provides some information about how metadata locking works.

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

This principle applies not only to transactional tables, but also to nontransactional tables. Suppose that a session begins a transaction that uses transactional table t and nontransactional table nt as follows:

START TRANSACTION;
SELECT * FROM t;
SELECT * FROM nt;

The server holds metadata locks on both t and nt until the transaction ends. If another session attempts a DDL or write lock operation on either table, it blocks until metadata lock release at transaction end. For example, a second session blocks if it attempts any of these operations:

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;

The same behavior applies for The LOCK TABLES ... READ. That is, explicitly or implicitly started transactions that update any table (transactional or nontransactional) will block and be blocked by LOCK TABLES ... READ for that table.

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

In autocommit mode, each statement is in effect a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.

Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.

The Performance Schema metadata_locks table exposes metadata lock information, which can be useful for seeing which sessions hold locks, are blocked waiting for locks, and so forth. For details, see Section 25.11.12.1, “The metadata_locks Table”.

二 MDL锁的作用

为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。
对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。
所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在Metadata lock wait 。

会话1	会话2
BEGIN;
SELECT * FROM XXX
	DROP TABLE XXX
SELECT * FROM XXX

若没有MDL锁的保护，则事务2可以直接执行DDL操作，并且导致事务1出错，5.1版本即是如此。5.5版本加入MDL锁就在于保护这种情况的发生，由于事务1开启了查询，那么获得了MDL锁，锁的模式为SHARED_READ，事务2要执行DDL，则需获得EXCLUSIVE锁，两者互斥，所以事务2需要等待。

三、MDL锁类型说明

由于MySQL是Server-Engine架构，尽管InnoDB层已经有了IS、IX这样的意向锁，所以MDL锁是在Server中实现。另外，MDL锁还能实现其他粒度级别的锁，比如全局锁、库级别的锁、表空间级别的锁，这是InnoDB存储引擎层不能直接实现的锁。
但与InnoDB锁的实现一样，MDL锁也是类似对一颗树的各个对象从上至下进行加锁（对树进行加锁具体见：《MySQL技术内幕：InnoDB存储引擎》）。但是MDL锁对象的层次更多，简单来看有如下的层次：

上图中显示了最常见的4种MDL锁的对象，并且注明了常见的SQL语句会触发的锁。与InnoDB层类似的是，某些类型的MDL锁会从上往下一层层进行加锁。比如LOCK TABLE … WRITE这样的SQL语句，其首先会对GLOBAL级别加INTENTION_EXCLUSIVE锁，再对SCHEMA级别加INTENTION_EXCLUSIVE锁，最后对TABLE级别加SHARED_NO_READ_WRITE锁。
Metadata Lock既然也是一种锁，所以每个MDL都会定义锁住的对象，锁的持有时间和锁的类型。

3.1 按照对象/范围划分

属性	含义	范围/对象
GLOBAL	全局锁	范围
COMMIT	提交保护锁	范围
SCHEMA	库锁	对象
TABLE	表锁	对象
FUNCTION	函数锁	对象
PROCEDURE	存储过程锁	对象
TRIGGER	触发器锁	对象
EVENT	事件锁	对象

MDL按锁住的对象来分类，可以分为global，commit，schema，table，function，procedure，trigger，event

3.2按照锁的持有时间

属性	含义
MDL_STATEMENT	从语句开始执行时获取，到语句执行结束时释放。
MDL_TRANSACTION	在一个事务中涉及所有表获取MDL，一直到事务commit或者rollback(线程中终清理)才释放。
MDL_EXPLICIT	需要MDL_context::release_lock()显式释放。语句或者事务结束,也仍然持有，如Lock table, flush .. with lock语句等。

3.3 按照操作的对象

数据库锁一般将锁划分为读锁(共享锁)和写锁(排它锁)，为了进一步提高并发性，还会加入意向共享锁和意向排它锁。看了下面的类型才知道，mysql搞得更复杂，应该是为了提升性能才这样设计的吧。

目前MDL有如下锁模式，锁之间的兼容性可见源码mdl.cc：blob/master/sql/mdl.cc

锁模式	对应SQL
MDL_INTENTION_EXCLUSIVE	意向排他锁 GLOBAL对象、SCHEMA对象操作会加此锁
MDL_SHARED	只访问元数据比如表结构 FLUSH TABLES with READ LOCK
MDL_SHARED_HIGH_PRIO	仅对MyISAM存储引擎有效，用于访问information_scheam表
MDL_SHARED_READ	SELECT查询访问表结构并且读表数据
MDL_SHARED_WRITE	DML语句访问表结构并且写表数据
MDL_SHARED_WRITE_LOW_PRIO	仅对MyISAM存储引擎有效
MDL_SHARED_UPGRADABLE	ALTER TABLE 是mysql5.6引入的新的metadata lock, 在alter table/create index/drop index会加该锁。特点是允许DML，防止DDL；
MDL_SHARED_READ_ONLY	LOCK xxx READ
MDL_SHARED_NO_WRITE	FLUSH TABLES xxx,yyy,zzz READ 可升级锁，访问表结构并且读写表数据，并且禁止其它事务写。
MDL_SHARED_NO_READ_WRITE	FLUSH TABLE xxx WRITE 可升级锁，访问表结构并且读写表数据，并且禁止其它事务读写。
MDL_EXCLUSIVE	ALTER TABLE xxx PARTITION BY … 防止其他线程读写元数据

关于global对象，主要作用是防止DDL和写操作的过程中，执行set golbal_read_only = on或flush tables with read lock。
关于commit对象锁，主要作用是执行flush tables with read lock后，防止已经开始在执行的写事务提交。insert/update/delete在提交时都会上（COMMIT，MDL_EXPLICIT，MDL_INTENTION_EXCLUSIVE）锁。

四 MDL常见场景

作为RD，很少会遇到MDL的，一般不关注，遇到是出问题看看，比如alter table的时候阻塞了。

a)当前有执行DML操作时执行ALTRE操作。

b）当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住。
b）当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住。
d）表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住。

select 与alter是否会相互阻塞：当执行select语句时，只要select语句在获取MDL_SHARED_READ锁之前，alter没有执行到rename阶段，那么select获取MDL_SHARED_READ锁成功，后续有alter执行到rename阶段，请求MDL_EXCLUSIVE锁时，就会被阻塞。rename阶段会持有MDL_EXCLUSIVE锁，但由于这个过程时间非常短(大头都在copy数据阶段)，并且是alter的最后一个阶段，所以基本感觉不到alter会阻塞select语句。由于MDL锁在事务提交后才释放，若线上存在大查询，或者存在未提交的事务，则会出现ddl卡住的现象。这里要注意的是，ddl卡住后，若再有select查询或DML进来，都会被堵住，

五 MDL锁的性能与并发改进

讲到这会发现MDL锁的开销并不比InnoDB层的行锁要小，而且这可能是一个更为密集的并发瓶颈。MySQL 5.6和5.5版本通常通过调整如下两个参数来进行并发调优:

metadata_locks_cache_size：MDL锁的缓存大小。

metadata_locks_hash_instances：通过分片来提高并发度，与InnoDB AHI类似。

MySQL 5.7 MDL锁的最大改进之处在于将MDL锁的机制通过lock free算法来实现，从而提高了在多核并发下数据库的整体性能提升。那么什么无锁算法呢？搜了下是LF_HASH。（尽管对MDL HASH进行了分区，但由于是以表名+库名的方式作为key值进行分区，如果查询或者DML都集中在同一张表上，就会hash到相同的分区，引起明显的MDL HASH上的锁竞争。由于引入了LF_HASH，MDL HASH分区特性自然直接被废除了）还是看看mysql.taobao的详细介绍吧：MySQL · 5.7优化 · Metadata Lock子系统的优化

另外找了篇，腾讯云的文章，跟这个MDL有点关系。畅游数据库性能优化过程简析（上）这篇从一个调优的角度去分析问题。

参考：

https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html

https://www.cnblogs.com/zengkefu/p/5690385.html

http://www.ywnds.com/?p=7209&viewuser=42

bohu83

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
1
评论
MySQL的MDL元数据锁

一序本来在看mysql.taobao介绍InnoDB 事务子系统介绍，真的头大，名词都看不懂，还是分开来学习的好，本文主要整理MDL（metadata locking）锁。作为尝试学习人新人，发现困惑很多，应该是没有买本书系统的去看的事，只是突然的去看技术博客。官网的文档介绍的比较少，英语不好。所以我自己觉得有几个层次，：有个功能干啥的，怎么配置参数或者使用，能看源码明白怎么实...
复制链接

扫一扫