本文总结一下MySQL的具体事务隔离级别下，具体的语句加锁分析，主要是参考专题式讲解—InnoDB到底是怎么加锁的，小孩子的《MySQL是怎样运行的：从根上理解MySQL》。

1、前期准备

1.1. 示例中的数据库表

新建数据库表：

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    INDEX idx_name (name)
) Engine=InnoDB CHARSET=utf8;

向表中插入数据：

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

1.2 示例中的索引结构

1.1中的hero表有两个索引，一个聚簇索引和一个二级索引，示意图如下：

• 二级索引：仅包含二级索引的name列和主键列id列，同一个目录项记录页中，按照二级索引列name升序组成单链表；
• 聚簇索引：包含完整的列的数据，同一个目录项记录页中，按照主键列number升序组成单链表。

  1.3 分析语句加锁的sql语句分类

  一条sql语句需要加哪些锁会受很多条件的制约，一般有以下几个维度会影响sql语句的加锁：

1. 事务的隔离级别；
2. 语句执行时使用的索引（比如聚簇索引、唯一二级索引、普通二级索引）；
3. 查询条件（等值查询、范围查询）；
4. 具体执行的语句类型，比如select、insert、delete、update。

由于本文是MySQL的加锁语句分析，因此主要分析以下几个维度的sql语句加锁情况：

1. 事务隔离级别：
   1. READ UNCOMMITTED/READ COMMITTED；
   2. REPEATABLE READ；
2. 索引使用：
   1. 主键索引；
   2. 二级索引/唯一二级索引；
3. 查询条件：
   1. 等值查询；
   2. 范围查询；

4. 具体执行语句：

   1. SELECT … LOCK IN SHARE MODE（共享锁）
   2. SELECT … FOR UPDATE（独占锁）
   3. UPDATE …
   4. DELETE …

      2、RU/RC事务隔离级别

      2.1 主键等值查询

      2.1.1 SELECT … LOCK IN SHARE MODE

      2.1.2 SELECT … FOR UPDATE

      2.1.3 UPDATE …

      2.1.4 DELETE …

      2.2 主键范围查询

      2.2.1 SELECT … LOCK IN SHARE MODE

      2.2.2 SELECT … FOR UPDATE

      2.2.3 UPDATE …

      2.2.4 DELETE …

      2.3 二级索引等值查询

      2.3.1 SELECT … LOCK IN SHARE MODE

      2.3.2 SELECT … FOR UPDATE

      2.3.3 UPDATE …

      2.3.4 DELETE …

      2.4 二级索引范围查询

      2.4.1 SELECT … LOCK IN SHARE MODE

      2.4.2 SELECT … FOR UPDATE

      2.4.3 UPDATE …

      2.4.4 DELETE …

      3、RR事务隔离级别

      3.1 主键等值查询

      3.1.1 SELECT … LOCK IN SHARE MODE

      3.1.1.1 查找的记录的主键存在

      举例：

      SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

      因为主键有唯一性，在一个事务中下次再执行上面的查询语句时肯定不会有别的事务插入多余的number值为8的记录，此时会为number值为8的记录的聚簇索引上加一个S型的Record locks（S型正经记录锁），如下图所示：
      

      3.1.1.2 查找的记录的主键不存在

      举例：

      -- 表中没有number = 7的记录
      SELECT * FROM hero WHERE number = 7 LOCK IN SHARE MODE;

      由于number值为7的记录不存在，为了避免幻读的发生，当前读取事务提交前我们需要预防别的事务插入number值为7的新纪录，因此会在聚簇索引中number值为8的记录（即被查询记录（不存在）的下一条记录）上加一个gap锁，不允许其他事务插入number值为（3, 8）这个区间的新纪录。如下图所示：
      

      3.2 主键范围查询

      3.2.1 SELECT … LOCK IN SHARE MODE

      3.2.1.1 >=查询（包含了Supremum伪记录）

      举例：

      SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;

      因为要避免幻读的发生，因此需要保证不让新纪录插入到number值为8的后边就可以，因此有如下加锁：

5. 聚簇索引中为number值为8的记录加上一个S型的Record locks（S型正经记录锁）；

6. 聚簇索引中为number值大于8的记录加上一个S型的next-key locks（包括Supremum伪记录）。

如下图所示：

3.2.1.2 <= 查询（不包含Supremum伪记录）

举例：

SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

上述sql语句加锁时会在聚簇索引中未number值为1、3、8、15（8往后一位）这4条记录都加上S型的next-key locks，server层判断number值为15的记录不满足number <= 8条件后，与RC/RU隔离级别下处理方式不同，RR隔离级别下并不会把numer=15的聚簇索引记录的S型的next-key locks释放掉，加锁情况如下图所示：

