MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

64次阅读

没有评论

共计 19037 个字符，预计需要花费 48 分钟才能阅读完成。

行业资讯
数据库
MySQL InnoDB 锁介绍及不同 SQL 语句分别加什么样的锁的示例分析

今天就跟大家聊聊有关 MySQL InnoDB 锁介绍及不同 SQL 语句分别加什么样的锁的示例分析，可能很多人都不太了解，为了让大家更加了解，丸趣 TV 小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

文章正文开始

“加什么样的锁”与以下因素相关

当前事务的隔离级别

SQL 是一致性非锁定读 (consistent nonlocking read) 还是 DML(INSERT/UPDATE/DELETE)或锁定读(locking read)

SQL 执行时是否使用了索引，所使用索引的类型(主键索引，辅助索引、唯一索引)

我们先分别介绍这几个因素

一、隔离级别(isolation level)

数据库事务需要满足 ACID 原则，“I”即隔离性，它要求两个事务互不影响，不能看到对方尚未提交的数据。数据库有 4 种隔离级别 (isolation level)，按着隔离性从弱到强(相应的，性能和并发性从强到弱) 分别是

Read Uncommitted。下面简称 RU

Read Committed。下面简称 RC

Repeatable Read(MySQL 的默认隔离级别)。下面简称 RR

Serializable

“I”即隔离性正是通过锁机制来实现的。提到锁就会涉及到死锁，需要明确的是死锁的可能性并不受隔离级别的影响，因为隔离级别改变的是读操作的行为，而死锁是由于写操作产生的。

--  查看事务的   全局和 session  隔离级别( MySQL 5.7.19 及之前使用 tx_isolation) select @@global.transaction_isolation, @@session.transaction_isolation; --  设置   全局   事务隔离级别为 repeatable read set global transaction isolation level repeatable read --  设置   当前 session  事务隔离级别为 read uncommitted set session transaction isolation level read uncommitted

事务隔离级别设置和查看的详细语法请见：https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

二、一致性非锁定读和锁定读

InnoDB 有两种不同的 SELECT，即普通 SELECT 和锁定读 SELECT。锁定读 SELECT 又有两种，即 SELECT … FOR SHARE 和 SELECT … FOR UPDATE; 锁定读 SELECT 之外的则是普通 SELECT。

不同的 SELECT 是否都需要加锁呢?

普通 SELECT 时使用一致性非锁定读，不加锁;

锁定读 SELECT 使用锁定读，加锁;

此外，DML(INSERT/UPDATE/DELETE)时，需要先查询表中的记录，此时也使用锁定读，加锁;

FOR SHARE 语法是 MySQL 8.0 时加入的，FOR SHARE 和 LOCK IN SHARE MODE 是等价的，但，FOR SHARE 用于替代 LOCK IN SHARE MODE，不过，为了向后兼容，LOCK IN SHARE MODE 依然可用。

1、一致性非锁定读(consistent nonlocking read)

InnoDB 采用多版本并发控制 (MVCC, multiversion concurrency control) 来增加读操作的并发性。MVCC 是指，InnoDB 使用基于时间点的快照来获取查询结果，读取时在访问的表上不设置任何锁，因此，在事务 T1 读取的同一时刻，事务 T2 可以自由的修改事务 T1 所读取的数据。这种读操作被称为一致性非锁定读。这里的读操作就是普通 SELECT。

隔离级别为 RU 和 Serializable 时不需要 MVCC，因此，只有 RC 和 RR 时，才存在 MVCC，才存在一致性非锁定读。

一致性非锁定读在两种隔离级别 RC 和 RR 时，是否有什么不同呢? 是的，两种隔离级别下，拍得快照的时间点不同

RC 时，同一个事务内的每一个一致性读总是设置和读取它自己的 *** 快照。也就是说，每次读取时，都再重新拍得一个 *** 的快照(所以，RC 时总是可以读取到 *** 提交的数据)。

RR 时，同一个事务内的所有的一致性读总是读取同一个快照，此快照是执行该事务的 *** 个一致性读时所拍得的。

2、锁定读(locking read)

如果你先查询数据，然后，在同一个事务内插入 / 更新相关数据，普通的 SELECT 语句是不能给你足够的保护的。其他事务可以更新 / 删除你刚刚查出的数据行。InnoDB 提供两种锁定读，即：SELECT … FOR SHARE 和 SELECT … FOR UPDATE。它俩都能提供额外的安全性。

这两种锁定读在搜索时所遇到的 (注意：不是最终结果集中的) 每一条索引记录 (index record) 上设置排它锁或共享锁。此外，如果当前隔离级别是 RR，它还会在每个索引记录前面的间隙上设置排它的或共享的 gap lock(排它的和共享的 gap lock 没有任何区别，二者等价)。

看完背景介绍，我们再来看一下 InnoDB 提供的各种锁。

三、InnoDB 提供的 8 种不同类型的锁

InnoDB 一共有 8 种锁类型，其中，意向锁 (Intention Locks) 和自增锁 (AUTO-INC Locks) 是表级锁，剩余全部都是行级锁。此外，共享锁或排它锁 (Shared and Exclusive Locks) 尽管也作为 8 种锁类型之一，它却并不是具体的锁，它是锁的模式，用来“修饰”其他各种类型的锁。

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

