mysql锁相关处理 mysql各种锁

基础能力：阅读这篇文章需要你对锁本身有一定的了解，同时使用过 MySQL 中的锁。

开发多用户、数据库驱动的应用时，最大的一个难点就是既要保证并发性能，又要确保数据一致性。为此就有了锁机制。

MySQL 在 server 层 和 存储引擎层 都运用了大量的锁，而理清楚各种锁的作用以及他们的加锁时机，不仅有利于我们在使用 MySQL 中避免不必要的死锁，同时可以让我们在业务层面去考虑 SQL 语句执行对生产环境可能带来的影响从而预防事故。

接下来将从两个层面上去讲解 MySQL 中的锁，一个是 server 层，一个是存储引擎层，存储引擎层仅深入讲解 InnoDB 中的锁。

MySQL 锁

MySQL server 层需要讲两种锁，第种个是MDL(metadata lock) 元数据锁，第二种则 Table Lock 表锁。

MDL 锁

MDL 又名元数据锁，那么什么是元数据呢，任何描述数据库的内容就是元数据，比如我们的表结构、库结构等都是元数据。那为什么需要 MDL 呢？

解决问题

MDL 的诞生，主要是为了解决 2 个问题：

• 事务隔离问题，比如在可重复(REPEATED READ)隔离级别下，会话 A 对表 C 做了 2 次查询的过程中，会话 B 在会话 A 第一次查找后第二次查找前对表 C 的表结构做了修改，会话 A 的两次查询结果就会不一致，无法满足可重复读的要求；
• 数据复制的问题，比如会话 A 执行了多条更新语句期间，会话 B 做了表结构变更并且先提交，就会导致 Slave 在重做时，先重做 ALTER，再重做 UPDATE ，此时在 Slave 中的表结构已经发生变化，就会出现执行语句错误，导致复制失败。

所以必须要对元数据进行保护，保证 DML 和 DDL 的一致性。

历史车轮

MDL 是在 5.5 引入到 MySQL 的，之前也有类似保护元数据的机制，只是没有明确提出 MDL 概念而已。但是 5.5 之前版本(比如 5.1)与 5.5 之后版本在保护元数据这块有一个显著的不同点是，5.1 对于元数据的保护是语句级别的，5.5 之后对于元数据的保护支持到了事务级别的。所谓语句级别，即语句执行完成后，MDL 就被释放了，而事务级别则是在事务结束后才释放 MDL。

实战探索

MDL 本身的目的是明确的，就是为了防止 DDL 和 DML 之前的冲突 ，MySQL 为了性能考虑，在实现上为 MDL 细分出了很多子类型，所以 MDL 的内容就复杂了起来，整个 MDL 涉及到的内容被实现成了一个子系统，官方也称为 Metadata Lock Subsystem，可想而知它的复杂程度，但是并不妨碍我们去了解它，这里将讲解两种方法去帮助我们观察 MDL 加锁情况。

performance_schema.metadata_locks

metadata_locks 是 MySQL 存储 MDL 加锁情况的地方，通过该表我们可以直接观察到当前已经加了哪些 MDL，只要执行如下语句就可以：

mysql> select * from performance_schema.metadata_locks\G;
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139691999980048
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6162
      OWNER_THREAD_ID: 59
       OWNER_EVENT_ID: 3
1 row in set (0.05 sec)

对于 MySQL 而言，一个 MDL 就会包含上述字段内容，我们需要重点关注 MDL 很重要的几个字段：

• OBJECT_TYPE ：表示 MDL 加锁的对象类型；
• LOCK_TYPE：MDL 的锁类型；
• LOCK_DURATION：MDL 的锁时长，有三个级别：语句级别、事务级别、显性级别；
• LOCK_STATUS：MDL 持有状态，此时 GRANTED 表示已得到锁。

上述加粗的字段往往是我们比较关心的，可以方便我们直接定位问题。但是该表不适合我们去深入了解 MDL，仅适合定位锁等待或死锁问题。

MDL 有三种加锁时长分别是语句级别、事务级别、显式级别，通过查看 metadata_locks 我们一般只能看到事务或是显式级别的 MDL，语句级别的 MDL 在语句执行完成后就会被释放，除非出现比较严重的锁等待，不然无法直接从 metadata_locks 看到，这个时候就需要 GDB 打断点进行观察来帮助我们学习 MDL。