这样如果别的事务想要插入新纪录的number值在(-∞, 1)、(1, 3)、(3, 8)、(8, 15)之间的话，是会进入等待状态的。

3.2.2 SELECT … FOR UPDATE

与3.2.1的SELECT … LOCK IN SHARE MODE基本相同，区别是将3.2.1中的S型的next-key locks换成X型的next-key locks。

3.2.3 UPDATE …

3.2.3.1 update语句未更新二级索引列

举例：

-- 二级索引是name列
UPDATE hero SET country = '汉' WHERE number >= 8;

这条update语句并没有更新二级索引列，加锁方式和3.2.2select … for update中的一致，即仅针对聚簇索引对应的记录加X型的next-key locks。

3.2.3.2 update语句更新了二级索引列

举例：

-- 二级索引是name列
UPDATE hero SET name = 'cao曹操' WHERE number >= 8;

上述sql语句加锁情况如下：

1. 对聚簇索引加锁，与select … for update一致，即对number值为8的记录加X型的Record locks（X型正经记录锁），对number值为15、20以及Supremum记录加X型next-key锁；
2. 对二级索引加锁，对number值为8、15、20的二级索引记录加上X型的Record locks（X型正经记录锁）。

如下图所示：

3.2.4 DELETE …

举例：

DELETE FROM hero WHERE number >= 8;
DELETE FROM hero WHERE number <= 8;

这两条delete语句跟更新带有二级索引列的update语句的加锁情况一致。

3.3 唯一二级索引等值查询

3.3.1 SELECT … LOCK IN SHARE MODE

1.1中的表加一个唯一二级索引，或者在建表的时候就指定唯一二级索引，如下：

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    UNIQUE INDEX idx_name (name)
) Engine=InnoDB CHARSET=utf8;

3.3.1.1 查找的记录的二级索引存在

举例：

SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

由于唯一二级索引具有唯一性，在一个事务中下次再执行这个查询语句的时候肯定不会有别的事务插入多条name值为c曹操的记录，加锁情况如下：

1. 在二级索引中，为name值为c曹操的二级索引记录加一个S型Record lock锁（S型正经记录锁）；
2. 为name值为c曹操的记录对应的聚簇索引记录中加一个S型Record lock锁（S型正经记录锁）。

如下图所示：

3.3.1.2 查找的记录的二级索引不存在

举例：

-- name为g关羽的记录不存在
SELECT * FROM hero WHERE name = 'g关羽' LOCK IN SHARE MODE;

在唯一二级索引uk_name中，键值比’g关羽’大的第一条记录的键值为l刘备，所以需要在这条二级索引记录上加一个gap锁，如图所示：

注意：这里如果查询的唯一二级索引记录不存在，只会对二级索引记录加gap锁，并不会对聚簇索引记录加锁。

3.3.2 SELECT … FOR UPDATE

举例：

SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;

这种情况与3.3.1中加锁语句像是，会给二级索引记录和对应的聚簇索引记录均加上锁，只不过加的是X型Record lock锁（X型正经记录锁）。

3.3.3 UPDATE …

与3.3.2中独占锁加锁情况类似，区别在于如果更新的列中有二级索引列的话，对应的二级索引列也会被加锁。

3.3.4 DELETE …

与3.3.2加锁情况类似，不过如果表中还有别的二级索引列的话，这些对应的二级索引列也会被加锁。

3.4 唯一二级索引范围查询

3.4.1 SELECT … LOCK IN SHARE MODE

3.4.1.1 >= 唯一二级索引

举例：

SELECT * FROM hero FORCE INDEX(idx_name) WHERE name >= 'c曹操' LOCK IN SHARE MODE;

先在二级索引中定位满足name > c曹操的第一条记录，也就是name值为c曹操的记录，然后就可以沿着这条记录的链表一路向后查找，从二级索引idx_name的示意图中可以看出，所有的用户记录都满足name > c曹操这个条件，因此所有的二级索引记录（包括Supremum记录）都会被加上S型的next-key锁，他们对应的聚簇索引记录也会被加上S型Record lock（S型正经记录锁）。需要注意：先给记录1的二级索引记录加S型的next-key锁，再给记录1的对应的聚簇索引记录加上S型Record lock（S型正经记录锁）；然后给记录2的二级索引记录加S型的next-key锁，再给记录2的对应的聚簇索引记录加上S型Record lock（S型正经记录锁）。
如图所示：

3.4.1.2 <= 唯一二级索引

举例：

SELECT * FROM hero WHERE name <= 'c曹操' LOCK IN SHARE MODE;

