聊聊select for update到底加了什么锁

前言

大家，我是田螺。

最近在开发需求的时候，用到了​​select...for update​​。在代码评审的时候，一位同事说 ，​​唯一索引+一个非索引字段​​，是否可能会锁全表呢？本文田螺哥将通过​​9​​​个实验操作的例子，给大家验证​​select...for update​​到底加了什么锁，是表锁还是行锁。

这是本文的提纲哈：

因为加锁是跟数据库的隔离级别息息相关的。而常用的数据库隔离级别也就​​RC(读已提交)和RR(可重复读)​​​，所以本文分别根据​​RC(读已提交) 和 RR(可重复读)隔离级别​​展开讲述。

1. 环境准备

设置数据库隔隔离级别

mysql> set global TRANSACTION ISOLATION level read COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

自动提交关闭

mysql> set @@autocommit=0;  //设置自动提交关闭
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

建表语句

CREATE TABLE `user_info_tab` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `status` varchar(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570072 DEFAULT CHARSET=utf8mb3;

初始化数据(接下来的实验证明，都是基于这几条初始数据)

insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('杰伦',18,'深圳','1');
insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('奕迅',26,'湛江','0');
insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('俊杰',28,'广州','1');

MYSQL 版本

mysql> select @@version;  
+-----------+
| @@version |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

2.RC 隔离级别

2.1 RC隔离级别 + 唯一索引

先把隔离级别设置为​​RC​​​，因为​​user_name​​​为唯一索引，我们使用​​user_name​​​为条件去执行​​select......for update​​语句，然后开启另外一个事务去更新数据同一条数据，发现被阻塞了。如下图：

事务二的更新语句为什么会阻塞呢？

因为事务一的​​select......for update​​已经加了锁。那加的是行锁还是表锁呢？如果加的是表锁的话，我们更新其他行的记录的话，应该是也会阻塞的，如果是行锁的话，更新其他记录是可以顺利执行的。

大家可以再看下这个图：

通过实验，可以发现:如果事务中是更新其他行记录的话，是可以顺利执行的。因此在RC隔离级别下，如果条件是唯一索引，那么​​select...for update​​加的应该是行锁。

有些小伙伴会很好奇，到底加了什么锁呢？ 接下来带大家看看，具体加的是什么锁。

我用的​​MySQL​​​版本是​​8.0+​​,用这个语句查看：

SELECT * FROM performance_schema.data_locks\G;

如下图，​​select * from user_info_tab where user_name ='杰伦' for update​​​语句一共加了三把锁，分别是 ​​IX​​意向排他锁（表级别的锁，不影响插入）、两把​​X​​排他锁（行锁，分别对应唯一索引，主键索引）​

有些读者朋友说，这里不是加了​​IX​​表锁嘛？为什么不阻塞其他行的更新？ 其实这个是意向排他锁。

意向排他锁：简称​​IX​​​锁，当事务准备在某条记录上加上​​X​​​锁时，需要在表级别加一个IX锁。如​​select ... for update​​​，要给表设置​​IX锁​​;

那既然有表锁，为啥事务二在执行其他行的更新语句时，并不会阻塞，这是因为：意向锁仅仅表明意向的锁，意向锁之间不会互斥，是可以并行的。，锁的兼容性如下：

有些小伙伴可能还有疑问，为啥会有两把的X锁呢？ 不是都锁住的是同一行嘛？其实RC隔离级别下，唯一索引的加锁是这样的：

为什么不是唯一索引上加X锁就可以了呢？为什么主键索引上的记录也要加锁呢？

如果并发的一个SQL，通过主键索引来更新：​​update user_info_tab set user_name = '学友' where id = '1570068'​​​;此时，如果​​select...for update​​​语句没有将主键索引上的记录加锁，那么并发的​​update​​​就会感知不到​​select...for update​​语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

大家如果对于锁这块基础知识，还是有疑问的话，可以看下我之前的这篇文章哈：​​两万字详解！InnoDB锁专题！​​

2.2 RC 隔离级别 + 主键

在​​RC 隔离级别​​​下，如果​​select...for update​​​的查询条件是​​主键id​​，加的又是什么锁呢？

我们执行语句：​​select * from user_info_tab where id ='1570070' for update;​​然后开启另外一个事务去更新数据同一条数据，发现被阻塞了。如下图：

事务二更新的是其他行的记录，则是可以顺利执行的，如下图：

通过实验，可以发现:

如果事务中是更新其他行记录，是可以顺利执行的话。在RC隔离级别下，如果条件是主键，那么​​select...for update​​锁的也是行。

根据2.1小节的结论，​​select...for update​​都会加个表级别的​​IX​​​意向排他锁。所以，查询条件是​​id​​​的话，​​select...for update​​​会加两把锁，分表是​​IX意向排他锁（表锁，不影响插入）​​​、​​一把X排他锁（行锁，对于主键索引）​​

我们执行语句，查询一下到底加的是什么锁。

begin;
select * from user_info_tab where id ='1570070' for update;
SELECT * FROM performance_schema.data_locks\G;

因此在RC隔离级别下，如果条件是主键，那么​​select......for update​​加的就是两把锁，一把​​IX​​​意向排他锁（不影响插入），一把对应主键的​​X​​排他锁（行锁，会锁住主键索引那一行）。

2.3 RC 隔离级别 + 普通索引

在​​RC 隔离级别​​​下，如果​​select......for update​​​的查询条件是​​普通索引​​，加的又是什么锁呢？

我们这里先给原来表加上普通索引：

alter table user_info_tab add index idx_city (city);

我们执行语句：​​select * from user_info_tab where city ='广州' for update;​​然后开启另外一个事务去更新同一条数据，发现被阻塞了。如下图：

如果事务二更新的是其他行的记录，还是可以顺利执行的，如下图：

我们看一下​​select * from user_info_tab where city ='广州' for update;​​到底加了什么锁，如下图：

发现一共加了三把锁，分别是：​​IX意向排他锁（表锁）​​​、​​两把X排他锁（行锁，分别对应普通索引的X锁，对应主键的X锁）​​。

如果查询条件，没有命中数据库表的记录，又加什么锁呢？

我们把查询条件改一下：​​select * from user_info_tab where city ='广州' and status='0' for update;​​

发现只加了一把锁，即​​IX意向排他锁（表锁，不影响插入）​​。

2.4 RC 隔离级别 + 无索引

在​​RC 隔离级别​​​下，如果​​select...for update​​的查询条件是无索引呢，加的又是什么锁呢？

多数读者凭感觉都是锁表了，我们来验证一下。

我们执行语句：​​select * from user_info_tab where age ='26' for update;​​（age是没有加索引的），然后开启另外一个事务去更新数据。如下图：

由上图可以发现，事务一 先执行​​select......for update​​​，然后事务二先更新别的行，发现可以顺利执行，如果执行​​for update​​的同一行，还是会阻塞等待。

可推出结论，​​select...for update​​的查询条件是无索引，主要还是行锁。我们看下具体的加锁情况：

SELECT * FROM performance_schema.data_locks\G;

发现一共加了两把锁，分别是：​​IX意向排他锁（表锁）​​​、​​一把X排他锁（行锁，对应主键的X锁）​​。

为什么不是一个锁表的X锁呢？ 这是因为:

若​​age​​列上没有索引，MySQL会走聚簇(主键)索引进行全表扫描过滤。每条记录都会加上X锁。但是，为了效率考虑，MySQL在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则。

3.RR 隔离级别

3.1  RR隔离级别 + 唯一索引

如果是RR（可重复）的数据库隔离级别呢，​​select...for update​​的查询条件是唯一索引的话，加的又是什么锁呢？

我们知道​​RR隔离级别​​​比​​RC隔离级别​​，主要差异还是有间隙锁这个概念。接下来我们还是通过实验去验证，先把数据库隔离级别设置为​​RR​​：

mysql> set global transaction isolation level repeatable read; (设置完好像要重启一下)

Query OK, 0 rows affected (0.00 sec)

我们执行语句：​​select * from user_info_tab where user_name ='杰伦' for update;​​（user_name是唯一索引的），然后开启另外一个事务去更新数据。如下图：

由上图可以发现，即使是RR数据库隔离级别，事务一先执行​​select...for update​​​，然后事务一先更新别的行，发现可以顺利执行，如果执行更新​​for update​​的那一行，还是会阻塞超时。

再去看下具体加了什么锁：

大家可以发现，不管是RC隔离级别还是RR隔离级别，​​select...for update​​，查询条件是唯一索引，命中数据库表记录时，一共会加三把锁：一把​​IX​​意向排他锁 （表锁，不影响插入），一把对应主键的​​X​​排他锁（行锁），一把对应唯一索引的X排他锁 （行锁）。

3.2 RR 隔离级别 + 主键

如果是 RR（可重复读）的数据库隔离级别，​​select...for update​​的查询条件是主键的话，加的又是什么锁呢？

根据前面的实验结果，我们其实可以推测得出来了，应该跟RC隔离级别一样，会加两把锁：一把​​IX​​意向排他锁（表锁，不影响插入），一把对应主键的​​X​​排他锁（行锁，影响对应主键那一行的插入）。

我们通过语句确认一下，先输入一下语句：

begin;
select * from user_info_tab where id ='1570070' for update;
SELECT * FROM performance_schema.data_locks\G;

大家可以看下，跟我们的推测是一样的：

3.3 RR 隔离级别 + 普通索引

在RR隔离级别下，如果​​select...for update​​的查询条件是普通索引的话，除了会加X锁，IX锁，还会加Gap 锁。

Gap锁的提出，是为了解决幻读问题引入的，它是一种加在两个索引之间的锁。

我们来验证一下，先开始事务一，执行一下操作：

begin;
select * from user_info_tab where city ='广州' for update;

接着开启事务二

begin;
insert into user_info_tab(`user_name`,`age`,`city`,`status`) values('方燕',27,'汕头','1');

验证流程图如下：

大家可以发现，插入新记录，会被阻塞，那是因为有间隙锁的缘故，我们再看下到底加了哪些锁：

发现相对于RC隔离级别，确实多了间隙锁，锁住范围了。我画一下这种场景的加锁示意图：

如果要插入汕头城市的记录，会被Gap锁锁住了，因此会阻塞。

因此，在RR隔离级别下，如果​​select...for update​​的查询条件是普通索引的话，命中查询记录的话，除了会加X锁（行锁），IX锁（表锁，不影响插入），还会加Gap 锁（间隙锁，会影响插入）。

大家如果对间隙锁这块不是很熟悉的话，可以看下我以前的这篇文章哈，​​两万字详解！InnoDB锁专题！​​

3.4  RR隔离级别 + 无索引

在RR隔离级别下，如果​​select...for update​​的查询条件是无索引的话，会锁全表嘛？来一起验证一下

我们直接按顺序执行以下这些语句：

begin;
select * from user_info_tab where age ='26' for update;
select OBJECT_NAME,INDEX_NAME, LOCK_TYPE,LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

可以发现加了这么多锁：

一共五把锁，这个​​IX​​​锁（表级别，意向排他锁），我们可以理解，跟前面几个例子的一样。后面三把行锁，就是把每一行的数据记录，都加了X排他锁（行锁，锁的对象对应于​​主键Id​​），我们也可以理解。但是这个第二行，是一把怎么样的X锁呢？

我谷歌了一下,什么是​​supremum pseudo-record​​，找到了这个解释:

For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.

翻译过来，大概意思就是：相当于比索引中所有值都大，但却不存在索引中，相当于最后一行之后的间隙锁。我理解就是如果查询条件有索引的话，类似于一个（索引最大值，+无穷）的虚拟间隙锁。

但是因为我们的查询字段​​age​​并没有索引，锁为​​X​​​锁，​​lock_data​​​值为​​supremum pseudo-record​​，它表示：全表行锁，要走聚簇索引进行全部扫描。

也就是说RR隔离级别下，对于​​select...for update​​，查询条件无索引的话，会加一个​​IX锁​​​（表锁，不影响插入），每一行实际记录行的​​X锁​​​，还有对应于​​supremum pseudo-record​​的虚拟全表行锁。这种场景，通俗点讲，其实就是锁表了。

我们来做个实验，验证虚拟全表行锁的存在，先开启事务一，执行：

begin;
select * from user_info_tab where age ='26' for update;

然后开启事务二，执行一个插入语句:

begin;
insert into user_info_tab(id,`user_name`,`age`,`city`,`status`) values(1,'小明',31,'北京','1');

大家可以看下，阻塞了，:

加餐

大家有没有发现，田螺哥列举RR数据库隔离级别的例子，​​select...for update​​​条件都是命中数据库表记录的。在这里，田螺哥给大家出道题。在​​RR​​​隔离级别下，如果​​select...for update​​的查询条件，没命中当前数据表记录的话，又加什么锁呢？

我们来搞点刺激的，​​select...for update​​​ 搞两个条件：一个唯一索引(​​user_name​​​) + 一个无索引(​​status​​)，然后没命中当前数据表记录，你觉得会加什么锁呢?

CREATE TABLE `user_info_tab` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `status` varchar(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE,
  KEY `idx_city` (`city`)
) ENGINE=InnoDB AUTO_INCREMENT=1570074 DEFAULT CHARSET=utf8mb3

我们按顺序执行者几条语句：

begin;
select * from  user_info_tab  where user_name ='杰伦'  and status ='0'  for update ;
select OBJECT_NAME,INDEX_NAME, LOCK_TYPE,LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

最后

我是捡田螺的小男孩。通过本文，大家学到哪些知识点呢？

1. ​​select...for update​​在不同场景，都加了什么锁。
2. 如何查看一个SQL 加了什么锁 （执行完原生SQL，再执行​​SELECT * FROM performance_schema.data_lock​​
3. 如何手写个死锁 (分别开两个事务，制造锁冲突，文章的例子，好多都是死锁的case)

本文是我通过实验对比得出的结论，如果大家觉得哪里不对的话，可以在留言区讨论哈。

文章转载自公众号：捡田螺的小男孩