GDB 断点调试

进行 GDB 之前，首先需要有一个带有 Debug 信息的 MySQL，所以需要先搭建一个可用环境。 直接通过 yum install mysqlxx 是无法安装一个带有 Debug 信息的 MySQL 的，同时为了防止污染自身环境，这里建议大家在 Docker 的基础上搭建一个带有 Debug 信息的 MySQL ，构建方法有两种方法：

• 直接拉取已有镜像进行构建 docker pull leo18945/mysql-debug ，但是该镜像仅为 5.7.29 版本的，其实也够，MDL 的实现与 8.0 差异不大；
• 直接下载 8.0 源码构建一个镜像，可以参照该文章构建一个： 。

接着我们通过 gdb -p $pid 将 GDB 附加到 MySQL-server 进程上，接下来你有三种断点的选择：

函数	说明
MDL_context::upgrade_shared_lock	加 MDL 的接口
MDL_context::acquire_lock	升级锁，将共享锁升级为排他锁或将低优先级锁升级为高优先级锁
MDL_ticket::downgrade_lock	降级锁，与 upgrade_shared_lock 作用相反

通过对上面三个接口的断点就可以能清晰的观察到一个事务或者一个语句会加什么样的 MDL。以下是调试 select * from T 在 MDL_context::acquire_lock 断点截图：

这里讲解一下上述红框中几个变量的含义：

• type 即 LOCK_TYPE；
• duration 即 LOCK_DURATION；
• key.m_ptr 是 MDL 的 Key，唯一标识一个锁。

key.m_ptr 的组成很简单，如下图：

在初始化 key.m_ptr 时，OBJECT_TYPE 很重要，它决定了 db name 和 table name 是否有值。比如 OBJECT TYPE 为 GLOBAL 时，就会在 namespace 上填 0，db name 和 table name 就没有值。现在 OBJECT_TYPE 为 4 代表着 TABLE，所以后续两个部分有值。

MDL 对象、类型和兼容性

MDL 不像表锁，它并不是只能锁住一种对象，实际上大到全局小到表，都会用到 MDL，比较常见的对象如下：

锁对象	说明
GLOBAL	全局，如果 MDL 加在该对象上则是对整个 MySQL 实例加锁，该锁全局唯一
COMMIT	提交，insert/delete/udpate 在事务提交时，都会在 commit 上加一个意向排他锁，该锁全局唯一
SCHEMA	库，insert/delete/udpate 在语句执行时会将库上加锁
TABLE	表，select/insert/delete/udpate 在语句执行时会将表上加锁

锁对象作用很大，很多文章中说的 flush table with read lock 加的全局锁实际上就是 MDL 在 GLOBAL 和 COMMIT 对象上同时加了一个读锁从而导致全局范围内的写阻塞(执行完 flush 语句后，可以查看 metadata_locks 进行观察)。看下面的类型兼容的时候也能有所体会。

MDL 的锁类型

我们在给对象加锁时还需要选择加什么类型锁，MySQL 有不下 10 种 MDL 类型，而且随着版本的更新，类型还在增加，这里仅列出常见语句 MDL 类型分别如下：

锁类型	说明
MDL_INTENTION_EXCLUSIVE(IX)	意向排他锁，除了IX外与其他锁均互斥
MDL_SHARED(S)	共享锁，仅在访问元数据信息时会加该锁，比如 desc table
MDL_SHARED_READ(SR)	共享锁，读取表数据使用，比如 select
MDL_SHARED_WRITE(SW)	共享锁，写表数据使用，比如update/insert/delete
MDL_SHARED_UPGRADABLE(SU)	可升级为写锁的共享锁，主要用于支持 Online DDL
MDL_EXCLUSIVE(X)	排他锁，只要变更元数据就一定会加该锁，比如create/alter/drop

从上表可以看到，任何 DML 语句都会加 MDL，这是保障 DDL 和 DML 一致性的基础。接着我们看看锁的兼容性。 上述表中 MDL 类型的兼容性矩阵如下(+为兼容，-为不兼容)：