MySQL8.0 删除了 information_schema.innodb_locks，添加了 performance_schema.data_locks，可以通过 performance_schema.data_locks 查看事务的锁情况，和 MySQL5.7 及之前不同，performance_schema.data_locks 不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁，也就是说即使事务并未被阻塞，依然可以看到事务所持有的锁(不过，正如文中 *** 一段所说，performance_schema.data_locks 并不总是能看到全部的锁)。表名的变化其实还反映了 8.0 的 performance_schema.data_locks 更为通用了，即使你使用 InnoDB 之外的存储引擎，你依然可以从 performance_schema.data_locks 看到事务的锁情况。

performance_schema.data_locks 的列 LOCK_MODE 表明了锁的类型，下面在介绍各种锁时，我们同时指出锁的 LOCK_MODE。

1、共享锁或排它锁(Shared and Exclusive Locks)

它并不是一种锁的类型，而是其他各种锁的模式，每种锁都有 shard 或 exclusive 两种模式。

当我们说到共享锁 (S 锁) 或排它锁 (X 锁) 时，一般是指行上的共享锁或者行上的排它锁。需要注意的是，表锁也存在共享锁和排它锁，即表上的 S 锁和表上的 X 锁，表上的锁除了这两种之外，还包括下面将会提到的意向共享锁 (Shard Intention Locks) 即 IS 锁、意向排它锁 (Exclusive Intention Locks) 即 IX 锁。表上的锁，除了这四种之外，还有其他类型的锁，这些锁都是在访问表的元信息时会用到的(create table/alter table/drop table 等)，本文不讨论这些锁，详细可见：常用 SQL 语句的 MDL 加锁源码分析。

数据行 r 上共享锁 (S 锁) 和排它锁 (X 锁) 的兼容性如下：

假设 T1 持有数据行 r 上的 S 锁，则当 T2 请求 r 上的锁时：

T2 请求 r 上的 S 锁，则，T2 立即获得 S 锁。T1 和 T2 同时都持有 r 上的 S 锁。

T2 请求 r 上的 X 锁，则，T2 无法获得 X 锁。T2 必须要等待直到 T1 释放 r 上的 S 锁。

假设 T1 持有 r 上的 X 锁，则当 T2 请求 r 上的锁时：

T2 请求 r 上的任何类型的锁时，T2 都无法获得锁，此时，T2 必须要等待直到 T1 释放 r 上的 X 锁

2、意向锁(Intention Locks)

表锁。含义是已经持有了表锁，稍候将获取该表上某个 / 些行的行锁。有 shard 或 exclusive 两种模式。

LOCK_MODE 分别是：IS 或 IX。

意向锁用来锁定层级数据结构，获取子层级的锁之前，必须先获取到父层级的锁。可以这么看 InnoB 的层级结构：InnoDB 所有数据是 schema 的集合，schema 是表的集合，表是行的集合。意向锁就是获取子层级 (数据行) 的锁之前，需要首先获取到父层级 (表) 的锁。

意向锁的目的是告知其他事务，某事务已经锁定了或即将锁定某个 / 些数据行。事务在获取行锁之前，首先要获取到意向锁，即：

事务在获取行上的 S 锁之前，事务必须首先获取表上的 IS 锁或表上的更强的锁。

事务在获取行上的 X 锁之前，事务必须首先获取表上的 IX 锁。

事务请求锁时，如果所请求的锁与已存在的锁兼容，则该事务可以成功获得所请求的锁; 如果所请求的锁与已存在的锁冲突，则该事务无法获得所请求的锁。

表级锁 (table-level lock) 的兼容性矩阵如下：

对于上面的兼容性矩阵，一定注意两点：

在上面的兼容性矩阵中，S 是表的 (不是行的) 共享锁，X 是表的 (不是行的) 排它锁。

意向锁 IS 和 IX 和任何行锁都兼容(即：和行的 X 锁或行的 S 锁都兼容)。

所以，意向锁只会阻塞全表请求(例如：LOCK TABLES … WRITE)，不会阻塞其他任何东西。因为 LOCK TABLES … WRITE 需要设置 X 表锁，这会被意向锁 IS 或 IX 所阻塞。

InnoDB 允许表锁和行锁共存，使用意向锁来支持多粒度锁(multiple granularity locking)。意向锁如何支持多粒度锁呢，我们举例如下

T1: SELECT * FROM t1 WHERE i=1 FOR UPDATE;

T2: LOCK TABLE t1 WRITE;

T1 执行时，需要获取 i = 1 的行的 X 锁，但，T1 获取行锁前，T1 必须先要获取 t1 表的 IX 锁，不存在冲突，于是 T1 成功获得了 t1 表的 IX 锁，然后，又成功获得了 i = 1 的行的 X 锁;T2 执行时，需要获取 t1 表的 X 锁，但，T2 发现，t1 表上已经被设置了 IX 锁，因此，T2 被阻塞(因为表的 X 锁和表的 IX 锁不兼容)。

假设不存在意向锁，则：

T1 执行时，需要获取 i = 1 的行的 X 锁(不需要获取 t1 表的意向锁了);T2 执行时，需要获取 t1 表的 X 锁，T2 能否获取到 T1 表的 X 锁呢?T2 无法立即知道，T2 不得不遍历表 t1 的每一个数据行以检查，是否某个行上已存在的锁和自己即将设置的 t1 表的 X 锁冲突，这种的判断方法效率实在不高，因为需要遍历整个表。