这个语句先会为name值为’c曹操’的二级索引记录加S型next-key锁以及它对应的聚簇索引记录加S型正经记录锁。但并不会像聚簇索引为查询条件那样为下一条记录加上锁了，原因是上面的sql语句开启了索引下推，即先在二级索引中判断name <= c曹操，而不是直接返回给server层判断。对于二级索引记录来说，可以先在存储引擎层判断给定条件name <= ‘c曹操’是否成立，如果不成立就不返回给server层了，从而避免了不必要的加锁。

3.4.2 SELECT … FOR UPDATE

和3.4.1加锁语句类似，只不过加的是X型正经记录锁。

3.4.3 UPDATE …

与3.4.2加锁语句类似：

1. 先在二级索引记录中找到符合条件的第一条记录，然后从这条记录开始向前或者向后遍历链表；
2. 给符合条件的二级索引记录加上X型的next-key锁；
3. 给2中二级索引记录对应的聚簇索引记录加上X型Record lock（X型正经记录锁）。

   3.4.4 DELETE …

   举例： ```sql DELETE FROM hero WHERE number >= 8;

DELETE FROM hero WHERE number <= 8;

这两个语句的加锁情况和更新带有二级索引列的UPDATE语句一致：
1. 先在二级索引记录中找到符合条件的第一条记录，然后从这条记录开始向前或者向后遍历链表；
1. 给符合条件的**二级索引**记录加上**X型的next-key锁；**
1. 给2中二级索引记录对应的**聚簇索引**记录加上**X型Record lock**（X型正经记录锁）。
<a name="ENZf8"></a>
# 4、INSERT加锁语句分析
在[MySQL锁基础](https://www.yuque.com/zhangjian-mbxkb/spkqgr/glwmcr#MwRq3)这篇文章的5.4和5.5节介绍了insert语句的加锁情况，概括地说：
1. 如果插入记录的位置被别的事务加了gap锁，此时当前事务会生成一个**插入意向锁**，然后进入等待状态，待加了gap锁的事务提交完事务释放掉了gap锁，当前事务才会获取插入意向锁，然后执行插入操作；
1. 一个事务对新插入的记录**一般不会显示加锁**（插入意向锁只是一个锁结构），在当前事务中新插入一条聚簇索引记录后，该记录的trx_id隐藏列代表的的就是当前事务的事务id，如果其他事务此时想对该记录添加S锁或者X锁时，首先会看一下该记录的trx_id隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个X锁（也就是为当前事务创建一个锁结构，is_waiting属性是false），然后自己进入等待状态（也就是为自己也创建一个锁结构，is_waiting属性是true）。
下面在具体分析一下。
<a name="UTflr"></a>
## 4.1 待插入记录的下一条记录上已经被其他事务加了gap锁时
这一节介绍的场景和第四节开头的第一种情况是一种情况。<br />每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了gap锁，如果已加gap锁，那INSERT语句应该被阻塞，并生成一个插入意向锁。比方说对于hero表来说，事务T1运行在REPEATABLE READ（后续简称为RR，后续也会把READ COMMITTED简称为RC）隔离级别中，执行了下边的语句：
```sql
# 事务T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM hero WHERE number < 8 FOR UPDATE;
+--------+------------+---------+
| number | name       | country |
+--------+------------+---------+
|      1 | l刘备      | 蜀      |
|      3 | z诸葛亮    | 蜀      |
+--------+------------+---------+
2 rows in set (0.02 sec)

这条语句会对主键值为1、3、8的这3条记录都添加X型next-key锁，此时事务T2想插入一条主键值为4的聚簇索引记录，那么T2在插入记录前，首先要定位一下主键值为4的聚簇索引记录在页面中的位置，发现主键值为4的下一条记录的主键值是8，而主键值是8的聚簇索引记录已经被添加了gap锁（next-key锁包含了正经记录锁和gap锁），那么事务T2就需要进入阻塞状态，并生成一个类型为插入意向锁的锁结构。

4.2 遇到重复键时

如果在插入新记录时，发现页面中已有的记录的主键或者唯一二级索引列与待插入记录的主键或者唯一二级索引列值相同，此时插入新记录的事务会获取页面中已存在的键值相同的记录的锁。
分两种情况：

• 插入记录的主键与表中已有记录的主键重复；

• 插入记录的二级索引与表中已有记录的二级索引重复。

  4.2.1 主键重复

  如果是主键值重复，那么：

• 当隔离级别不大于RC时，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加S型正经记录锁；

• 当隔离级别不小于RR时，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加S型next-key锁。

  4.2.2 唯一二级索引列重复

  如果是唯一二级索引列重复，那不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加S型next-key锁。