Mysql系列：select for update是怎么加锁的

type

status

date

slug

summary

1、概述

先看下面的 sql，大家能否说出这六句 sql 在不同的事务隔离级别下，是否加锁，加的是共享锁还是排他锁，是否存在间隙锁？

网上有很多版本的答案，最主要的原因就是脱离 MySQL 的版本、事务隔离级别、以及where后跟的是否为索引条件列。在回答这个问题之前，先确认三个问题：

当前事务隔离级别是什么

id 列是否存在索引

如果存在索引是聚簇索引还是非聚簇索引呢？

2、基础知识

2.1 聚簇索引

Mysql 的索引树按叶子结点保存的数据类型分为：

聚簇索引：叶子节点保存真实数据。

非聚簇索引：叶子节点还是索引，指向聚簇索引树。

另外：

innodb一定存在聚簇索引，默认以主键作为聚簇索引

有几个索引，就有几棵B+树(不考虑hash索引的情形)

2.2 事务隔离级别

MySQL innodb支持的四种事务隔离级别，他们由弱到强如下所示:

Read Uncommited(RU)：读未提交，简称RU，一个事务可以读到另一个事务未提交的数据！

Read Committed (RC)：读已提交，简称为RC，一个事务可以读到另一个事务已提交的数据!

Repeatable Read (RR):可重复读，MySQL默认的事务隔离级别，简称为RR。加入间隙锁，一定程度上避免了幻读的产生！注意了，只是一定程度上，并没有完全避免。另外就是记住从该级别才开始加入间隙锁(这句话记下来，后面有用到)!

Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。

2.3 锁分类

锁的种类可以参考前面的文章 ‣，这里只提到和本文相关的一些锁分类。

从锁的粒度可以分为：

行锁。通过update、delete 、 select for update、 select in share mode等语句加上的锁都是行级别的锁。select for update 仅适用于 InnoDB，并且必须开启事务，在 begin 与 commit 之间才生效。

表锁：LOCK TABLE … READ和LOCK TABLE … WRITE才能申请表级别的锁。

从锁是否可以共享可以分为:

共享锁(S锁)：假设事务T1对数据A加上共享锁，那么事务T2可以读数据A，不能修改数据A。

排他锁(X锁)：假设事务T1对数据A加上共享锁，那么事务T2不能读数据A，不能修改数据A。

意向共享锁(IS锁)：一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁。

意向排他锁(IX锁)：一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁。

意向锁的作用？
意向排他锁存在的意义是为了更高效的获取表锁，当其他事务要对全表的数据进行加锁时，那么就不需要判断每一条数据是否被加锁了。

2.4 行级锁的种类

根据官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html ，可以分为下面三类

Record Lock：简单翻译为行锁吧。注意了，该锁是对索引记录进行加锁！锁是在加索引上而不是行上的。注意了，innodb一定存在聚簇索引，因此行锁最终都会落到聚簇索引上！

Gap Lock：简单翻译为间隙锁，是对索引的间隙加锁，是一个左开右开的区间。其目的只有一个，防止其他事物插入数据。在Read Committed隔离级别下，不会使用间隙锁。这里我对官网补充一下，隔离级别比Read Committed低的情况下，也不会使用间隙锁，如隔离级别为Read Uncommited时，也不存在间隙锁。当隔离级别为Repeatable Read和Serializable时，就会存在间隙锁。

Next-Key Lock：这个理解为Record Lock+索引前面的Gap Lock，是一个左开右闭的区间。记住了，锁住的是索引前面的间隙！

比如一个索引包含值 10，11，13和20。那么，行锁的范围如下：

Gap Lock 间隙锁的范围如下：

Next-Key的范围如下

2.5 快照读和当前读

在 mysql 中 select 分为快照读和当前读，执行下面的语句

执行的是快照读，读的是数据库记录的快照版本，是不加锁的。（这种说法在隔离级别为Serializable中不成立，后面我会补充。）

那么，执行

会对读取记录加S锁 (共享锁)。

会对读取记录加X锁 (排他锁)。

那么加的是表锁还是行锁呢？

那么关于是表锁还是行锁，大家可以看到网上最流传的一个说法是这样的，

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

这句话大家可以搜一下，都是你抄我的，我抄你的。那么，这句话本身有两处错误！

错误一：并不是用表锁来实现锁表的操作，而是利用了Next-Key Locks，也可以理解为是用了行锁+间隙锁来实现锁表的操作！

为了便于说明，我来个例子，假设有表数据如下，pId为主键索引