所以，使用意向锁，实现了“表锁是否冲突”的快速判断。意向锁就是协调行锁和表锁之间的关系的，或者也可以说，意向锁是协调表上面的读写锁和行上面的读写锁 (也就是不同粒度的锁) 之间的关系的。

3、索引记录锁(Record Locks)

也就是所谓的行锁，锁定的是索引记录。行锁就是索引记录锁，所谓的“锁定某个行”或“在某个行上设置锁”，其实就是在某个索引的特定索引记录 (或称索引条目、索引项、索引入口) 上设置锁。有 shard 或 exclusive 两种模式。

LOCK_MODE 分别是：S,REC_NOT_GAP 或 X,REC_NOT_GAP。

行锁就是索引记录锁，索引记录锁总是锁定索引记录，即使表上并未定义索引。表未定义索引时，InnoDB 自动创建隐藏的聚集索引(索引名字是 GEN_CLUST_INDEX)，使用该索引执行 record lock。

4、间隙锁(Gap Locks)

索引记录之间的间隙上的锁，锁定尚未存在的记录，即索引记录之间的间隙。有 shard 或 exclusive 两种模式，但，两种模式没有任何区别，二者等价。

LOCK_MODE 分别是：S,GAP 或 X,GAP。

gap lock 可以共存(co-exist)。事务 T1 持有某个间隙上的 gap lock 并不能阻止事务 T2 同时持有同一个间隙上的 gap lock。shared gap lock 和 exclusive gap lock 并没有任何的不同，它俩并不冲突，它俩执行同样的功能。

gap lock 锁住的间隙可以是 *** 个索引记录前面的间隙，或相邻两条索引记录之间的间隙，或 *** 一个索引记录后面的间隙。

索引是 B + 树组织的，因此索引是从小到大按序排列的，在索引记录上查找给定记录时，InnoDB 会在 *** 个不满足查询条件的记录上加 gap lock，防止新的满足条件的记录插入。

上图演示了：InnoDB 在索引上扫描时，找到了 c2=11 的记录，然后，InnoDB 接着扫描，它发现下一条记录是 c2=18，不满足条件，InnoDB 遇到了 *** 个不满足查询条件的记录 18，于是 InnoDB 在 18 上设置 gap lock，此 gap lock 锁定了区间(11, 18)。

为什么需要 gap lock 呢?gap lock 存在的唯一目的就是阻止其他事务向 gap 中插入数据行，它用于在隔离级别为 RR 时，阻止幻影行 (phantom row) 的产生; 隔离级别为 RC 时，搜索和索引扫描时，gap lock 是被禁用的，只在外键约束检查和重复 key 检查时 gap lock 才有效，正是因为此，RC 时会有幻影行问题。

gap lock 是如何阻止其他事务向 gap 中插入数据行的呢? 看下图

索引是 B + 树组织的，因此索引是从小到大按序排列的，如果要插入 10，那么能插入的位置只能是上图中标红的区间。在 10 和 10 之间插入时，我们就认为是插入在 *** 面的 10 的后面。如果封锁了标红的区间，那么其他事务就无法再插入 10 啦。

问题一：当 T2 要插入 10 时，上图哪些地方允许插入(注意：索引是有序的哦)?

答：(8, 10)和(10,11)。在 10 和 10 之间插入，我们就认为是插入在 *** 的 10 后面。

只要封锁住图中标红的区间，T2 就无法再插入 10 啦。上面这两个区间有什么特点吗? 对，这两个区间就是：满足条件的每一条记录前面的间隙，及，*** 一条不满足条件的记录前面的间隙。InnoDB 使用下一个键锁 (Next-Key Locks) 或间隙锁 (Gap Locks) 来封锁这种区间。

问题二：gap lock 是用来阻塞插入新数据行的，那么，T2, insert into g values(z , 9) 会被阻塞吗? 插入 (z , 8)，(z , 10)，(z , 11) 呢?

答：上图中，T1 的 update 设置的 gap lock 是 (8, 10)和(10,11)，而，insert intention lock 的范围是(插入值, 向下的一个索引值)。insert intention lock 的详细介绍请见下面的 6. 插入意向锁(Insert Intention Locks)。

于是，对于上面这些插入值，得到的 insert intention lock 如下：

插入 (z , 8)时，insert intention lock 是 (8, 10) — 冲突，与 gap lock (8, 10)重叠了

插入 (z , 9)时，insert intention lock 是 (9, 10) — 冲突，与 gap lock (8, 10)重叠了

插入 (z , 10)时，insert intention lock 是 (10, 11) — 冲突，与 gap lock (10, 11)重叠了

插入 (z , 11)时，insert intention lock 是 (11, 15) — 不冲突

事实是不是这样呢，看下图

是的，和我们分析的一致，为了看的更清楚，我们把结果列成图表如下

问题三：“gap 是解决 phantom row 问题的”，插入会导致 phantom row，但更新也一样也会产生 phantom row 啊。

例如，上图的 T1 和 T2，T1 把所有 i = 8 的行更新为 108，T2 把 i =15 的行更新为 8，如果 T2 不被阻塞，T1 的 WHERE 条件岂不是多出了一行，即：T1 出现了 phantom row?

答：nice question。我们自己来分析下 T1 和 T2 分别加了哪些锁

T1 加的锁：idx_i 上的 next-key lock (5, 8]，PRIMARY 上的 b，以及 idx_i 上的 gap lock (8,10)

