互动有礼｜MySQL如何实现并发控制？（上）

阿里云瑶池数据库 2024-08-12

273

前言

最开始学习数据库的时候都会被问到一个问题：“数据库系统相比与文件系统最大的优势是什么？”。具体的优势有很多，其中一个很重要的部分是：数据库系统能够进行更好的并发访问控制。

那么，数据库系统到底是怎么进行并发访问控制的？

本系列文章以 MySQL 8.0.35 代码为例，分为上、下两篇尝试对 MySQL 中的并发访问控制进行整体介绍。本篇为上篇，将重点介绍表级别的并发访问控制。

总体介绍

按照近些年流行的概念来讲，MySQL 是一个典型的存储计算分离的架构，MySQL Server 作为计算层，Storage Engine 作为存储层。所以并发访问的控制也需要在计算层和存储层分别进行处理。这里多说一句，MySQL 在设计之初就支持多存储引擎，这也是 MySQL 快速流行的重要原因之一，只是随着 MySQL 的发展，到 MySQL 8.0 时代，基本变成了 InnoDB 一家独大的情况。所以本文后续的分析，主要都是围绕 InnoDB 引擎展开。

从数据访问的角度，用户视角下，MySQL 的数据分为：表、行、列。MySQL 内部视角下则包括了：表、表空间、索引、B+tree、页、行、列等。在 MySQL 8.0 中，默认情况下一个表独占一个表空间，所以为了描述简单，本文后续内容对表和表空间不做区分。

回到主题，MySQL中的并发访问控制也是基于MySQL内部的数据结构来进行设计的，具体包括了：

1. 表级别的并发访问控制，包括 Server 层和 Engine 层上的表；

2. 页级别的并发访问控制，包括 Index 和 Page 上的并发访问；

3. 行级别的并发访问控制；

后续内容将围绕以上三个部分展开，本篇将重点介绍“表级别的并发访问控制”。

表级别的并发访问控制

我的DDL会锁表吗？

在使用数据库的过程中，一个绕不开的操作就是 DDL，特别是在线上运行的库上直接进行 DDL 操作。MySQL 的用户经常会疑惑的一个问题就是：“我这个 DDL 会不会锁表啊？别把业务搞挂了。”之所以会有这样的疑问，是因为在早期的 MySQL 版本中（5.6 之前），DDL 期间是无法进行 DML 操作的，这就导致如果是对一个大表进行 DDL 操作的话，业务会长期无法进行数据写入。为了减少 DDL 期间对业务的应用，衍生出很多三方的 DDL 功能，其中使用最多的一个是 pt-online-schema-change。

实际上，从 MySQL 5.6 版本开始，MySQL 已经支持 Online DDL 操作；到 5.7 版本，Online DDL 的支持范围进一步扩大，到了 8.0 版本，MySQL 官方进一步支持了 Instant DDL 功能，在 MySQL 上执行 DDL 基本上不会造成业务影响。

关于 Online DDL 的详细介绍，可以直接阅读官方文档^[1]，想直接看精简版的同学，可以参考笔者之前整理的一篇文章^[2]。

[1] 官方文档：https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html

[2] http://mysql.taobao.org/monthly/2021/03/06/

MDL锁

DDL 是否会锁表其实就是表级别并发访问控制中最重要的一个问题。MySQL 中实现 DDL、DML、DQL 并发访问最重要的结构就是 MDL 锁。先看一个简单的例子：

CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c1` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


INSERT INTO t1 VALUES (1, 10);
INSERT INTO t1 VALUES (2, 20);
INSERT INTO t1 VALUES (3, 30);

在上述例子中：

session 1 上模拟了一个慢查询；
session 2 上执行了一个添加的 DDL，因为查询没有结束，所以 DDL 被阻塞；
session 3 上继续进行了查询，查询也会被阻塞，用户觉得“锁表”了；

为什么会出现上述的情况？这里结合 performance_schema 下的 metadata_locks 表可以很清楚的看到等待关系：

可以看到：

session 1（THREAD_ID = 57）持有了表上的 SHARED_READ 锁；
session 2（THREAD_ID = 58）持有了表上的 SHARED_UPGRADABLE 锁，需要申请表上的 EXCLUSIVE 锁，被阻塞；
session 3（THREAD_ID = 59）需要申请表上的 SHARED_READ 锁，被阻塞；

从代码路径上，MDL 的加锁逻辑在打开表的过程中，具体的入口函数为open_and_process_table，具体的函数堆栈如下：

|--> open_and_process_table
|    |--> open_table
|    |    |--> mdl_request.is_write_lock_request
|    |    |--> thd->mdl_context.acquire_lock  请求 global MDL 锁
|    |    |
|    |    |--> open_table_get_mdl_lock
|    |    |    |--> thd->mdl_context.acquire_lock  请求 table MDL 锁

DDL 过程中升级 MDL 锁逻辑的入口函数为mysql_alter_table，具体的函数堆栈如下：

|--> mysql_alter_table
|    |--> mysql_inplace_alter_table
|    |    |--> wait_while_table_is_used
|    |    |    |--> thd->mdl_context.upgrade_shared_lock  升级 MDL 锁
|    |    |    |    |--> acquire_lock  请求 table MDL EXCLUSIVE 锁

通过上面一个简单的例子，我们知道了 MDL 锁的基本概念，也知道了所谓的 DDL 导致“锁表”的原因，严格的说，MDL 锁并不是表锁，而是元数据锁，关于 MDL 更深入的介绍，可以参考这篇文章，本文不再过多展开。MySQL 在 5.6 版本中引入了 MDL 锁，那么是不是有了 MDL 锁之后，其他的表锁就不需要了？

Server层的表锁

回答上面的问题前，先看一下 MySQL Server 层处理表锁的基本过程。MySQL 中任意表上的操作都需要加表锁，具体的入口函数为lock_tables，具体的函数堆栈如下：

|--> lock_tables
|    |--> mysql_lock_tables
|    |    |--> lock_tables_check  / 判断是否需要加锁
|    |    |--> get_lock_data  // 计算有多少张表需要加锁，初始化 MYSQL_LOCK 结构
|    |    |    |--> file->lock_count
|    |    |
|    |    |--> lock_external
|    |    |    |--> ha_external_lock  // 调用 engine handler 接口
|    |    |
|    |    |--> thr_multi_lock
|    |    |    |--> sort_locks
|    |    |    |--> // 遍历加锁
|    |    |    |--> thr_lock  // 加锁 or 等待
|    |    |    |    |--> wait_for_lock // 锁等待，Waiting for table level lock

通过上面的堆栈可以看到，整个加锁的过程包括了以下步骤：

加锁前需要先判断对应的表是否需要加锁；
加锁时，需要先调用 Engine 层的 hanlder 接口加锁；
如果需要，再在 Server 层进行加锁；

对于 InnoDB 引擎，lock_count接口直接返回 0，表示 InnoDB 引擎的表不需要 Server 层后续再加表锁，直接在external_lock接口中完成所有的处理，这部分后面展开。对于其他引擎，以 CSV 引擎为例，lock_count接口返回 1，所以需要进入到后续的thr_lock加锁逻辑中。关于thr_lock加锁的类型，以及不同类型锁的冲突关系，此处不再做展开。

狭义上来说，thr_lock接口加的锁就是 Server 层的表锁，具体的加锁逻辑、锁类型的互斥关系、锁等待的逻辑此处不再展开，有兴趣的同学可以自己结合代码进行查看。

InnoDB中的表锁

前面提到，Server 层的 lock_tables 接口会调用 Engine 层的 Handler 接口，具体的会调用 external_lock 接口，那么 InnoDB 在该接口内会去加表锁吗？先看一下函数调用堆栈：

|    |--> // lock_type == F_WRLCK
|    |--> m_prebuilt->select_lock_type = LOCK_X
|    |
|    |--> // lock_type == F_RDLCK && trx->isolation_level == TRX_ISO_SERIALIZABLE 
|    |--> m_prebuilt->select_lock_type = LOCK_S
|    |
|    |--> // others
|    |--> m_prebuilt->select_lock_type == LOCK_NONE


|--> row_search_mvcc
|    |--> lock_table(..., prebuilt->select_lock_type == LOCK_S ? LOCK_IS : LOCK_IX, ...)

通过上面的堆栈可以看到，进入到 InnoDB 层的加锁逻辑时:

只会先设置后续查询需要的锁类型；
普通的查询操作设置为 LOCK_NONE，后续查询过程无需上锁；
更新操作设置为 LOCK_X，后续查询过程中需要加表上的 IX 锁；

关于 InnoDB 层表锁的具体类型，以及不同类型锁的冲突关系，此处不再做展开。Engine 层的表锁情况，可以在 performance_schema 下的 data_locks 表中进行查看：

LOCK TABLES操作

前面已经介绍了 MySQL 中的 MDL 锁以及 Server 层和 InnoDB 层的表锁，那么对应到 LOCK TABLES 操作上，到底加的是什么锁？先看一下 LOCK TABLES 操作的执行路径：

|--> mysql_execute_command
|    |--> // switch (lex->sql_command)
|    |--> // SQLCOM_LOCK_TABLES
|    |--> trans_commit_implicit // 隐式提交之前的事务
|    |--> thd->locked_tables_list.unlock_locked_tables // 释放之前的表锁
|    |--> thd->mdl_context.release_transactional_locks // 释放之前的 MDL 锁
|    |
|    |--> lock_tables_precheck
|    |--> lock_tables_open_and_lock_tables
|    |    |--> open_tables
|    |    |    |--> lock_table_names // 根据表名加锁（此时还没有打开表）
|    |    |    |    |--> mdl_requests.push_front
|    |    |    |    |--> thd->mdl_context.acquire_locks
|    |    |    |
|    |    |    |--> open_and_process_table
|    |    |
|    |    |--> lock_tables

从上面的堆栈可以看到，对于显式的 LOCK TABLES 操作：

会首先隐式提交之前的事务，并且释放掉之前所有的表锁和 MDL 锁；
在打开表之前，直接根据表名进行加锁（如果有其他事务未提交，可能会卡在这里）；
然后进入到正常的打开表和加锁的逻辑；

用一个表格总结一下不同的 LOCK TABELS 操作的加锁情况（InnoDB 表）：

典型线上问题

关于 MySQL 中由于表锁导致的问题，举两个线上常见的案例：

1. DDL 操作导致的 MDL 锁等待。也就是前面在介绍 MDL 锁时举到的例子。其实这类是比较好发现的，直接执行 show processlist 就能看到大量的 MDL 锁等待，这里主要是说明一下如何处理此类问题。处理的方法主要有两种：

借助performance_schema下的metadata_locks表，找到具体的MDL等待关系，然后进行处理（例如：kill 掉慢查询）；

但是线上多数情况下并没有开启 performance_schema（担心有性能影响），所以也无法从 metadata_locks 表中查询到 MDL 等待关系。此时可以采用另一个方法：直接根据 Time 列进行排序（逆序），然后依次 kill 连接，直到锁等待关系解除。当然，也可以直接 kill 掉所有连接。

2. Server 层表锁导致的性能问题。典型的场景就是开启了 general_log，并且设置输出格式为 TABLE。由于 genelog_log 表是 CSV 引擎，所以需要通过 Server 层的表锁来控制并发插入，当写入量很大时，CSV 表的写入会出现性能瓶颈。从现象上看，就是大量的连接等待表锁“Waiting for table level lock”。CSV 表的写性能问题暂时没有好的优化方式，所以遇到之后最好的处理手段就是直接关闭 general_log。

表级别的加锁过程总结

以上就是表级别的加锁过程，做一个总结：

1. 最先加的是 MDL 锁，在打开表时（open_and_process_table接口）就需要根据操作的类型确定 MDL 的锁类型（实际上，大部分请求在词法解析阶段就已经完成了 MDL 请求的初始化）；

2. 在实际的 SQL 操作时，会根据操作的类型，在不同的位置调用lock_tables接口加表锁，表锁又分为 Server 层的表锁和 Engine 层的表锁：

对于 InnoDB 引擎，直接调用 Engine 层的external_lock接口去加 Engine 层的表锁（通过前面的代码堆栈知道，其实只是确定后续需要加锁的类型，加锁动作是后置的），不需要再在 Server 层加表锁；
对于 CSV 引擎，Engine 层并没有实现external_lock接口，所以需要在 Server 层加表锁。

作者有话说

在写这篇文章之前，关于 MySQL 内部各种锁的介绍文章已经很多了，其实只要是稍微了解数据库、了解 MySQL 的同学，都会有自己对于各种锁的认知。

至于为什么要写这篇文章，一是觉得网上很多文章都太偏重于概念，一上来就是共享锁与互斥锁、乐观锁与悲观锁、显式锁与隐式锁，要不就是一个表格告诉你各种锁的互斥与兼容关系，而没有结合实际的例子来说明为什么要这么加锁，一看一个不吱声；

二是最近刚好碰到了几个线上问题，所以趁此机会把之前分散整理的一些文档统一梳理了一遍，更多的还是自己的理解，如果文章中有描述错误的地方，欢迎批评指正。

想了下，标题叫做《MySQL中的锁分析》好像不是很合适，所以叫做《MySQL如何实现并发控制》。当然，MySQL 中的并发控制远不止这些，有机会的话将继续补充。

精彩干货敬请期待 🎉

下篇内容将重点分享页级别、行级别的并发访问控制。

💬 欢迎对本系列内容感兴趣的开发者在评论区留言互动，8月14日12:00前，「互动最大声」（留言点赞数最高，且留言内容与文章主题相关）的小伙伴将获得瑶池数据库定制款保温杯*1哦～欢迎评论区积极讨论