类型	IX	S	SR	SW	SU	X
IX	+	-	-	-	-	-
S	-	+	+	+	+	-
SR	-	+	+	+	+	-
SW	-	+	+	+	+	-
SU	-	+	+	+	-	-
X	-	-	-	-	-	-

可以看到大部分共享锁之间是兼容的，但是 SU 和 SU 不兼容，这有效保证了一个表的多个 DDL 之间的操作是互斥，因为 SU 锁能升级为 X 锁，这种升级的方式是为了在没有真正执行到变更表结构的流程前依旧能保证读操作的并发性，所以 SU 能兼容 DML 又能阻塞 DDL。

MDL 的持有时长

除了给某个对象加锁和加什么类型的锁外，还需要确定加锁时长，MySQL 支持三种 MDL 持有时长：

时长	含义
MDL_STATEMENT	语句级别
MDL_TRANSACTION	事务级别，很常见，几乎所有的语句涉及到的 MDL 都是该级别
MDL_EXPLICIT	显式级别

小结

说到这里其实大概已经对 MDL 有了大致的了解，实际上我们并不用深究 MDL 的各个类型，上面列出的类型基本够用，毕竟大同小异，都是为了在某个特定分支上更好的支持并发性，但是实际上我们的业务场景我们只要了解到 MDL 对并发性的影响基本就够了。

很多文章里面也会说 MDL 是一个只存在写锁和读锁的表级锁，虽然这种观点是错误的，但是也很好的说明了我们对 MDL 理解仅限于读锁和写锁上某种程度上更容易让我们对选择做出决定（官网对 MDL 的介绍也仅提到了写锁和读锁），有时候去纠结具体加的是哪一种类型的 MDL 反而会让事情变得复杂。

数据库高并发性的实现是数据库开发者要考虑的而不是数据库的使用者要考虑的。使用者仅理解 MDL 读锁和写锁个人觉得完全木有问题，所以上面我也仅列出了部分 MDL 锁类型，只是为了让大家能够对 MDL 有个更深入的认识，而不是仅限于写锁和读锁。

表锁

MySQL 的表锁是显式调用的，一般我们可以通过 lock tables … read/write 加锁，通过 unlock tables 释放锁。从 lock tables 语句也能看出来，MySQL 的表锁只有 S 锁和 X 锁。

需要注意， lock tables 语法执行后，除了限制其他会话的操作，当前会话也会被限制。

比如说如果在会话 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他会话写 t1、读写 t2 的语句都会被阻塞。同时会话 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。写 t1 是不允许，同理也不能访问其他表。

对于不同的存储引擎所使用的 MySQL 表锁实现是不一样的，针对 MyISAM 引擎使用 THR_LOCK 来实现表锁，对于 InnoDB 而言，自 5.7.5 版本后，MySQL 表锁就彻底改用 MDL 实现了。

在使用 InnoDB 引擎的情况下， lock tables t1 read 会在 MySQL server 层给表 t1 上加一个 MDL_SHARED_READ_ONLY 的 MDL，而 lock tables t1 write 则会在 MySQL server 层给表 t2 加一个 MDL_SHARED_NO_READ_WRITE 的 MDL。

虽然实现变了，但是功能和语义是没有变化的，其还是表锁，所以接下来我们还是以 MySQL 表锁去解释问题和现象。

MySQL 表锁和引擎表锁

当我们执行 lock tables 时，如果存储引擎是 InnoDB 时，InnoDB 层默认会感知到 lock tables 操作，接着会在表上加一个表锁（这个表锁是 InnoDB 引擎的表锁，为了区分于 MySQL 的表锁，我们称为引擎表锁）。

也就是说 lock tables 操作会在表上加两个锁，一个是 MySQL 表锁，一个是引擎表锁。那为什么要这么干呢？

原因是 InnoDB 需要感知 MySQL 表锁，通过在表上加引擎表锁来达到感知的目的，从而能够检测 MySQL 表锁引起的死锁。

加 MySQL 表锁时就加引擎表锁，释放 MySQL 表锁时就释放引擎表锁。这样 MySQL 表锁也间接的感知了 InnoDB 内部的表锁和行锁，可以理解为加 MySQL 表锁和引擎表锁是一个原子操作，如果 InnoDB 内部加了表锁或者行锁，引擎表锁就会被阻塞， lock tables 也会被阻塞。