T2 加的锁：idx_i 上的 next-key lock (11, 15]，PRIMARY 上的 f，以及 idx_i 上的 gap lock (15,108)，*** 这个 gap lock 是因为 T1 在 idx_i 上加了新值 108

根据上面的分析，T1 和 T2 的锁并没有重叠，即我们分析的结果是：T2 不会被阻塞。

但，上图清楚的表明 T2 确实被阻塞了，原因竟然是：T2 insert intention lock 和 T1 gap lock(8, 10)冲突了。很奇怪，T2 是更新语句，为什么会有 insert intention lock 呢?

我不知道确切的原因，因为我没找到文档说这事。根据我的推断，update … set 成功找到结果集然后执行更新时，在即将被更新进入行的新值上设置了 insert intention lock(如果找不到结果集，则就不存在 insert intention lock 啦)，因此，T2 在 idx_i 上的新值 8 上设置了 insert intention lock(8, 10)。最终，T2 insert intention lock(8, 10) 与 T1 gap lock(8, 10)冲突啦，T2 被阻塞。

因此，update … set 成功找到结果集时，会在即将被更新进入行的新值上设置 index record lock 以及 insert intention lock。如前所述，insert intention lock 的范围是 (插入值，下一个值)，如果 T2 是 update g set i=9 where i=15; 那么 update … set 所设置的新值是 9，则 T2 insert intention lock 就是(9, 10) 啦，它依然会和 T1 gap lock(8, 10)冲突，是这样吗? 确实是的，感兴趣的同学可以试试。

5、下一个键锁(Next-Key Locks)

next-key lock 是 (索引记录上的索引记录锁) + (该索引记录前面的间隙上的锁) 二者的合体，它锁定索引记录以及该索引记录前面的间隙。有 shard 或 exclusive 两种模式。

LOCK_MODE 分别是：S 或 X。

当 InnoDB 搜索或扫描索引时，InnoDB 在它遇到的索引记录上所设置的锁就是 next-key lock，它会锁定索引记录本身以及该索引记录前面的 gap(gap immediately before that index record)。即：如果事务 T1 在索引记录 r 上有一个 next-key lock，则 T2 无法在紧靠着 r 前面的那个间隙中插入新的索引记录(gap immediately before r in the index order)。

next-key lock 还会加在“supremum pseudo-record”上，什么是 supremum pseudo-record 呢? 它是索引中的伪记录(pseudo-record)，代表此索引中可能存在的 *** 值，设置在 supremum pseudo-record 上的 next-key lock 锁定了“此索引中可能存在的 *** 值”，以及这个值前面的间隙，“此索引中可能存在的 *** 值”在索引中是不存在的，因此，该 next-key lock 实际上锁定了“此索引中可能存在的 *** 值”前面的间隙，也就是此索引中当前实际存在的 *** 值后面的间隙。例如，下图中，supremum pseudo-record 上的 next-key lock 锁定了区间(18, 正无穷)，正是此 next-key lock 阻止其他事务插入例如 19, 100 等更大的值。

supremum pseudo-record 上的 next-key lock 锁定了“比索引中当前实际存在的 *** 值还要大”的那个间隙，“比大还大”，“bigger than bigger”

6、插入意向锁(Insert Intention Locks)

一种特殊的 gap lock。INSERT 操作插入成功后，会在新插入的行上设置 index record lock，但，在插入行之前，INSERT 操作会首先在索引记录之间的间隙上设置 insert intention lock，该锁的范围是(插入值, 向下的一个索引值)。有 shard 或 exclusive 两种模式，但，两种模式没有任何区别，二者等价。

LOCK_MODE 分别是：S,GAP,INSERT_INTENTION 或 X,GAP,INSERT_INTENTION。

insert intention lock 发出按此方式进行插入的意图：多个事务向同一个 index gap 并发进行插入时，多个事务无需相互等待。

假设已存在值为 4 和 7 的索引记录，事务 T1 和 T2 各自尝试插入索引值 5 和 6，在得到被插入行上的 index record lock 前，俩事务都首先设置 insert intention lock，于是，T1 insert intention lock (5, 7)，T2 insert intention lock (6, 7)，尽管这两个 insert intention lock 重叠了，T1 和 T2 并不互相阻塞。

如果 gap lock 或 next-key lock 与 insert intention lock 的范围重叠了，则 gap lock 或 next-key lock 会阻塞 insert intention lock。隔离级别为 RR 时正是利用此特性来解决 phantom row 问题; 尽管 insert intention lock 也是一种特殊的 gap lock，但它和普通的 gap lock 不同，insert intention lock 相互不会阻塞，这极大的提供了插入时的并发性。总结如下：

gap lock 会阻塞 insert intention lock。事实上，gap lock 的存在只是为了阻塞 insert intention lock

gap lock 相互不会阻塞

insert intention lock 相互不会阻塞

insert intention lock 也不会阻塞 gap lock

INSERT 插入行之前，首先在索引记录之间的间隙上设置 insert intention lock，操作插入成功后，会在新插入的行上设置 index record lock。

我们用下面三图来说明 insert intention lock 的范围和特性

上图演示了：T1 设置了 gap lock(13, 18)，T2 设置了 insert intention lock(16, 18)，两个锁的范围重叠了，于是 T1 gap lock(13, 18)阻塞了 T2 insert intention lock(16, 18)。