pId(int)	name(varchar)	num(int)
1	aaa	100
2	bbb	200
7	ccc	200

执行语句(name列无索引)

那么此时在pId=1,2,7这三条记录上存在行锁(把行锁住了)。另外，在(-∞,1)(1,2)(2,7)(7,+∞)上存在间隙锁(把间隙锁住了)。因此，给人一种整个表锁住的错觉！对该结论有疑问的，可自行执行show engine innodb status;语句进行分析。

错误二：所有文章都不提隔离级别！

注意我上面说的，之所以能够锁表，是通过行锁+间隙锁来实现的。那么，RU和RC都不存在间隙锁，这种说法在RU和RC中还能成立么？

因此，该说法只在RR和Serializable中是成立的。如果隔离级别为RU和RC，无论条件列上是否有索引，都不会锁表，只锁行！

3、分析

下面来对开始的问题作出解答，假设有表如下，pId 为主键索引。

初始化数据如下：

pId(int)	name(varchar)	num(int)
1	aaa	100
2	bbb	200
3	bbb	300
7	ccc	200

查看/修改数据库事务隔离级别

查看当前出现的锁

查看由于锁占用导致等待的表

有了这些准备只是，可以开始验证了。

3.1 RC/RU + 非索引

这种情况的加锁特征是：不管怎么查都是只对聚簇索引加行锁。

3.1.1 select 精确查询

不加任何锁，是快照读。

3.1.2 select 范围查询

不加任何锁，是快照读。

3.1.3 select in share mode 精确查询

当num = 200，有两条记录。这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级S锁，采用当前读。

3.1.4 select in share mode 范围查询

当num > 200，有一条记录。这条记录对应的pId=3，因此在pId=3的聚簇索引上加上行级S锁，采用当前读。

3.1.5 select for update 精确查询

当num = 200，有两条记录。这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级X锁，采用当前读。

3.1.6 select for update 范围查询

当num > 200，有一条记录。这条记录对应的pId=3，因此在pId=3的聚簇索引上加上行级X锁，采用当前读。

3.2 RC/RU + 聚簇索引

pId 是主键列，因此 pId 用的就是聚簇索引。这种加锁特征：和【RC/RU + 非索引】情况是类似的。

3.2.1 select 精确查询

不加任何锁，是快照读。

3.2.2 select 范围查询

不加任何锁，是快照读。

3.2.3 select in share mode 精确查询

在pId=2的聚簇索引上，加S锁，为当前读。

3.2.4 select in share mode 范围查询

在pId=3，7的聚簇索引上，加S锁，为当前读。

3.2.5 select for update 精确查询

在pId=2的聚簇索引上，加X锁，为当前读。

3.2.6 select for update 范围查询

在pId=3，7的聚簇索引上，加X锁，为当前读。

为什么条件列加不加索引，加锁情况是一样的？

其实是不一样的。在RC/RU隔离级别中，MySQL Server做了优化。在条件列没有索引的情况下，尽管通过聚簇索引来扫描全表，进行全表加锁。但是，MySQL Server层会进行过滤并把不符合条件的锁当即释放掉，因此你看起来最终结果是一样的。但是RC/RU+条件列非索引比本例多了一个释放不符合条件的锁的过程！

注意 pid > 10 的数据不存在，会对聚簇索引的间隙(7,+∞)加 gap lock。

3.3 RC/RU + 非聚簇索引

我们在 num 列上建上非唯一索引。此时有一棵聚簇索引(主键索引，pId)形成的 B+ 索引树，其叶子节点为硬盘上的真实数据。以及另一棵非聚簇索引(非唯一索引，num)形成的 B+ 索引树，其叶子节点依然为索引节点，保存了 num 列的字段值，和对应的聚簇索引。

3.3.1 select 精确查询

不加任何锁，是快照读。

3.3.2 select 范围查询

不加任何锁，是快照读。

3.3.3 select in share mode 精确查询

当num = 200，由于num列上有索引，因此先在 num = 200的两条索引记录上加行级S锁。接着，去聚簇索引树上查询，这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级S锁，采用当前读。

3.3.4 select in share mode 范围查询

当num > 200，由于num列上有索引，因此先在符合条件的 num = 300的一条索引记录上加行级S锁。接着，去聚簇索引树上查询，这条记录对应的pId=3，因此在pId=3的聚簇索引上加行级S锁，采用当前读。

3.3.5 select for update 精确查询