lock tables 默认就会加引擎表锁，可以通过参数 innodb_table_locks 进行配置。为 1 时 lock tables 就会加上引擎表锁，为 0 时就不加。

锁机制在 InnoDB 还有一个点需要注意的地方，事务结束就会释放该事务中的锁，如果直接执行 lock tables ，在语句结束后引擎表锁就会释放，这并不是我们想要的，所以我们要这么使用:

SET autocommit=0;  -- 禁用自动提交
LOCK TABLES t1 WRITE, t2 READ, ...;  -- 此时加 MySQL 表锁和引擎表锁
... do something with tables t1 and t2 here ...
COMMIT;  -- 可有可无，UNLOCK TABLES 本身就会 commit
UNLOCK TABLES;

小结

看到这里相信你已经对 MySQL 的表锁有了一定的了解，MySQL 表锁只有 S 锁和 X 锁，他的实现可能是使用 THR_LOCK 也可能是使用 MDL。对于 InnoDB 引擎而言， lock tables 会加两把表锁，一是 MySQL 表锁，一是引擎表锁。

InnoDB 锁

InnoDB 存储引擎既支持表锁，也支持行锁。表锁占用资源较少，但粒度很粗。有时候你仅仅需要锁住几条记录，但使用表锁的话相当于为表中的所有记录都加锁，所以性能比较差。行锁粒度更细，可以实现更精准的并发控制。

表级锁

我们通常说的表锁说的就是表级锁，InnoDB 有 5 种表级锁：IS、IX、S、X、AUTO- INC。

S 锁和 X 锁

S 锁又名读锁，X 锁又名写锁，我们在上面介绍 MySQL 表锁时提到的引擎表锁说的就是这里的 S 锁和 X 锁，当使用 lock tables t read 就会加 S 锁，当使用 lock tables t write 就会加 X 锁。

除上述情况之外，一般都不会使用 S 锁和 X 锁。

在对表执行 select/insert/delete/update 语句时， InnoDB 存储引擎是不会为表添加表级别的 S 锁或者 X 锁的。即便是表没有索引也不会使用 S 锁和 X 锁，原因会在后面讲到。所以 S 锁和 X 锁在 InnoDB 中的地位是比较尴尬的，用处不大。

IS 锁和 IX 锁

IS 锁又名意向读锁，IX 锁又名意向写锁，当我们在对使用 InnoDB 存储引擎的表的某些记录加行级读锁之前，就需要先在表级别加一个 IS 表锁，当我们在对使用 InnoDB 存储引擎的表的某些记录加行级写锁之前，那就需要先在表级别加一个 IX锁 。

IS 锁 和 IX 锁 的使命只是为了后续在加表级别的 S锁 和 X锁 时判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。也就是说当对表数据进行操作时就会加意向锁。写操作加 IX 锁，读操作加 IS 锁。

AUTO- INC 锁

在使用 MySQL 过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上单调递增的值。 系统实现这种自动给 AUTO_INCREMENT 修饰的列递增赋值的原理主要是两个：

• 采用 AUTO-INC 锁，也就是在执行插入语句时就在表级别加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把 AUTO-INC 锁释放掉。这样一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。记住该锁是语句级别的，而不是事务级别的。如果我们的插入语句在执行前不可以确定具体要插入多少条记录即无法预计即将插入记录的数量，比如说使用 INSERT … SELECT、REPLACE … SELECT 或者 LOAD DATA 这种插入语句，一般是使用 AUTO-INC 锁为 AUTO_INCREMENT 修饰的列生成对应的值。
• 轻量级的锁，其作用是在为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的 AUTO_INCREMENT 列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。
  如果我们的插入语句在执行前就可以确定具体要插入多少条记录，比如说在语句执行前就可以确定要插入2条记录，那么一般采用轻量级锁的方式对 AUTO_INCREMENT 修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。

这里可能有人要问了，什么是轻量级的锁？有没有名字？ MySQL 技术内幕（InnoDB存储引擎）这本书籍中提到了一个很好解释的概念，其实在数据库中存在两种含义的锁： Lock 与 Latch：

