MySQL InnoDB加锁规则分析

谢冬

1.  基础知识回顾

1、索引的有序性，索引本身就是有序的
2、InnoDB中间隙锁的唯一目的是防止其他事务插入间隙。间隙锁可以共存。一个事务取得的间隙锁并不会阻止另一个事务取得同一间隙上的间隙锁。共享和独占间隔锁之间没有区别。它们彼此之间不冲突，并且执行相同的功能。
3、MySQL默认隔离级别是 REPEATABLE-READ
4、加锁的对象是索引，加锁的基本单位是next-key锁，而行锁和间隙锁，是由next-key锁退化而来的
5、记录锁，锁的是索引，而非数据本身
6、间隙锁是开区间，next-key锁是前开后闭区间
7、意向锁是表级锁，它相当于一个标志，可以用来提高加锁的效率
8、间隙锁的目的是为了防止幻读，在“读已提交”隔离级别下允许幻读，所以如果隔离级别是“读已提交”，就不会用到间隙锁，更不会用到next-key锁。因此，只有“可重复读”及以上隔离级别下，才会有next-key锁
9、InnoDB中锁住的是索引。对辅助索引加锁时，辅助索引所对应的主键索引也会被锁住。
10、所谓“间隙”本质是又间隙右边的那条记录决定的
 
接下来，具体看一下走不同的索引时的加锁情况。本例中使用的MySQL版本为8.0.30

1. SELECT VERSION();
2. SHOW VARIABLES LIKE 'transaction_isolation';
3. SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

复制代码

测试表结构及数据如下：

2.  案例分析
LOCK_MODE不同值的含义：

• X ：代表next-key锁
  
• X,GAP ：代表间隙锁
  
• X,REC_NOT_GAP ：代表记录锁
  

2.1.  主键索引
情况一：等值查询，存在

Session A	Session B
BEGIN; SELECT * FROM t_user WHERE id = 10 FOR UPDATE;
	INSERT INTO t_user (id, `name`, id_card_no, birthday, score) VALUES (9, '于禁', '1012', '2023-11-01', 1); Affected rows: 1

首先对表加意向排它锁，然后对主键加记录锁，可以看到只锁住了id=10这个主键索引
情况二：等值查询，不存在

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id = 5 FOR UPDATE;
	INSERT INTO t_user (id, `name`, id_card_no, birthday, score) VALUES (6, '于禁', '1012', '2023-11-01', 1); 1205 - Lock wait timeout exceeded; try restarting transaction
		UPDATE t_user SET score = score + 1 WHERE id = 10; Affected rows: 1

加锁范围： (-∞, 10)
注意，是开区间，10并没有被锁

情况三：范围查找

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id >= 10 AND id < 11 FOR UPDATE;
	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (9,'典韦','1011','2022-12-19',1) Affected rows: 1
		INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (18,'徐晃','1018','2022-12-09',1); 1205 - Lock wait timeout exceeded; try restarting transaction

一个记录锁10，加一个间隙锁(10, 20)，合起来就是[10, 20)
锁定区间：[10, 20)

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id >= 10 AND id =10，而是id>10的话，最终只会在id=20上加next-key锁，这种情况下锁定区间为：(10,20]</p>2.2.  唯一索引（非主键） 情况一：等值查询，存在 [table]
Session A	Session B
BEGIN; SELECT * FROM t_user WHERE id_card_no = '1003' FOR UPDATE;
	UPDATE t_user SET score = score + 1 WHERE id = 30; 1205 - Lock wait timeout exceeded; try restarting transaction

辅助索引 ('1003',30)加记录锁，同时，主键索引上id=30加记录锁
情况二：等值查询，不存在

先看一眼现在的数据

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id_card_no = '1042' FOR UPDATE;
	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (52,'许褚','1041','2023-01-01',1); 1205 - Lock wait timeout exceeded; try restarting transaction
		UPDATE t_user SET score = score + 1 WHERE id_card_no = '1041'; Affected rows: 0

只在辅助索引idx_card上加了间隙锁，锁定范围是：('1040', '1050')
索引是有序的，尽管索引字段类型是字符串类型，仍然是有序的
因为是间隙锁，所以没有锁定1050，也就自然不会给id=50加记录锁
值得注意的是，在('1040', '1050')这个区间内插入是不行的，但是更新是可以的
情况三：范围查找

[table][tr][td]Session A[/td][td]Session B[/td][td]Session C[/td][/tr][tr][td]BEGIN;
SELECT * FROM t_user WHERE id_card_no = '2023-11-11' AND birthday