本篇文章着重参考链接 2 复现实验，并根据data_locaks表实际查看锁情况(仅限简单查询)

环境: mysql Ver 8.0.25 for macos11.3 on x86_64 (Homebrew) 事务隔离级别为 可重复读 表语句: CREATE TABLE test ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’, name varchar(256) NOT NULL COMMENT ‘用户名称’, desc varchar(256) DEFAULT NULL COMMENT ‘描述’, PRIMARY KEY (id), KEY idx_name (name) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT=’测试表’; 插入数据: insert into test (id,name,desc) values(1,’a’,’xx’); insert into test (id,name,desc) values(4,’b’,’xx’); insert into test (id,name,desc) values(5,’b’,’xx’); insert into test (id,name,desc) values(12,’d’,’xx’); insert into test (id,name,desc) values(13,’d’,’xx’);

1、唯一索引命中查询

开启事务执行sql：select * from test where id = 1 for update;
锁情况：

1. 表级意向锁
2. 记录锁，锁住索引 1

1.1、update test set desc = ‘yy’ where id = 1;

hang住

锁情况

1. 加了表级意向锁
2. 等待索引1的记录锁

1.2、结论

sql语句命中唯一索引，则添加给定索引值的记录锁

2、唯一索引未命中查询

开启事务执行sql：select * from test where id = 3 for update;

锁情况：

1. 表级意向锁
2. GAP锁，LOCK_DATA为4，则锁住区间 (1,4)

2.1、insert into test (id,name,desc) values(2,’b’,’xx’);

hang住

锁情况

1. 加了表级意向锁
2. 等待LOCK_DATA为4的GAP锁，插入意向锁

2.2、update test set desc = ‘yyy’ where id = 4;

不会阻塞

锁情况

1. 加了表级意向锁
2. 持有索引为4的记录锁

2.3、update test set desc = ‘yyy’ where id = 1;

不会阻塞

锁情况

1. 加了表级意向锁
2. 持有索引为1的记录锁

2.4、select * from test where id = 3 for update;

不会阻塞

锁情况

1. 表级意向锁
2. GAP锁，LOCK_DATA为4，则锁住区间 (1,4)

2.5、结论

sql语句未命中唯一索引，则

1. 会添加给定不存在记录锁在区间的GAP锁
2. 不会对区间值添加记录锁
3. GAP锁与GAP锁不互斥
4. GAP锁 + 插入意向锁与GAP互斥

3、普通索引命中查询

开启事务执行sql：select * from test where name = ‘b’ for update;
锁情况：

1. 表级意向锁
2. ‘b’, 4 Next-Key Lock
3. ‘b’, 5 Next-Key Lock
4. 主键索引4、5的记录锁
5. ‘d’, 12的GAP锁

因此锁住的区间为( ‘a’-1, ‘b’-4]、(‘b’-4, ‘b’-5]、4、5、(‘b’-5, ‘d’-12) 简化下，即为(‘a’-1, ‘d’-12), 主键索引4、5以及普通索引’b’-4, ‘b’-5

3.1、update test set desc = ‘zzz’ where id = 4;

更新主键索引为5的记录情况同4一样，不再单独记录

hang住
锁情况：

1. 表级意向锁
2. 等待索引4的记录锁

3.2、update test set desc = ‘zzz’ where name = ‘b’;

hang住
锁情况：

1. 表级意向锁
2. 等待普通索引4的记录锁

3.3、update test set desc = ‘zzz’ where name = ‘d’;

不会阻塞

锁情况：

1. 锁情况同 name = ‘b’ 一样
2. 不同点在于，’d’-13记录之后没有记录，因此上锁的记录为 supremum pseudo-record

3.4、insert into test (name) values(‘b’);

hang住

锁情况：

1. 表级意向锁
2. 等待 ‘d’-12 的 GAP + 插入意向锁

3.5、insert into test (id,name) values(3,’b’);

hang住

锁情况：

1. 表级意向锁
2. 等待 ‘b’-4 的 GAP + 插入意向锁

3.6、insert into test (id,name) values(2,’a’);

hang住

锁情况：

1. 表级意向锁
2. 等待 ‘b’-4 的 GAP + 插入意向锁

3.7、insert into test (id,name) values(11,’d’) && insert into test (name) values(‘d’)

上述两个SQL同3.4、3.5、3.6，不再赘述

3.8、结论

参考 select * from test where name = ‘b’ for update; 锁情况，可以得出：

1. 会将所有符合非唯一索引的记录加上NextKey Lock
2. 会将所有符合非唯一索引的记录对应的主键索引加上记录锁
3. 会将符合非唯一索引的记录的下一条记录加上GAP锁

4、普通索引未命中查询

开启事务执行sql：select * from test where name = ‘c’ for update;
锁情况：

1. 表级意向锁
2. ‘d’, 12的GAP锁

因此锁住的区间为(‘b’-5, ‘d’-12)

4.1、insert into test (name) values(‘c’);

hang住

锁情况：

1. 表级意向锁
2. 等待 ‘d’-12 的 GAP + 插入意向锁

4.2、insert into test (id,name) values(6,’b’);

hang住

锁情况：

1. 表级意向锁
2. 等待 ‘d’-12 的 GAP + 插入意向锁

4.3、insert into test (id,name) values(11,’d’);

hang住

锁情况：

1. 表级意向锁
2. 等待 ‘d’-12 的 GAP + 插入意向锁

4.4、insert into test (id,name) values(3,’b’);

不会阻塞

锁情况：

1. 表级意向锁

4.5、insert into test (name) values(‘d’);

不会阻塞

锁情况：

1. 表级意向锁

4.6、结论

若普通索引未命中则：

1. 会将符合非唯一索引的记录所在区间加上GAP锁
2. 关于4.4、4.5只加了表级锁的解释。其实还加了记录锁，只有当你update最新的记录的时候再看才会查出来

PS、一些额外的case

1、查询未走使用索引

不论sql是否有查询结果，会给表中的每一条记录都加上NextKey Lock

2、关于删除

2.1、找到满足条件的记录，并且记录有效，且命中唯一索引

给定查询记录加对应索引的记录锁

2.2、找到满足条件的记录，并且记录有效，且命中非唯一索引

1. 给定查询记录加对应非唯一索引的NextKey Lock
2. 给定查询记录加对应主键索引的Record Lock
3. 给下一条记录加gap锁(防止与插入冲突)

2.3、未找到满足条件的记录，且使用非唯一索引

1. 给下一条记录加gap锁(加在非唯一索引身上)

2.4、未找到满足条件的记录，且使用唯一索引

1. 给下一条记录加gap锁(加在唯一索引身上)