• Lock 个人称为重量级的锁，其锁定的对象是数据库的表、页或者行，一般在事务结束或者执行语句结束时释放，其作用域最小是一条语句执行到结束。
• Latch 称为轻量级的锁，可以理解为代码级别上的锁，其目的是用来保证并发线程操作临界资源的正确性，没错，这其实就是我们写 C++ 代码的时候用到的 mutex，其作用域最小是一行代码；

到这里大家估计就能明白什么是轻量级的锁，从宏观上来看其实可以说就是没有 Latch，这个锁只是代码层面上为了修改一个变量加的 mutex。其实就跟下面的代码一样：

std::atomic<int> auto_inc_value;
auto_inc_value += v;

在 MySQL 可以通过 innodb_autoinc_lock_mode 进行配置是否使用 Latch：

• 配置为 0 时，直接使用 AUTO-INC 锁；
• 配置为 1 时，根据不同场景选择是 AUTO- INC 锁还是轻量级的锁；
• 配置为 2 时，直接使用轻量级锁（8.0 默认配置），需要注意的是，该配置可能会造成不同事务中的插入语句为 AUTO_INCREMENT 修饰的列生成的值是交叉的（因为在 binlog 日志中生成的 SQL 语句顺序得不到保证），在有主从复制的场景中不安全。

表级锁兼容性

表级锁的兼容性矩阵如下(+为兼容，-为不兼容)：

类型	IS	IX	S	X	AUTO-INC
IS	+	+	+	-	-
IX	+	+	-	-	-
S	+	-	+	-	-
X	-	-	-	-	-
AUTO-INC	-	-	-	-	-

行级锁

整个 InnoDB 存储引擎，最重要的就是行级锁，同时行级锁也是最容易观察的锁。InnoDB 有 4 种行级锁：Record Lock、Gap Lock、Next-Key Lock、Insert Intention Lock。在代码层面上它们还有以下叫法：

• Record Lock -> LOCK_REC_NOT_GAP
• Gap Lock -> LOCK_GAP
• Next-Ley Lock -> LOCK_ORDINARY
• Insert Intention Lock -> LOCK_INSERT_INTENTION

在讲解上述这些行级锁之前，我们先构建一下表数据：

create table T (
  uid int primary key,
  data varchar(4)
) engine=innodb;
insert into T set uid=1, data='aa';
insert into T set uid=3, data='bb';
insert into T set uid=5, data='cc';
insert into T set uid=6, data='dd';
insert into T set uid=8, data='ee';

除了构造表数据外我们还需要有一种方法来观察行级锁，我们可以使用以下语句来观测：

mysql> select * from performance_schema.data_locks\G;  -- >= mysql 8.0
mysql> select * from performance_schema.innodb_locks\G; -- < mysql 8.0

接下来的讲解中涉及到的内容均在上述操作中得出。

Record Lock

Record Lock 即记录锁，该锁只能加在一行记录的索引上，如果没有记录就不会有记录锁。加锁表现如下图：

记录锁有 S 锁 和 X 锁 之分的，叫读记录锁和写记录锁，当一个事务给一条记录添加读记录锁后，其他事务也可以继续在该记录上添加读记录锁 ，但不可以添加写记录锁；当一个事务给一条记录添加写记录锁后，其他事务既不可以在该记录添加读记录锁 ，也不可以添加写记录锁。

读记录锁比较特殊，因为 MVCC 存在，读操作是可以不加锁的，所以我们加读记录锁就需要执行 select … for share 才能够主动加锁，然后就可以观察到如下内容：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T where uid=1 for share;
+-----+------+
| uid | data |
+-----+------+
|   1 | aa   |
+-----+------+
1 row in set (0.01 sec)
mysql> select object_schema,object_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+---------------+-------------+-----------+---------------+-------------+-----------+
| object_schema | object_name | lock_type | lock_mode     | lock_status | lock_data |
+---------------+-------------+-----------+---------------+-------------+-----------+
| test          | t           | TABLE     | IS            | GRANTED     | NULL      |
| test          | t           | RECORD    | S,REC_NOT_GAP | GRANTED     | 1         |
+---------------+-------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