当num = 200，由于num列上有索引，因此先在 num = 200的两条索引记录上加行级X锁。接着，去聚簇索引树上查询，这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级X锁，采用当前读。

3.3.6 select for update 范围查询

当num > 200，由于num列上有索引，因此先在符合条件的 num = 300的一条索引记录上加行级X锁。接着，去聚簇索引树上查询，这条记录对应的pId=3，因此在pId=3的聚簇索引上加行级X锁，采用当前读。

3.4 RR/Serializable + 非索引

RR 级别需要多考虑的就是 gap lock，他的加锁特征在于：无论你怎么查都是锁全表。

3.4.1 select 精确查询

在RR级别下，不加任何锁，是快照读。

在Serializable级别下，在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加 gap lock。

3.4.2 select 范围查询

在RR级别下，不加任何锁，是快照读。

在Serializable级别下，在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock。

3.4.3 select in share mode 精确查询

在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock。

3.4.4 select in share mode 范围查询

在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock。

3.4.5 select for update 精确查询

在pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock。

3.4.6 select for update 范围查询

在pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock。

3.4.7 select in share mode/for update 精确查询，数据不存在

注意 num = 250 的数据不存在，这种情况会对聚簇索引的所有行加上行级X锁，采用当前读。

3.4.8 select in share mode/for update 范围查询，数据不存在

注意 num > 400 的数据不存在，这种情况会对聚簇索引的所有行加上行级X锁，采用当前读。

3.5 RR/Serializable + 聚簇索引

大家知道 pId 是主键列，因此 pId 用的就是聚簇索引。该情况的加锁特征在于：

如果where后的条件为精确查询(=的情况)，那么只存在record lock。

如果where后的条件为范围查询(>或<的情况)，那么存在的是record lock+gap lock。

3.5.1 select 精确查询

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock。

3.5.2 select 范围查询

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，是当前读，在 pId=3,7 的聚簇索引上加S锁，在(2,3)(3,7)(7,+∞)加上gap lock。

3.5.3 select in share mode 精确查询

是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock

3.5.4 select in share mode 范围查询

是当前读，在pId=3,7的聚簇索引上加S锁，在(2,3)(3,7)(7,+∞)加上gap lock。

3.5.5 select for update 精确查询

是当前读，在pId=2的聚簇索引上加X锁。

3.5.6 select for update 范围查询

在pId=3,7的聚簇索引上加X锁，在(2,3)(3,7)(7,+∞)加上gap lock。

3.5.7 select in share mode/for update 精确查询，数据不存在

注意了，pId=6是不存在的列，这种情况会在(3,7)上加gap lock。

3.5.8 select in share mode/for update 范围查询，数据不存在

注意了，pId>18，查询结果是空的。在这种情况下，是在(7,+∞)上加gap lock。

3.6 RR/Serializable + 非聚簇索引

这里非聚簇索引，需要区分是否为唯一索引。因为如果是非唯一索引，间隙锁的加锁方式是有区别的。

如果是唯一索引，情况和【RR/Serializable+ 聚簇索引】类似，唯一区别的是：对聚簇索引枷锁，只会加在聚簇索引树上；对非聚簇索引加锁，会同时加在对应的非聚簇索引树和聚簇索引树上。

如果是非唯一索引，和唯一索引的区别是：唯一索引进行精确查询后，只存在 record lock，不存在 gap lock；而非唯一索引进行精确查询以后，不仅存在 record lock，还存在 gap lock。

老规矩在 num 列建立非唯一索引。

3.6.1 select 精确查询

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。

3.6.2 select 范围查询

在 RR 级别下，不加任何锁，是快照读。

在 Serializable 级别下，是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁，在(200,300)(300,+∞)加上gap lock。

3.6.3 select in share mode 精确查询

是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。

3.6.4 select in share mode 范围查询

是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁，在(200,300)(300,+∞)加上gap lock。

3.6.5 select for update 精确查询

是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加X锁，在(100,200)(200,300)加上gap lock。

3.6.6 select for update 范围查询

是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加X锁，在(200,300)(300,+∞)加上gap lock

3.6.7 select in share mode/for update 精确查询，数据不存在

注意了，num=250是不存在的列，这种情况会在(200,300)上加gap lock。

3.6.8 select in share mode/for update 范围查询，数据不存在

注意了，pId>400，查询结果是空的。在这种情况下，是在(400,+∞)上加gap lock。

4、参考

https://www.cnblogs.com/rjzheng/p/9950951.html