MySQL InnoDB在Repeatable Read事务隔离级别的加锁情况

MySQL版本：8.0.30

REPEATABLE READ事务隔离级别索引加锁测试

CREATE TABLE `user` (
  `id` int NOT NULL,
  `money` int DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO user VALUES(1,1,10);
INSERT INTO user VALUES(4,4,14);
INSERT INTO user VALUES(8,8,18);
INSERT INTO user VALUES(12,12,22);
INSERT INTO user VALUES(16,16,26);

设置和查看事务隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL  REPEATABLE READ;
SELECT @@TRANSACTION_ISOLATION;

MySQL默认的事务隔离级别为REPEATABLE READ。

唯一索引命中

• 事务1对主键索引（或唯一索引）id=4加了记录互斥锁，所以T4时，事务2申请对id=4加记录互斥锁的请求被阻塞
• T5时，往id=3和id=5插入数据都可以成功
• T6时，事务1提交事务，释放了持有的id=4的记录锁，所以T7时，事务2再次申请id=4的记录互斥锁成功。
• T8和T9还原现有的环境，用于后续实验。
• 所以唯一索引命中，加记录互斥锁。

T4时，performance_schema.data_locks表的锁占用情况：

唯一索引未命中

• T2时，事务1访问id=3的主键索引，由于索引不存在，InnoDB在(1,4)范围加间隙锁
• T4时，事务2尝试在(1,4)的边界1和4获取互斥锁都能够成功；尝试在边界外5插入数据，也能够成功；另外有一个重要的注意点，间隙锁和间隙锁之间并不互斥，事务T1在(1,4)范围加了间隙锁后，事务T2能马上在(1,4)范围加间隙锁，并不会阻塞。
• T5时，回滚事务，重新开启一个事务，是为了简化data_locks表中的记录数，便于观察
• T6时，事务2尝试在(1,4)的间隙锁范围插入数据均被阻塞
• 所以，唯一索引未命中的情况下，会加间隙锁，开区间，间隙锁会阻塞在对应范围的数据插入，

T4截图

T6截图

唯一索引范围查询

• T2时，事务1获取了主键索引id=4的记录互斥锁，并获取了id=8的间隙锁，照理说应该锁定的范围是[4,8)
• T4时，事务2为id=1和id=8加记录互斥锁，都不阻塞
• T5时，事务2尝试在[4,8)范围获取记录互斥锁，均被阻塞，意料之中；但是出乎意料的是，id=2和id=3的记录也无法插入，所以事务1实际锁定的范围在(1,8)
• 从T5的截图可以看到，事务2的插入意向锁需要锁定(1,4)的间隙，而事务1锁定了id=4的记录互斥锁和(4,8)的间隙锁，不应该冲突啊，有没有哪位大佬解释下？
• 所以，唯一索引范围查询，使用开区间，临键锁

T2时，锁占用情况

T4时，锁占用情况

T5时，锁占用情况

非唯一索引命中

• T2时，事务1对非唯一索引age=14获取互斥锁，从截图可以看到，InnoDB对主键索引id=4加了互斥记录锁，对age=14加了互斥记录锁，对age=(14,18)加了互斥间隙锁
• T4时，事务2获取边界age=10和age=18的互斥锁都请求成功
• T5时，事务2获取id=4的互斥记录锁，被阻塞，符合预期；尝试在age=(14,18)插入数据，被阻塞，被阻塞；尝试在age=(10,14)插入数据，被阻塞，说明事务1锁定了(11,14)的范围
• 所以，非唯一索引的等值匹配命中，使用临键锁，锁定当前记录+前后间隙，是开区间(10,18)，且会锁定对应的主键索引的记录锁。

T2，加锁情况

T4，获取age=10的锁情况

非唯一索引未命中

• T2时，事务1对非唯一索引age=15获取互斥锁，由于未命中，从截图可以看到，InnoDB对age=(14,18)加了互斥间隙锁
• T4~T7验证了age=(14,18)的互斥间隙锁。

T2时，锁占用情况

非唯一索引范围查询

• T2时，事务1执行非唯一索引的范围查询后，InnoDB锁定了主键索引id=4，并锁定了age=14和age=18的非唯一索引。
• T4时，事务2访问age=10获取互斥锁，成功获取
• T5时，事务2无法获取age=14和age=18的非唯一索引的互斥锁，符合预期；但是，对于age=(10,14)和age=(14,18)范围的互斥锁也无法访问，说明InnoDB实际上在age=(10,18]范围加了临键锁。
• 所以，非唯一索引的范围查询使用临键锁，且区间为前开后闭。且会锁定区间内的主键索引的记录锁。

T2时，锁占用情况

T5执行插入age=12加锁情况

无索引命中

• T2时，事务1申请锁定money=8的记录，可以看到，InnoDB直接加了表锁，锁定了整张表，导致后续的查询都被阻塞。

T2时，加锁情况

无索引未命中

• 无索引情况下，即使不命中数据，也会加表锁。

无索引范围查询

• 无索引情况下，范围查询，也会加表锁。

总结

• 无索引情况下，一致性锁定读，即加锁读，直接加表锁
• 唯一索引：命中，记录互斥锁；未命中，会加开区间，间隙锁；范围查询，开区间，临键锁
• 非唯一索引：命中，主键索引加记录互斥锁，非唯一索引使用开区间，临键锁；未命中，会加开区间，间隙锁；范围查询，前开后闭，临键锁。