科普文：软件架构数据库系列之【MySQL 执行DQL/DML/DDL必加的自动表级锁：MDL元数据锁Meta Data Lock】

-无-为-

已于 2024-10-01 10:21:38 修改

阅读量628

点赞数 17

分类专栏：业务场景软件架构 MySQL 文章标签：数据库 mysql MDL 元数据锁

于 2024-10-01 10:21:02 首次发布

本文链接：https://blog.csdn.net/Rookie_CEO/article/details/142661846

版权

软件架构同时被 3 个专栏收录

674 篇文章 11 订阅

订阅专栏

业务场景

382 篇文章 1 订阅

订阅专栏

MySQL

124 篇文章 2 订阅

订阅专栏

概叙

实战：MySQL之Innodb中的锁_mysql innodb 锁-CSDN博客

科普文：软件架构数据库系列之【Innodb的锁和MVCC】-CSDN博客

从前面文章可以知道，innodb是行锁，但是行锁可以升级成表锁，而且行锁总共分为三种：记录锁（Record Locks），间隙锁（Gap Locks），临键锁（Next-Key Locks），不过今天我们不说这三种锁，说一下表锁：MDL元数据锁Meta Data Lock。

MySQL的锁，按照不同依据，有不同的划分方法，具体可以看官方文档：MySQL :: MySQL 8.4 Reference Manual :: 10.11 Optimizing Locking Operations

见名知意，MDL元数据锁的作用是在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。

自MySQL5.5.3版本开始引入了MD元数据锁，来保护表的元数据信息，用于解决或者保证DDL操作与DML/DQL操作之间的一致性。

在5.5.3版本之前，MySQL事务对于表结构即元数据(Metadata)的锁定是语句(statement)粒度的，即语句执行完成后，不管事务是否可以完成，其表结构就可以被其他会话更新掉！而引入Metadata lock后，表结构元数据(Metadata)的锁定变成了事务(transaction)粒度的，即只有事务结束时才会释放Metadata lock。

关于DDL、DCL、DML、DQL等可参考前面的SQL命令梳理：科普文：软件架构数据库系列之【MySQL数据库SQL命令和SQL标准梳理】-CSDN博客

MDL元数据锁支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象，可通过show processlist看到 waiting for table metadata lock 时，那就是遇到MDL元数据锁了。

一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

先了解一下MySQL锁的基本概念：

MySQL锁的由来

客户端发往MySQL的一条条SQL语句，实际上都可以理解成一个个单独的事务（一条sql语句默认就是一个事务）。而事务是基于数据库连接的，每个数据库连接在MySQL中，又会用一条工作线程来维护，也意味着一个事务的执行，本质上就是一条工作线程在执行，当出现多个事务同时执行时，这种情况则被称之为并发事务，所谓的并发事务也就是指多条线程并发执行。

多线程并发执行自然就会出问题，也就是科普文：数据库事务、隔离级别和并发问题（MySQL）_数据库的并发控制、事务管理和隔离级别-CSDN博客中提到的脏写、脏读、不可重复读及幻读问题。

而对于这些问题又可以通过调整事务的隔离级别来避免，那为什么调整事务的隔离级别后能避免这些问题产生呢？

这是因为不同的隔离级别中，工作线程执行SQL语句时，用的锁粒度、类型不同。

锁定义

由以上可知，数据库的锁机制本身是为了解决并发事务带来的问题而诞生的，主要是确保数据库中，多条工作线程并行执行时的数据安全性。

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁分类

MySQL的锁机制与索引机制类似，都是由存储引擎负责实现的，这也就意味着不同的存储引擎，支持的锁也并不同，这里是指不同的引擎实现的锁粒度不同。

但总归说来说去其实就共享锁（读锁）、排他锁（写锁）两种，只是加的方式不同、加的地方不同，因此就演化出了这么多锁的称呼。

而表锁：MDL元数据锁Meta Data Lock，是“共享排他锁（读写锁）”，因为在5.6引入OnlineDDL后，MDL可以从排他锁降（写锁）级到共享锁（读锁）。