从上面可以看到先加了一个 IS 表级锁，然后又加了一个S Record Lock (lock mode 为 S,REC_NOT_GAP 表示读记录锁)在主键为 1 的记录上。如果一个锁加在主键上，lock_data 就是主键的值。

除了在 select 时主动加读锁外，也可以主动加写锁，通过select … for update语句加的就是写锁，此后 读 加锁记录 时的操作都变成了当前读。

不同 MySQL 版本 可能支持的功能和语法会有变动，大家可以根据需要查阅 MySQL 的官网进行进一步了解。

Gap Lock

在讲解 MVCC 的时候，有提到过 REPEATED READ 隔离级别下是不能解决幻读的，但是通过主动加锁是可以避免幻读的。

幻读就是前一次查询不存在的东西下一次查询出现了，其实就是在事务 A 中的两次查询之间，事务 B执行插入操作，被事务 A 感知到了。所以要解决幻读就是要阻塞插入操作。

InnoDB 专门提供了一个 Gap Lock，又称间隙锁，通过锁住一个数据区间来解决幻读的问题。什么意思呢？看下图：

上图中描述了 Gap Lock 是如何解决幻读的，它通过锁住 (3,5) 这个开区间来阻塞插入操作。从图中也看到了Gap Lock 有 S 锁和 X 锁，但是需要记住的是 Gap Lock 仅为了防止插入操作引起的幻读而提出的，S Gap Lock 和 X Gap Lock 作用没区别，所以允许重复加锁。如果你对一条记录加了 Gap Lock (不论是 S Gap Lock 还是 X Gap Lock)，并不会限制其他事务对这个区间加 Gap Lock。

我们可以通过下面步骤演示一下插入操作被 Gap Lock 阻塞的场景：

步骤	事务 A	事务 B
第一步	begin;
第二步	select * from T where uid > 3 and uid < 5;
第三步		insert into T values(4,‘ss’);
第四步	select * from T where uid > 3 and uid < 5;
第五步		delete from T where uid=4;
第六步	select * from T where uid > 3 and uid < 5 for share;
第七步		insert into T values(4,‘ss’); – 阻塞
第八步	select * from T where uid > 3 and uid < 5;
第九步	select * from performance_schema.data_locks;
第十步	commit;

其中事务 A 中第二步和第四步是没有加锁的场景，出现了幻读；第六步和第七步则是加了锁的场景，没有出现幻读。通过第九步观察行级锁的情况如下：

mysql> select object_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-------------+-----------+------------------------+-------------+-----------+
| object_name | lock_type | lock_mode              | lock_status | lock_data |
+-------------+-----------+------------------------+-------------+-----------+
| t           | TABLE     | IX                     | GRANTED     | NULL      |
| t           | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 5         |
| t           | TABLE     | IS                     | GRANTED     | NULL      |
| t           | RECORD    | S,GAP                  | GRANTED     | 5         |
+-------------+-----------+------------------------+-------------+-----------+
4 rows in set (0.00 sec)

我们先忽略前面两条记录，那是 insert 操作加的锁。从第三行看 select … for share 加的锁，先加了一个 IS 表级锁，然后在记录 5 上面加了一个 S Gap Lock(lock mode 为 S,GAP 表示读间隙锁)，为啥 Gap Lock 是加在记录 5 上的呢？不是应该加在 (3,5) 上吗？

这是因为 InnoDB 实现上并没有表达间隙的结构，也不需要这样的结构， Gap Lock 是加在一个具体存在的记录上的，比如当 Gap Lock 加在记录 5 上时，表达的是不允许其他事务往这条记录 5 前面的间隙插入新记录。如果要表达 (8,+∞) ，则是将 Gap Lock 加在 Supremum 这条记录上。

实际上所有行级锁都是加在记录上的，所以一个记录上可能会同时存在多个行级锁，有的是单纯锁这行记录的，有的是为了锁这行记录前面的间隙的，等等，总的来说这些同时存在的锁之间是兼容的。

Next-Key Lock

