索引失效不会导致表锁

本文将通过实验来验证“索引失效不会加表锁”这个事实。

前提知识 #

临键锁（next-key lock）是间隙锁+记录锁，有“访问到哪锁到哪”的特点， 在 RR 隔离级别下索引失效时，会扫描全表，这时如果是加临键锁，最终会锁住所有索引间隙和记录， 如果是加表锁，其他事务的写操作应该会被阻塞。

实验准备 #

本文所有实验使用的 MySQL 版本均为 8.0.22 。 执行如下 SQL 来创建表和添加数据：

drop table if exists test;
create table test (
    id int primary key auto_increment,
	name varchar(63),
	index idx_name (name)
) engine innodb;
insert into test (name) values ('1'),('2'),('3'),('4'),('5');
delete from test where id=4;
delete from test where id=5;
insert into test (name) values ('6'),('7');

确认准备的数据与下面的一致：

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  6 | 6    |
|  7 | 7    |
+----+------+
5 rows in set (0.00 sec)

实验 1 ：表锁会排斥 select ... for update #

先执行事务 A ：

-- 事务A
set autocommit = 0;
set session transaction isolation level REPEATABLE READ;

lock tables test read;

再执行事务 B ：

-- 事务B
set autocommit = 0;
set session transaction isolation level REPEATABLE READ;

select * from test where id=5 for update;
-- 被阻塞了

事务 B 被阻塞了，证明表锁会排斥 select ... for update 语句。

实验 2 ：索引失效不会加表锁 #

先执行事务 A。where name=3 不加引号让索引失效：

-- 事务A
set autocommit = 0;
set session transaction isolation level REPEATABLE READ;

update test set name='33' where name=3;

再执行事务 B。这里 id=5 是特别设计的，数据库中并不存在 id=5 的数据，会获取间隙锁， 而间隙锁与间隙锁之间是兼容的，所以如果事务 A 加的是 next-key lock，也不会阻塞事务 B ：

-- 事务B
set autocommit = 0;
set session transaction isolation level REPEATABLE READ;

select * from test where id=5 for update;
-- 不被阻塞，证明索引失效不是加表锁

最后执行事务 C ：

-- 事务C
set autocommit = 0;
set session transaction isolation level REPEATABLE READ;

update test set name='66' where name='6';
-- 被阻塞了

索引失效时，如果是加表锁，事务 B 应该会被阻塞，但实际上并没有， 因此，索引失效时不会加表锁。

实验 2 对照实验 #

保持实验 2 中的所有条件不变，仅将事务 A 中的 where name=3 改为能用到索引的 where name='3' ， 也就是加了引号，再重新进行该实验。

实验结果是，只有事务 C 发生了变化，事务 C 没有发生阻塞的情况，结果符合预期。

实验变种 1 #

如果保持实验 2 其他语句不变，只将隔离级别改为 READ COMMITTED：

set session transaction isolation level READ COMMITTED;

事务 C 存在的阻塞情况并不会发生，说明 RC 隔离级别下，索引失效不会导致表锁，也没有临键锁。

如果接着再开启一个事务 D 更新 id=3 的数据：

-- 事务C
set autocommit = 0;
set session transaction isolation level READ COMMITTED;

update test set name='33' where id=3;
-- 被阻塞了

事务 D 被阻塞了说明，RC 隔离级别下，update 会给查询到的行加行锁。

实验变种 2 #

如果保持实验 2 其他语句不变，只将事务 A 的 update 语句改成：

select * from test where name=3 for update;

事务 C 的阻塞情况仍然存在，说明 RR 隔离级别下索引失效时， select ... for update 加锁效果与 update 类似。

结论 #

• RR 隔离级别下，update、select ... for update 索引失效不会加表锁，可能是加能锁住所有索引间隙和记录的临键锁，因为实验中所有的现象都符合临键锁的特点。
• RC 隔离级别下，update、select ... for update 索引失效不会加表锁，也不加间隙锁。