首先要知道 MySQL 有哪些锁，如上图所示，至少有 12 类锁（其中自增锁是事务向包含了 AUTO_INCREMENT 列的表中新增数据时会持有，predicate locks for spatial index 为空间索引专用，本文不讨论这 2 类锁）。

除开从锁粒度来划分锁之外，其实锁也可以从其他的维度来划分，因此也会造出很多关于锁的名词，下面先简单梳理一下MySQL的锁体系：

以锁粒度的维度划分
- 全局锁：锁定数据库中的所有表。加上全局锁之后，整个数据库只能允许读，不允许做任何写操作。对整个数据库实例加锁。加锁表现：数据库处于只读状态，阻塞对数据的所有 DML/DDL；加锁方式：Flush tables with read lock 释放锁：unlock tables(发生异常时会自动释放)；作用场景：全局锁主要用于做数据库实例的逻辑备份，与设置数据库只读命令set global readonly=true相比，全局锁在发生异常时会自动释放。
- 表级锁：每次操作锁住整张表。主要分为三类
  - 表锁（分为表共享读锁 read lock、表独占写锁 write lock）
  - 元数据锁（meta data lock，MDL）：基于表的元数据加锁，加锁后整张表不允许其他事务操作。这里的元数据可以简单理解为一张表的表结构
  - 意向锁（Intention Locks分为意向共享锁 IS、意向排他锁 IX）：这个是InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
- 行级锁：每次操作锁住对应的行数据。InnoDB 引擘支持行级别锁，行锁粒度小，并发度高，但加锁开销大，也可能会出现死锁。加锁机制：innodb 行锁锁住的是索引页，回表时，主键的聚簇索引也会加上锁。主要分为三类
  - 记录锁 / Record 锁：也就是行锁，一条记录和一行数据是同一个意思。防止其他事务对此行进行update和delete，在 RC、RR隔离级别下都支持
  - 间隙锁 / Gap 锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
  - 临键锁 / Next-Key 锁：间隙锁的升级版，同时具备记录锁+间隙锁的功能，在RR隔离级别下支持
以互斥性的角度划分
- 共享锁 / S锁：不同事务之间不会相互排斥、可以同时获取的锁
- 排他锁 / X锁：不同事务之间会相互排斥、同时只能允许一个事务获取的锁
- 共享排他锁 / SX锁：MySQL5.7版本中新引入的锁，主要是解决SMO带来的问题
以操作类型的维度划分
- 读锁：查询数据时使用的锁
- 写锁：执行插入、删除、修改、DDL语句时使用的锁
以加锁方式的维度划分
- 显示锁：编写SQL语句时，手动指定加锁的粒度
- 隐式锁：执行SQL语句时，根据隔离级别自动为SQL操作加锁
以思想的维度划分
- 乐观锁：每次执行前认为自己会成功，因此先尝试执行，失败时再获取锁
- 悲观锁：每次执行前都认为自己无法成功，因此会先获取锁，然后再执行

锁信息查看方式

MySQL 5.6.16 版本之前，需要建立一张特殊表 innodb_lock_monitor，然后使用show engine innodb status查看

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

DROP TABLE innodb_lock_monitor;

MySQL 5.6.16 版本之后，修改系统参数 innodb_status_output 后，使用show engine innodb status查看

set GLOBAL innodb_status_output=ON;

set GLOBAL innodb_status_output_locks=ON;

每 15 秒输出一次 INNODB 运行状态信息到错误日志。

MySQL5.7 版本之后

可以通过 information_schema.innodb_locks 查看事务的锁情况，但只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况

MySQL8.0

删除 information_schema.innodb_locks，添加 performance_schema.data_locks，即使事务并未被阻塞，依然可以看到事务所持有的锁，同时通过 performance_schema.table_handles、performance_schema.metadata_locks 可以非常方便的看到元数据锁等表锁。

什么是MDL锁

MDL全称为metadata lock，即元数据锁。

MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁，来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。

Metadata Locking behavior prior to MySQL 5.5.3：Implications of Metadata Locking Changes in MySQL 5.5

MySQL5.6Online DDL