当我们即要锁住某条记录，又想阻止其他事务在该记录前面的间隙插入新记录，这个时候就会使用 Next-Key Lock，它就是一个 Record Lock 和 Gap Lock 功能的集合，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前面的间隙。也就是左开右闭区间（其他数据库还有 Previous-Key Lock，原理一样，只是左闭右开而已），其加锁表现如下图：

我们也可以通过以下语句观察到 Next-Key Lock：

mysql> select * from T where uid<=1 for share;
+-----+------+
| uid | data |
+-----+------+
|   1 | aa   |
+-----+------+
1 row in set (0.03 sec)
mysql> select object_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-------------+-----------+-----------+-------------+-----------+
| object_name | lock_type | lock_mode | lock_status | lock_data |
+-------------+-----------+-----------+-------------+-----------+
| t           | TABLE     | IS        | GRANTED     | NULL      |
| t           | RECORD    | S         | GRANTED     | 1         |
+-------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

我们可以看到在 记录 1 上加了 Next-Key Lock(lock_mode为 S 则表示加的 Next-Key Lock)。

Insert Intention Lock

Insert Intention Lock 即插入意向锁，它是特殊的 Gap Lock 锁，专为 Insert 操作设计。该锁的目的是如果插入到同一行间隙中的多个事务未插入到间隙内的同一位置，则它们无需相互等待。假设有值为 4 和 7 的行记录。事务 A 插入 5，事务 B 插入 6，这两个事务之间的插入操作是不冲突的。

其实很好理解， Gap Lock 锁住的是一个不存在数据的区间，而 Insert Intention Lock 也是 Gap Lock，只是其锁定的范围从区间变成了一个具体的记录，其加锁示意图如下：

重要的事情再提一遍，所有的行级锁都是加在一个已有的记录上，比如上图两个 Insert Intention Lock 都是加在记录 6 上的。

隐式加 X Record Lock

在插入语句执行完成后， Insert Intention Lock 就会被释放，同时被插入的记录上也不会保留有任何锁。这样就会有一个问题，无法避免脏读，比如此时执行 select … from for share 即锁定读，由于被插入记录上没有其他锁，就会被当前事务获取到。

为了避免上述情况出现，在做锁定读(也经常被叫当前读)时，InnoDB 会主动加锁，逻辑处理如下：

• 对于聚集索引树上新插入的记录，会直接判断 TRX ID，如果是已经提交的事务，则直接加上当前事务想加的锁；如果是当前活跃的事务，则会主动给该记录加上一个 Record Lock，然后自身进入等待状态，直到插入该记录的事务提交后释放了 Record Lock，当前事务才会加上自己的锁。TRX ID 判断逻辑跟 MVCC ReadView 的判断逻辑是差不多；
• 对于二级索引树而言，会直接判断 PAGE_MAX_TRX_ID，如果 PAGE_MAX_TRX_ID 是已提交的事务，则直接在二级索引记录上加当前事务想加的锁，然后根据情况判断是否要在聚集索引树也加锁；如果 PAGE_MAX_TRX_ID 为活跃事务，则需要直接到聚集索引树上判断。

我们可以通过如下步骤来演示隐式加 X Record Lock 的场景：

步骤	事务 A	事务 B	事务C
第一步	begin;	begin;
第二步	select * from T where uid > 3 and uid < 5;
第三步		insert into T values(4,‘ss’);
第四步			select * from performance_schema.data_locks;
第五步	select * from T where uid > 3 and uid < 5 for share;
第六步			select * from performance_schema.data_locks;
第七步		commit;
第八步	commit;

第四步和第六步查看 data_locks 可以得到如下行级锁数据：

-- 第四步结果
mysql> select object_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-------------+-----------+-----------+-------------+-----------+
| object_name | lock_type | lock_mode | lock_status | lock_data |
+-------------+-----------+-----------+-------------+-----------+
| t           | TABLE     | IX        | GRANTED     | NULL      |
+-------------+-----------+-----------+-------------+-----------+
1 row in set (0.01 sec)

-- 第六步结果
mysql> select object_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-------------+-----------+---------------+-------------+-----------+
| object_name | lock_type | lock_mode     | lock_status | lock_data |
+-------------+-----------+---------------+-------------+-----------+
| t           | TABLE     | IX            | GRANTED     | NULL      |
| t           | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
| t           | TABLE     | IS            | GRANTED     | NULL      |
| t           | RECORD    | S             | WAITING     | 4         |
+-------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)

