MYSQL的MDL元数据锁

MYSQL的MDL元数据锁

 

 

• 1 前言
• 2 MDL锁与实现
• 3 MDL锁的性能与并发改进
• 4 MDL锁的诊断

前言

好久没更新，主要是因为Inside君最近沉迷于一部动画片——《新葫芦娃兄弟》。终于抽得闲，完成了本篇关于MySQL MDL锁的深入分析与介绍。虽然之前有很多小伙伴分析过，但总感觉少了点什么，故花了点时间翻看了下源码。Inside君或许不是最牛掰的内核开发人员，但自认为应该是业界最会讲故事的码农，希望本篇能做到通俗易懂，因为MDL锁其实并不好理解。如果同学们还有问题，也可以直接看源码文件mdl.cc。

MDL锁与实现

MySQL5.5版本引入了MDL锁（metadata lock），用于解决或者保证DDL操作与DML操作之间的一致性。例如下面的这种情形：

会话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需要等待。

InnoDB层已经有了IS、IX这样的意向锁，有同学觉得可以用来实现上述例子的并发控制。但由于MySQL是Server-Engine架构，所以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锁。

这里最令人意外的是还有COMMIT对象层次的锁，其实这主要用于XA事务中。比如分布式事务已经PREPARE成功，但是在XA COMMIT之前有其他会话执行了FLUSH TABLES WITH READ LOCK这样的操作，那么分布式事务的提交就需要等待。

除了上图标注的对象，其实还有TABLESPACE、FUNCTION、PROCEDURE、EVENT等其他对象类型，其实都是为了进行并发控制。只是这些在MySQL数据库中都不常用，故不再赘述（当然也是为了偷懒）。

GLOBAL=0,TABLESPACE,SCHEMA,TABLE,FUNCTION,PROCEDURE,TRIGGER,EVENT,COMMIT,USER_LEVEL_LOCK,LOCKING_SERVICE,NAMESPACE_END 

 

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

锁模式	对应SQL
MDL_INTENTION_EXCLUSIVE	GLOBAL对象、SCHEMA对象操作会加此锁
MDL_SHARED	FLUSH TABLES with READ LOCK
MDL_SHARED_HIGH_PRIO	仅对MyISAM存储引擎有效
MDL_SHARED_READ	SELECT查询
MDL_SHARED_WRITE	DML语句
MDL_SHARED_WRITE_LOW_PRIO	仅对MyISAM存储引擎有效
MDL_SHARED_UPGRADABLE	ALTER TABLE
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 …

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算法来实现，从而提高了在多核并发下数据库的整体性能提升。

MDL锁的诊断

MySQL 5.7版本之前并没有提供一个方便的途径来查看MDL锁，github上有一名为mysql-plugin-mdl-info的项目，通过插件的方式来查看，非常有想法的实现，大赞。好在官方也意识到了这个问题，于是在MySQL 5.7中的performance_schea库下新增了一张表metadata_locks，用其来查看MDL锁那是相当的方便:

不过默认PS并没有打开此功能，需要手工将wait/lock/metadata/sql/mdl监控给打开:

SELECT * FROM performance_schema.setup_instruments;

  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';
  select * from performance_schema.metadata_locks\G

 

会话1

mysql> lock table xx read;
Query OK, 0 rows affected (0.21 sec)

会话2：

SELECT * FROM performance_schema.metadata_locks;

mysql> SELECT * FROM performance_schema.metadata_locks\G;
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 240554768
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:5927
      OWNER_THREAD_ID: 38
       OWNER_EVENT_ID: 10
1 row in set (0.00 sec)

参考博文:https://www.cnblogs.com/zengkefu/p/5690385.html