上图演示了：T1 设置了 insert intention lock(13, 18)、index record lock 13;T2 设置了 gap lock(17, 18)。尽管 T1 insert intention lock(13, 18) 和 T2 gap lock(17, 18)重叠了，但，T2 并未被阻塞。因为 insert intention lock 并不阻塞 gap lock。

上图演示了：T1 设置了 insert intention lock(11, 18)、index record lock 11;T2 设置了 next-key lock(5, 11]、PRIMARY 上的 index record lock b、gap lock(11, 18)。此时：T1 index record lock 11 和 T2 next-key lock(5, 11]冲突了，因此，T2 被阻塞。

7、自增锁(AUTO-INC Locks)

表锁。向带有 AUTO_INCREMENT 列的表时插入数据行时，事务需要首先获取到该表的 AUTO-INC 表级锁，以便可以生成连续的自增值。插入语句开始时请求该锁，插入语句结束后释放该锁(注意：是语句结束后，而不是事务结束后)。

你可能会想，日常开发中，我们所有表都使用 AUTO_INCREMENT 作主键，所以会非常频繁的使用到该锁。不过，事情可能并不像你想的那样。在介绍 AUTO-INC 表级锁之前，我们先来看下和它密切相关的 SQL 语句以及系统变量 innodb_autoinc_lock_mode

INSERT-like 语句

insert

insert … select

replace

replace … select

load data

外加，simple-inserts, bulk-inserts, mixed-mode-inserts

simple-inserts

待插入记录的条数，提前就可以确定 (语句初始被处理时就可以提前确定) 因此所需要的自增值的个数也就可以提前被确定。

包括：不带嵌入子查询的单行或多行的 insert, replace。不过，insert … on duplicate key update 不是

bulk-inserts

待插入记录的条数，不能提前确定，因此所需要的自增值的个数也就无法提前确定

包括：insert … select, replace … select, load data

在这种情况下，InnoDB 只能每次一行的分配自增值。每当一个数据行被处理时，InnoDB 为该行 AUTO_INCREMENT 列分配一个自增值

mixed-mode-inserts

也是 simple-inserts 语句，但是指定了某些 (非全部) 自增列的值。也就是说，待插入记录的条数提前能知道，但，指定了部分的自增列的值。