MySQL5.6之后引入了Online DDL 从名字上就可以看出来在线执行DDL，不和其他操作冲突，具体执行流程如下：

1.DDL操作需要首先获取MDL写锁
2.将MDL写锁降级成MDL读锁。
3.做真正的DDL操作，这一步十分耗时，由于此时我们持有的是MDL读锁，因此并不会阻塞其他的增删改查操作。
4.做完DDL操作之后，接下来将MDL读锁升级成MDL写锁。
5. 释放MDL锁。

注意：Online DDL并不是让你随时随地地可以更新表结构，也还是有限制的，只有在第三步哪里可以继续执行增删改查，即使是在第三步，更新表结构也是不允许的。

MySQL5.7 metadata_locks表

MDL锁通常发生在DDL操作挂起的时候，原因是有未提交的事务对该表进行DML操作。而MySQL的会话那么多，不知道哪个会话的操作没有及时提交影响了DDL。通常我们排查这类问题，往往需要从information_schema.innodb_trx表中查询当前在执行的事务，但当SQL已经执行过了，没有commit，这个时候这个表中是看不到SQL的。

在MySQL5.7中，performance_schema库中新增了metadata_locks表，专门记录MDL的相关信息。

在MySQL5.7中，performance_schema库中新增了metadata_locks表，专门记录MDL的相关信息。首先要开启MDL锁记录，执行如下SQL开启：

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

MySQL8.0 并行DDL

科普文：软件架构数据库系列之【MySQL8.0新特性-并行DDl-innodb_ddl_threads】_mysql8 ddl工具-CSDN博客

MySQL 的 DDL 有很多种方法。

MySQL 本身自带三种方法，分别是：copy、inplace、instant。

copy 算法为最古老的算法，在 MySQL 5.5 及以下为默认算法。
从 MySQL 5.6 开始，引入了 inplace 算法并且默认使用。inplace 算法还包含两种类型：rebuild-table 和 not-rebuild-table。MySQL 使用 inplace 算法时，会自动判断，能使用 not-rebuild-table 的情况下会尽量使用，不能的时候才会使用 rebuild-table。当 DDL 涉及到主键和全文索引相关的操作时，无法使用 not-rebuild-table，必须使用 rebuild-table。其他情况下都会使用 not-rebuild-table。
从 MySQL 8.0.12 开始，引入了 instant 算法并且默认使用。目前 instant 算法只支持增加列等少量 DDL 类型的操作，其他类型仍然会默认使用 inplace。

MDL加锁过程

元数据锁是server层的锁，表级锁，每执行一条DQL、DML、DDL语句时都会申请MDL锁。
DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）。
申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。
一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。
事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放）。

其实MDL加锁过程是系统自动控制，无法直接干预，也不需要直接干预。

DQL、DML会自动加MDL共享锁（读锁）：加读锁则所有线程可正常读表的元数据，并且读锁不影响表的增删改查操作，只是不能修改表结构；
DDL会自动加MDL排他锁（写锁）：而加写锁只有拥有锁的线程可以读写元数据，即只拥有锁的线程才能更新表结构，其他线程不能修改结构也不能执行相应的增删改查。

即MDL中，读读共享，读写互斥，写写互斥。

对于引入MDL，其主要解决了2个问题：

一个是事务隔离问题，比如在可重复隔离级别RR下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；
另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。

验证MDL锁

下面展示下模拟及查找MDL锁的过程：

共享锁（读锁）SHARED_READ

表共享读锁(shared_read_only)和元数据共享读锁(shared_read)还是有区别的。shared_read_only->能读不能写，当前客户端与其它客户端只能读不能写；shared_read->读写都可，当前客户端和其它客户端读写都可。
元数据共享读锁(shared_read)和元数据共享写锁(shared_write)兼容，即两客户端开启事务后，都可以正常执行读(select)、写(insert/delete/update)操作。但数据得满足事务的隔离级别。
对两个客户端，一旦一个客户端执行了增删改查(即加了shared_read或shared_write)，另一个客户端无法执行表结构更改(加上元数据排他锁(exclusive)，exclusive与其它元数据锁互斥)。