从上面数据可以看到，事务 B 插入的数据在事务 A 执行锁定读时加上了 X Record Lock，而事务 A 的 S Record Lock 需要等待 X Record Lock 释放才能读。

行级锁兼容性

行级锁的兼容性与加锁顺序有关，其兼容性矩阵是非对称的，下表阅读顺序为行顺序从上到下(+为兼容，-为不兼容)：

类型（已加锁\待加锁）	Record	Gap	Next-Key	Insert Intention
Record	-	+	-	+
Gap	+	+	+	-
Next-Key	-	+	-	-
Insert Intention	+	+	+	+

其中要注意的是先加 Gap Lock 再加 Insert Intention Lock ，后者会被前者阻塞，而反过来则不会

行锁和表锁的抉择

全表扫描用行级锁

很多人都会从各种文章中看到：没有索引时，InnoDB 是使用的引擎表锁，这个观点是错误的，InnoDB 只会使用行级锁。

因为 InnoDB 并不是根据每条记录来产生行锁的，相反，其是根据每个事务访问的每个页对锁进行管理的，采用位图的方式，因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一样的。

我们可以举个最简单的例子，假设一张表有 3000,000 个数据页，每个页大约有 100 条记录，那么总共就有 300,000,000 条记录。若有一个事务执行全表更新的 SQL 语句，就需要对所有记录加 X 锁。若根据每行记录产生的锁对象进行加锁，并且每个锁占用10字节，则仅对锁管理就需要差不多 3GB 的内存。

而 InnoDB 存储引擎根据页进行加锁，假设每个页存储的锁信息占用 30 个字节，则锁对象仅需 90MB 的内存。可见 InnoDB 其实没必要使用表锁。同时这种大表全遍历的情况在业务中本身就要避免，要么分表要么分库要么分区。

lock tables 下的行级锁

前面说过 lock tables 在innodb_table_locks=1的情况下会加两把锁，当前表虽然加了 InnoDB 表锁，但是还是会使用行级锁，表现如下：

mysql> set @@innodb_table_locks=1;  -- 如果不加设置，不会加表锁
mysql> set autocommit=0;
mysql> lock tables t write;
mysql> select * from t for share;
mysql> select object_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-------------+-----------+-----------+-------------+------------------------+
| object_name | lock_type | lock_mode | lock_status | lock_data              |
+-------------+-----------+-----------+-------------+------------------------+
| t           | TABLE     | X         | GRANTED     | NULL                   |
| t           | RECORD    | X         | GRANTED     | supremum pseudo-record |
| t           | RECORD    | X         | GRANTED     | 1                      |
| t           | RECORD    | X         | GRANTED     | 3                      |
| t           | RECORD    | X         | GRANTED     | 5                      |
| t           | RECORD    | X         | GRANTED     | 6                      |
| t           | RECORD    | X         | GRANTED     | 8                      |
| t           | RECORD    | X         | GRANTED     | 4                      |
+-------------+-----------+-----------+-------------+------------------------+
8 rows in set (0.00 sec)

总结

MySQL 中涉及到两个大层面的锁：server 层锁和存储引擎锁，又涉及到各种小层面，包括 MDL、MySQL 表锁、InnoDB 表锁以及 InnoDB 行锁，甚至是到代码层面上的 Latch。实际上后台开发重点关注 InnoDB的行锁其实就差不多，其他锁或多或少难接触到，但是也要对其有所了解。

本章中并没有提太多 SQL 性能优化，以及什么语句下具体会加什么锁，个人认为这种说什么语句下会加什么锁的观点虽然大多情况下是正确的，但结论有点片面。

从上面的内容，你估计也看到了 MySQL 太多系统变量可以控制 SQL 语句的加锁表现了，所以还是需要具体问题具体分析。当然分析之前得至少知道这些东西，才能够有想法的去分析。业务级别性能优化最直接办法还是 Explain 语句会比较直接。学会使用 Explain，工作中大部分性能问题其实都能解决。

本文中的内容是自己看源码或者其他文章得出的结论，所以不一定是正确的，如有问题，辛苦提出，谢谢。