INSERT INTO t1 (c1,c2) VALUES (1, a), (NULL, b), (5, c), (NULL, d

INSERT … ON DUPLICATE KEY UPDATE 也是 mixed-mode，最坏情况下，它就是 INSERT 紧跟着一个 UPDATE，此时，为 AUTO_INCREMENT 列所分配的值在 UPDATE 阶段可能用到，也可能用不到。

再看一下系统变量 innodb_autoinc_lock_mode，它有三个候选值 0，1，和 2

8.0.3 之前，默认值是 1，即“连续性的锁定模式(consecutive lock mode)”;8.0.3 及之后默认值是 2，即“交织性锁定模式(interleaved lock mode)”

a. 当 innodb_autoinc_lock_mode= 0 时，INSERT-like 语句都需要获取到 AUTO-INC 表级锁;

b. 当 innodb_autoinc_lock_mode= 1 时，如果插入行的条数可以提前确定，则无需获得 AUTO-INC 表级锁; 如果插入行的条数无法提前确定，则就需要获取 AUTO-INC 表级锁。因此，simple-inserts 和 mixed-mode inserts 都无需 AUTO-INC 表级锁，此时，使用轻量级的 mutex 来互斥获得自增值;bulk-inserts 需要获取到 AUTO-INC 表级锁;

c. 当 innodb_autoinc_lock_mode= 2 时，完全不再使用 AUTO-INC 表级锁;

我们生产数据库版本是 5.6.23-72.1，innodb_autoinc_lock_mode=1，而且，我们日常开发中用到大都是 simple-inserts，此时根本就不使用 AUTO-INC 表级锁，所以，AUTO-INC 表级锁用到的并不多哦。

LOCK_MODE：AUTO-INC 表级锁用到的并不多，且，AUTO-INC 锁是在语句结束后被释放，较难在 performance_schema.data_locks 中查看到，因此，没有进行捕获。感兴趣的同学可以使用 INSERT … SELECT 捕获试试。

8、空间索引(Predicate Locks for Spatial Indexes)

我们平时很少用到 MySQL 的空间索引。所以，本文忽略此类型的锁

到此为止，MySQL InnoDB 8 种类型的锁我们就介绍完了。我们以一个例子结束 8 种类型的介绍。

T1 先执行，事务 ID 是 8428;T2 后执行，事务 ID 是 8429

上图演示了：

任何事务，在锁定行之前，都需要先加表级锁 intention lock，即：第三行的 IX 和 *** 行的 IX。

idx_c 是辅助索引，InnoDB 扫描 idx_c 时遇到了 c =222，于是，在 idx_c 上加了 next-key lock，即：第四行的 X。next-key lock 就是 index record lock+gap lock，于是此 next-key lock 锁定了 idx_c 上值为 222 的索引记录，以及 222 前面的间隙，也就是间隙(22, 222)。

idx_c 是辅助索引，在主键索引之外的任何索引上加 index record lock 时，都需要在该行的主键索引上再加 index record lock，于是，又在 PRIMARY 上添加了 index record lock，即：第五行的 X,REC_NOT_GAP。

InnoDB 扫描完 c =222 后，又扫描到了 c =2222，这是 idx_c 上，*** 个不满足索引扫描条件的索引记录，于是 InnoDB 在 c =2222 上加 gap lock，c=2222 上的 gap lock 锁定的范围是“idx_c 上 2222 前面的间隙”，这本应该是(222, 2222)，但，T1 即将在 idx_c 上插入 c =224，于是，c=2222 上的 gap lock 锁定的范围是(224, 2222)。即：第六行的 X,GAP。

InnoDB 即将在 idx_c 上插入 c =224，224 也是不满足 c =222 的，于是 InnoDB 在 c =224 上加 gap lock，该 gap lock 锁定了 224 前面的间隙，也就是(222, 224)，即，第七行的 X,GAP。

T2 执行 INSERT 成功后，会在新插入行的加 index record lock，但，T2 在插入之前，首先要作的是得到表级锁 intention lock 以及设置表的每个索引的 insert intention lock，该锁的范围是 (插入值, 向下的一个索引值)，于是，在设置 idx_c 上的 insert intention lock 范围就是(226, 2222)，这个范围和事务 T1 第六行 gap lock 范围(224, 2222) 重叠。于是，事务 T2 被阻塞了，T2 必须等待，直到 T1 释放第六行的 gap lock。

performance_schema.data_locks 表中并不能看到 T2 的全部锁，比如，T2 也得在 iux_b 上设置 insert intention lock，但，performance_schema.data_locks 中并没有这个锁。关于 performance_schema.data_locks 中显示了哪些锁，请见本文 *** 一段。

把这些锁及其范围列出来如下图所示

四、不同的 SQL 加了什么样的锁?

OK，我们已经了解了 InnoDB 各种不同类型的锁，那么，不同 SQL 语句各加了什么样的锁呢

我们用最朴素的想法来思考一下，用锁作什么呢? 锁要作的就是达到事务隔离的目的，即：两个并发执行的事务 T1 和 T2，如果 T1 正在修改某些行，那么，T2 要并发读取 / 修改 / 插入满足 T1 查询条件的行时，T2 就必须被阻塞，这是锁存在的根本原因。index record lock, gap lock, next-key lock 都是实现手段，这些手段使得锁既能达到目的，还能实现 *** 的并发性。所以，当我们考虑事务 T1 中的 SQL 上加了什么锁时，就想一下，当 T1 执行时，如果并发的事务 T2 不会触及到 T1 的行，则 T2 无需被阻塞，如果 T2 的要读取 / 修改 / 插入满足 T1 条件的行时，T2 就得被 T1 阻塞。而 T1 阻塞 T2 的具体实现就是：T1 在已存在的行上加 index record lock 使得 T2 无法触碰已存在的行，以及，T1 在不存在的行上加 gap lock 使得 T2 无法插入新的满足条件的行。

前面我们说过“加什么样的锁”与以下因素相关

当前事务的隔离级别

SQL 是一致性非锁定读 (consistent nonlocking read) 还是 DML 或锁定读(locking read)

SQL 执行时是否使用了索引，所使用索引的类型(主键索引，辅助索引、唯一索引)

我们来看一下，不同的隔离级别下，使用不同的索引时，分别加什么锁。在讨论之前，我们先剔除无需讨论的情况

首先，普通 SELECT 使用一致性非锁定读，因此根本不存在锁。无需讨论;

再者，作为开发者，我们几乎从来不会使用到隔离级别 RU 和 Serializable。这两个隔离级别无需讨论。

于是，剩下的就是给定锁定读 SELECT 或 DML(INSERT/UPDATE/DELETE)语句，在不同隔离级别下，使用不同类型的索引时，分别会加什么样的锁? 直接给出答案，其加锁原则如下

（一）、RR 时，如果使用非唯一索引进行搜索或扫描，则在所扫描的每一个索引记录上都设置 next-key lock。

这里“所扫描的每一个索引记录”是指当扫描执行计划中所使用的索引时，搜索遇到的每一条记录。WHERE 条件是否排除掉某个数据行并没有关系，InnoDB 并不记得确切的 WHERE 条件，InnoDB 倔强的只认其扫描的索引范围(index range)。

你可能觉得 InnoDB 在设置锁时蛮不讲理，竟然不管 WHERE 条件排除掉的某些行，这不是大大增加了锁的范围了嘛。不过，等我们了解了 MySQL 执行 SQL 时的流程，这就好理解了。MySQL 的执行计划只会选择一个索引，使用一个索引来进行扫描，MySQL 执行 SQL 语句的流程是，先由 InnoDB 引擎执行索引扫描，然后，把结果返回给 MySQL 服务器，MySQL 服务器会再对该索引条件之外的其他查询条件进行求值，从而得到最终结果集，而加锁时只考虑 InnoDB 扫描的索引，由 MySQL 服务器求值的其他 WHERE 条件并不考虑。当然，MySQL 使用 index_merge 优化时会同时使用多个索引的，不过，这个时候设置锁时也并不特殊，同样，对于所用到的每一个索引，InnoDB 在所扫描的每一个索引记录上都设置 next-key lock。

加的锁一般是 next-key lock，这种锁住了索引记录本身，还锁住了每一条索引记录前面的间隙，从而阻止其他事务向索引记录前面紧接着的间隙中插入记录。

如果在搜索中使用了辅助索引(secondary index)，并且在辅助索引上设置了行锁，则，InnoDB 还会在相应的聚集索引上设置锁; 表未定义聚集索引时，InnoDB 自动创建隐藏的聚集索引(索引名字是 GEN_CLUST_INDEX)，当需要在聚集索引上设置锁时，就设置到此自动创建的索引上。

（二）、RR 时，如果使用了唯一索引的唯一搜索条件，InnoDB 只在满足条件的索引记录上设置 index record lock，不锁定索引记录前面的间隙; 如果用唯一索引作范围搜索，依然会锁定每一条被扫描的索引记录前面的间隙，并且再在聚集索引上设置锁。

（三）、RR 时，在 *** 个不满足搜索条件的索引记录上设置 gap lock 或 next-key lock。

一般，等值条件时设置 gap lock，范围条件时设置 next-key lock。此 gap lock 或 next-key lock 锁住 *** 个不满足搜索条件的记录前面的间隙。

（四）、RR 时，INSERT 在插入新行之前，必须首先为表上的每个索引设置 insert intention lock。

每个 insert intention lock 的范围都是(待插入行的某索引列的值, 此索引上从待插入行给定的值向下的 *** 个索引值)。只有当 insert intention lock 与某个 gap lock 或 next-key lock 冲突时，才能在 performance_schema.data_locks 看到 insert intention lock。

（五）、RC 时，InnoDB 只在完全满足 WHERE 条件的行上设置 index record lock。

（六）、RC 时，禁用了 gap lock。

正因为此，RC 时不存在 gap lock 或 next-key lock。这是为什么呢? 我们想一想啊，gap lock 是用来解决 phantom row 问题的，gap lock 封锁的区间内不能插入新的行，因为插入时的 insert intention lock 会和 gap lock 冲突，从而阻止了新行的插入。但，隔离级别 RC 是允许 phantom row 的，因此 RC 时 gap lock 是被禁用的。

（七）、RR 或 RC 时，对于主键或唯一索引，当有重复键错误 (duplicate-key error) 时，会在重复的索引记录上设置 shared next-key lock 或 shared index record lock。这可能会导致死锁。

假设 T1, T2, T3 三个事务，T1 已经持有了 X 锁，T2 和 T3 发生了重复键错误，因此 T2 和 T3 都在等待获取 S 锁，这个时候，当 T1 回滚或提交释放掉了 X 锁，则 T2 和 T3 就都获取到了 S 锁，并且，T2 和 T3 都请求 X 锁，“T2 和 T3 同时持有 S 锁，且都在请求 X 锁”，于是死锁就产生了。

好了，规则都列出来了，是时候实践一把了。下面在展示锁时，我们同时指出了当前所使用的隔离级别，表上的索引以及事务的 SQL 语句。

实践一：搜索时无法使用索引，即全表扫描时，InnoDB 在表的全部行上都加锁

上图演示了：搜索条件无法使用索引时，InnoDB 不得不在表的全部行上都加锁。所以，索引实在太重要了，查询时，它能加快查询速度; 更新时，除了快速找到指定行，它还能减少被锁定行的范围，提高插入时的并发性。

实践二：唯一索引和非唯一索引、等值查询和范围查询加锁的不同

搜索时使用唯一索引作等值查询时，InnoDB 只需要加 index record lock; 搜索时使用唯一索引作范围查询时或使用非唯一索引作任何查询时，InnoDB 需要加 next-key lock 或 gap lock。

示例 1 演示了：使用非唯一索引 idx_c 搜索或扫描时，InnoDB 要锁住索引本身，还要锁住索引记录前面的间隙，即 next-key lock: X 和 gap lock: X,GAP。next-key lock 既锁住索引记录本身，还锁住该索引记录前面的间隙，gap lock 只锁住索引记录前面的间隙。等值条件时，在 *** 一个不满足条件的索引记录上设置 gap lock。

示例 2 演示了：使用唯一索引 iux_b 的唯一搜索条件，即，使用唯一索引执行等值查找时，InnoDB 只需锁住索引本身，即 index record lock: X, REC_NOT_GAP，并不锁索引前面的间隙。

示例 3 演示了：使用唯一索引 iux_b 进行范围扫描时，依然需要锁定扫描过的每一个索引记录，并且锁住每一条索引记录前面的间隙，即 next-key lock: X。范围条件时，在 *** 一个不满足条件的索引记录上设置 next-key lock。

实践三：不同隔离级别加锁的不同

无论何种隔离级别，SQL 语句执行时，都是先由 InnoDB 执行索引扫描，然后，返回结果集给 MySQL 服务器，MySQL 服务器再对该索引条件之外的其他查询条件进行求值，从而得到最终结果集。

上图中，在不同的隔离级别下，执行了相同的 SQL。无论何种隔离级别，PRIMARY 上的 index record lock 总是会加的，我们不讨论它。在 idx_b 上，隔离级别为 RC 时，InnoDB 加了 index record lock，即：X,REC_NOT_GAP，隔离级别为 RR 时，InnoDB 加了 next-key lock，即 X。注意：RC 时没有 gap lock 或 next-key lock 哦。

上图演示了：事务的隔离级别也会影响到设置哪种锁。如我们前面所说，gap lock 是用来阻止 phantom row 的，而 RC 时是允许 phantom row，所以，RC 时禁用了 gap lock。因此，上图中，RC 时没有在索引上设置 gap lock 或 next-key lock。

实践四：操作不存在的索引记录时，也需要加锁

上图中，idx_b 上并不存在 b =266 的索引记录，那么，当更新 b =266 的记录时，是否需要加锁呢? 是的，也需要加锁

无论 b =266 是否存在，RR 时，InnoDB 在 *** 个不满足搜索条件的索引记录上设置 gap lock 或 next-key lock。一般，等值条件时设置 gap lock，范围条件时设置 next-key lock。上图中是等值条件，于是 InnoDB 设置 gap lock，即上图的 X,GAP，其范围是(226, 2222)，正是此 gap lock 使得并发的事务无法插入 b 列大于等于 266 的值，RC 时，由于 gap lock 是被禁止的，因此，并不会加 gap lock，并发的事务可以插入 b 列大于等于 266 的值。

上图演示了：操作不存在的索引记录时，也需要加锁。

实践五：重复键错误 (duplicate-key error) 时，会加共享锁。这可能会导致死锁。

对于主键或唯一索引，当有重复键错误 (duplicate-key error) 时，会在重复的索引记录上设置 shared next-key lock 或 shared index record lock。这可能会导致死锁。

上图演示了：T1 在主键 1 上设置 exclusive index record lock。T2 和 T3 插入时，会产生重复键错误，于是 T2 和 T3 都在主键 1 上设置了 shared next-key lock。如上图所示

如果此时，T1 rollback 释放掉其所持有的 index record lock，则 T2 和 T3 等待获取的 shared next-key lock 都成功了，然后，T2 和 T3 争夺主键 1 上的 index record lock，于是 T2 和 T3 就死锁了，因为它俩都持有 shard next-key lock，双方谁都不会放弃已经得到的 shared next-key lock，于是，谁都无法得到主键 1 的 index record lock。

需要明确的是死锁的可能性并不受隔离级别的影响，因为隔离级别改变的是读操作的行为，而死锁是由于写操作产生的。死锁并不可怕，MySQL 会选择一个牺牲者，然后，在系统变量 innodb_lock_wait_timeout 指定的秒数达到后，自动回滚牺牲者事务; 从 MySQL5.7 开始，新加入了系统变量 innodb_deadlock_detect(默认 ON)，如果开启此变量，则 MySQL 不会再等待，一旦探测到死锁，就立即回滚牺牲者事务。

上图演示了：在上图的状态下，当 T1 commit 时，T1 释放了主键 1 上的 index record lock，于是 T2 和 T3 等待获取的 shared next-key lock 都成功了，然后，T2 和 T3 争夺主键 1 上的 index record lock，于是 T2 和 T3 死锁了，因为它俩都持有 shard next-key lock，双方谁都不会放弃已经得到的 shared next-key lock，于是，谁都无法得到主键 1 的 index record lock。

五、performance_schema.data_locks 中能看到全部的锁吗?

显而易见，performance_schema.data_locks 并未显示全部的锁，那么，它显示了哪些锁呢? 很不幸，我并未找到文档说这事，尽管文档 (https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html) 说：“事务持有的每一个锁以及事务被阻塞的每一个锁请求，都在该表中占据一行”，但，我们很多例子都表明，它并未显示全部的锁。根据我的试验，我猜测 performance_schema.data_locks 显示的是 WHERE 条件所触碰到的索引上的锁，“WHERE 条件所触碰到的索引”是指 SQL 实际执行时所使用的索引，也就是 SQL 执行计划的 key 列所显示的索引，正因为此，INSERT 时看不到任何锁，update g set a=a+1 where b=22 时只看到 idx_b 上的锁。需要强调的是，这是我自己试验并猜测的，我并未在文档中看到这种说法。

假设 T1 和 T2 两个事务操作同一个表，先执行 T1，此时尽管 performance_schema.data_locks 中只显示 T1 的 WHERE 条件所触碰到的索引上的锁，但是，事实上在 T1 的 WHERE 条件触碰不到的索引上，也是会设置锁的。尽管表的索引 idx 并未被 T1 所触碰到，即 performance_schema.data_locks 显示 T1 在索引 idx 并没有设置任何锁，但，当 T2 执行锁定读 / 插入 / 更新 / 删除时触碰到了索引 idx，T2 才恍然发现，原来 T1 已经在索引 idx 上加锁了。

我们来看下面的三个例子

“performance_schema.data_locks 无法看到全部锁”示例一

上图演示了：T1 执行时，只触碰到了索引 idx_b，T1 执行完后，在 performance_schema.data_locks 中只能看到 idx_b 上的锁，看起来 T1 并未在 idx_a 上设置任何锁; 但，当 T2 执行触碰到了索引 idx_a 时，T2 才恍然发现，原来 T1 已经在 idx_a 上设置了 index record lock 啦。

“performance_schema.data_locks 无法看到全部锁”示例二

插入新行时，会先设置 insert intention lock，插入成功后再在插入完成的行上设置 index record lock。

上图演示了：T1 插入了新行，但，在 performance_schema.data_locks 中，我们既看不到 T1 设置的 insert intention lock，也看不到 T1 设置的 index record lock。这是因为 T1 的 WHERE 条件并未触碰到任何索引(T1 根本不存在 WHERE 条件)，因此我们看不到 T1 的这两个锁; 但，当 T2 要删除 T1 新插入的行时，T2 才恍然发现，原来 T1 已经在索引 c2 上设置了 index record lock 啦。

“performance_schema.data_locks 无法看到全部锁”示例三