前言大家好，我是蜗牛。最近在开发需求的时候，使用了select...forupdate。codereview的时候有同事说，能不能用唯一索引+非索引字段锁住整张表？天罗兄在本文中将通过9个实验操作实例来验证select...forupdate加了什么样的锁，是表锁还是行锁。这是本文的大纲：因为加锁与数据库的隔离级别密切相关。常用的数据库隔离级别有RC（ReadCommitted）和RR（RepeatableRead），所以本文按照RC（ReadSubmitted）和RR（RepeatableRead）隔离级别进行介绍。1.环境准备设置数据库隔离级别mysql>setglobalTRANSACTIONISOLATIONlevelreadCOMMITTED;QueryOK,0rowsaffected(0.00sec)mysql>select@@transaction_isolation;+------------------------+|@@transaction_isolation|+------------------------+|已提交|+------------------------+1rowinset(0.00sec)autocommitoffmysql>set@@autocommit=0;//设置自动提交关闭查询OK，0行受影响（0.00秒）mysql>select@@autocommit;+------------+|@@autocommit|+--------------+|0|+------------+1rowinset(0.00sec)tablecreationstatementCREATETABLE`user_info_tab`(`id`intNOTNULLAUTO_INCREMENT,`user_name`varchar(255)DEFAULTNULL,`age`intDEFAULTNULL,`city`varchar(255)DEFAULTNULL,`status`varchar(4)NOTNULLDEFAULT'0',PRIMARYKEY(`id`),UNIQUEKEY`idx_user_name`(`user_name`)使用BTREE)ENGINE=InnoDBAUTO_INCREMENT=1570072DEFAULTCHARSET=utf8mb3;初始化数据（后面的实验会证明都是基于这些初始数据）insertintouser_info_tab(`user_name`,`age`,`city`,`status`)values('Jaylen',18,'Shenzhen','1');insertintouser_info_tab(`user_name`,`age`,`city`,`status`)values('Eason',26,'湛江','0');insertintouser_info_tab(`user_name`,`age`,`city`,`status`)values('俊杰',28,'广州','1');MYSQL版本mysql>select@@版本;+------------+|@@version|+------------+|8.0.31|+------------+1rowinset(0.00sec)2.RCisolationlevel2.1RCisolationlevel+uniqueindex首先设置隔离级别为RC，因为user_name是一个唯一索引，我们以user_name为条件执行select......forupdate语句，然后再开启一个事务去更新同一条数据，发现被阻塞了如下图：为什么事务2的update语句会被阻塞？因为事务一的select...forupdate已经被锁定了。是行锁还是表锁？如果是表锁的话，如果我们更新其他行的记录，应该也会被阻塞。如果是行锁，更新其他记录可以顺利执行。大家可以再看这张图：通过实验可以发现，如果在事务中更新其他行记录，是可以顺利执行的。因此，在RC隔离级别，如果条件是唯一索引，那么select...forupdate应该加行锁。有的朋友会好奇，加的是什么锁？接下来，我将向您展示添加什么样的锁。我使用的MySQL版本是8.0+，使用这条语句查看：SELECT*FROMperformance_schema.data_locks\G;如下图，select*fromuser_info_tabwhereuser_name='Jay'forupdate语句一共加了三把锁，分别是IX意向排他锁（不影响插入的表级锁），以及两个X排他锁（行锁，分别对应唯一索引和主键索引）。有读者说，这里不是加了一个IX表锁吗？为什么不阻止其他行的更新？实际上，这是一个有意的独占锁。意向排他锁：简称IX锁。当一个事务要对一条记录加X锁时，需要在表级加IX锁。比如select...forupdate，需要给表设置IX锁；既然有表锁，为什么事务2在对其他行执行update语句的时候不阻塞呢？这是因为：意向锁只表示意向锁和意向锁不互斥，可以并行化，锁兼容性如下：可能有朋友还有疑问，为什么会有两个X锁？不是同一条线被锁了吗？其实在RC隔离级别下，唯一索引的加锁是这样的：为什么不直接给唯一索引加一个X锁呢？为什么要锁定主键索引上的记录？如果使用并发SQL通过唯一索引条件更新主键索引：updateuser_info_tabsetuser_name='Xueyou'whereid='1570068';此时，如果select...forupdate语句没有使用主键索引，如果记录被锁住，并发update将不知道select...forupdate语句的存在，违反了对同一记录的更新/删除需要串行执行的约束。如果你对锁的基础知识还有疑问，可以看我之前的文章：2万字详解！InnoDB锁题！2.2RC隔离级别+主键RC隔离级别下，如果select...forupdate的查询条件是主键id，加什么样的锁？我们执行语句：select*fromuser_info_tabwhereid='1570070'forupdate;然后再启动另一个事务去更新同一条数据，发现阻塞了。如下图：事务2更新了其他行的记录，所以可以顺利执行，如下图：通过实验可以发现，如果在事务中更新其他行，则可以顺利执行。在RC隔离级别下，如果条件是主键，那么select...forupdate也会锁住行。根据2.1节的结论，select...forupdate会加一个表级的IX意向排他锁。因此，如果查询条件是id，select...forupdate会加两把锁。分表是一个IX意向排他锁（表锁，不影响插入），一个X排他锁（行锁，针对主键索引）我们执行语句查看加了什么样的锁。begin;select*fromuser_info_tabwhereid='1570070'forupdate;SELECT*FROMperformance_schema.data_locks\G;所以在RC隔离级别下，如果条件是主键，那么select...forupdateadded那里是两把锁，一把IX意向排他锁（不影响插入），一把X主键对应的排他锁（行锁，会锁住主键索引的行）。2.3RC隔离级别+普通索引RC隔离级别下，如果select...forupdate的查询条件是普通索引，加什么样的锁？这里先给原表增加一个普通索引：altertableuser_info_tabaddindexidx_city(city);我们执行语句：select*fromuser_info_tabwherecity='Guangzhou'forupdate;然后启动另一个事务来更新相同的数据，发现被阻塞了。如下图：如果事务2更新了其他行的记录，还是可以顺利执行的，如下图：我们来看select*fromuser_info_tabwherecity='Guangzhou'forupdate;加了什么样的锁，如下图：发现一共加了三个锁，分别是：IX意向排他锁（表锁），两个X排它锁（行锁，对应普通索引的X锁和主键的X锁）。如果查询条件没有命中数据库表的记录，应该加什么样的锁呢？我们换个查询条件：select*fromuser_info_tabwherecity='Guangzhou'andstatus='0'forupdate;发现只加了一把锁，即IX意向排它锁（表锁，不影响插入）。2.4RC隔离级别+无索引在RC隔离级别，如果select...forupdate的查询条件是无索引，加什么样的锁？大部分读者都觉得表被锁了，我们来验证一下。我们执行语句：select*fromuser_info_tabwhereage='26'forupdate;（年龄没有索引），然后启动另一个事务来更新数据。如下图：从上图可以看出，第一个事务执行了select...forupdate，然后第二个事务先更新其他行，发现可以顺利执行。如果执行更新的同一行，它仍然会阻塞并等待。可以得出结论，select...forupdate的查询条件是没有索引，主要是行锁。我们看一下具体的加锁情况：SELECT*FROMperformance_schema.data_locks\G;发现一共加了两把锁，分别是：IX意向排他锁（表锁），一个X排他锁（行锁，对应主键X锁）。为什么不是锁定表的X锁？这是因为：如果age列上没有索引，MySQL会使用聚簇（主键）索引进行全表扫描过滤。每条记录都会加X锁。不过为了效率，MySQL在这方面做了改进。扫描过程中，不满足过滤条件的记录将被解锁。同时优化违反了2PL原则。3、RR隔离级别3.1RR隔离级别+唯一索引如果是RR（可重复）数据库隔离级别，如果select...forupdate的查询条件是唯一索引，加什么样的锁？我们知道RR隔离级别高于RC隔离级别，主要区别在于间隙锁的概念。接下来我们通过实验来验证，首先设置数据库隔离级别为RR：mysql>setglobaltransactionisolationlevelrepeatableread;（好像设置后重启）QueryOK,0rowsaffected(0.00sec)我们执行语句：select*fromuser_info_tabwhereuser_name='Jaylen'forupdate;（user_name是唯一的索引），然后启动另一个事务来更新数据。如下图所示：从上图可以看出，即使是RR数据库隔离级别，事务也是先执行select...forupdate，然后事务先更新其他行，并且是发现可以顺利执行了。如果更新的行被执行，仍然会阻塞超时。我们看一下具体加的锁：可以发现无论是RC隔离级别还是RR隔离级别，select...forupdate，查询条件是唯一索引，当数据库表记录是hit，一共会加三把锁：一把IX意向排他锁（表锁，不影响插入），一把X排他锁（行锁）对应主键，一把X排他锁（行锁）对应到唯一索引。3.2RR隔离级别+主键如果数据库隔离级别是RR（可重复读），如果select...forupdate的查询条件是主键，加什么样的锁？根据之前的实验结果，我们其实可以推断应该和RC隔离级别一样，会加两把锁：一把IX意向排他锁（表锁，不影响插入），一把X排他主键对应的锁Locks（行锁，影响主键对应行的插入）。我们通过语句来确认一下，首先输入语句：begin;select*fromuser_info_tabwhereid='1570070'forupdate;SELECT*FROMperformance_schema.data_locks\G;大家可以看一下，和我们猜测的一样：3.3RR隔离级别+普通索引RR隔离级别下，如果select...forupdate的查询条件是普通索引，除了加X锁和IX锁、Gap锁也将被添加。Gaplock的提议是为了解决幻读问题而引入的。它是在两个索引之间添加的锁。我们来验证一下，先启动事务1，执行一些操作：begin；select*fromuser_info_tabwherecity='Guangzhou'进行更新；然后开始事务2begin;insertintouser_info_tab(`user_name`,`age`,`city`,`status`)values('Fangyan',27,'Shantou','1');验证流程图如下：可以发现插入新记录会被阻塞，那是因为gaplock的缘故，我们看看加了哪些锁：相对于RC隔离级别，gap锁确实多了,范围被锁定。我画一个这个场景加锁的示意图：如果要插入汕头市的记录，会被Gap锁锁住，所以会被阻塞。所以在RR隔离级别下，如果select...forupdate的查询条件是普通索引，如果命中查询记录，除了加X锁（行锁），IX锁（表锁，不affectinsertion），会加Gaplocks（间隙锁，会影响插入）。如果你对间隙锁不是很熟悉，可以看我之前的文章，20000字，详细讲解！InnoDB锁题！3.4RR隔离级别+无索引在RR隔离级别，如果select...forupdate的查询条件是无索引，会不会整表被锁？下面我们一起来验证一下，我们是直接按顺序执行下面的语句：begin；select*fromuser_info_tabwhereage='26'进行更新；从performance_schema.data_locks选择OBJECT_NAME、INDEX_NAME、LOCK_TYPE、LOCK_MODE、LOCK_STATUS、LOCK_DATA；可以发现，添加多把锁：一共有五把锁。这个IX锁（表级，意向排他锁），我们可以理解，和前面的例子一样。接下来的三个行锁就是给每一行的数据记录加X个排他锁（行锁，锁对象对应主键Id），我们也可以这样理解。但是第二行是什么X锁呢？我google了一下，什么是supremepseudo-record，找到了这样的解释：对于最后一个区间，next-keylock锁定了索引中最大值以上的间隙，并且“supremum”伪记录的值比任何一个都高实际上在索引中的值。supreme不是真正的索引记录，因此，实际上，这个下一键锁仅锁定最大索引值之后的间隙。翻译一下，大概意思是：相当于索引中所有的值都很大，但是在索引中不存在，相当于在最后一行之后加了一个间隙锁。我的理解是，如果查询条件有索引，就类似于一个（索引最大值，+无穷大）虚间隙锁。但是因为我们的查询字段age没有索引，锁是X锁，lock_data的值为supremumpseudo-record，意思是：全表行锁，所有的扫描都必须使用聚簇索引。也就是说在RR隔离级别下，forselect...forupdate，如果查询条件没有索引，一个IX锁（表锁，不影响插入），对每行一个X锁实际记录行，以及对应至上伪记录的虚拟全表行锁。这种场景，通俗地说，其实就是表锁。下面做个实验来验证虚拟全表行锁是否存在。首先，启动事务1，执行：begin;select*fromuser_info_tabwhereage='26'forupdate;然后启动事务2，执行插入语句：begin;insertintouser_info_tab(id,`user_name`,`age`,`city`,`status`)values(1,'Xiaoming',31,'Beijing','1');可以看到，被阻塞了，:add天罗兄有没有发现，以RR数据库隔离级别为例，select...forupdateconditions都命中了数据库表记录。在这里，天罗哥给大家出道一道题。RR隔离级别下，如果select...forupdate的查询条件没有命中当前数据表记录，应该加什么锁？来点刺激的，select...forupdate使两个条件：唯一索引（user_name）+无索引（status），然后错过当前数据表记录，你觉得会加什么样的锁？CREATETABLE`user_info_tab`(`id`intNOTNULLAUTO_INCREMENT,`user_name`varchar(255)DEFAULTNULL,`age`intDEFAULTNULL,`city`varchar(255)DEFAULTNULL,`status`varchar(4)NOTNULLDEFAULT'0',PRIMARYKEY(`id`),UNIQUEKEY`idx_user_name`(`user_name`)USINGBTREE,KEY`idx_city`(`city`))ENGINE=InnoDBAUTO_INCREMENT=1570074DEFAULTCHARSET=utf8mb3我们按顺序执行几句话：开始；select*fromuser_info_tabwhereuser_name='Jay'andstatus='0'forupdate;从performance_schema.data_locks选择OBJECT_NAME、INDEX_NAME、LOCK_TYPE、LOCK_MODE、LOCK_STATUS、LOCK_DATA；最后，我挑了蜗牛小男孩。通过这篇文章，你学到了哪些知识点？Select...forupdate在不同的场景中添加任何锁。如何查看一条SQL加了哪些锁（执行原生SQL，然后执行SELECT*FROMperformance_schema.data_lock如何手写死锁（分别开两个事务，造成锁冲突，文章中很多例子都是死锁例）
