MySQL InnoDB加锁规则分析

标签：加锁 score 2023 索引 Session InnoDB user MySQL id

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

SELECT VERSION();
SHOW VARIABLES LIKE 'transaction_isolation';
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 <= 20 FOR UPDATE;
	UPDATE t_user SET score = score + 1 WHERE id = 20; 1205 - Lock wait timeout exceeded; try restarting transaction
		INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (21,'张辽','1021','2022-12-09',1); Affected rows: 1

id=10上加了记录锁，id=20上加了next-key锁

next-key锁是前开后闭区间，所以，最终锁定区间为：[10,20]

如果这里不是id>=10，而是id>10的话，最终只会在id=20上加next-key锁，这种情况下锁定区间为：(10,20]

2.2. 唯一索引（非主键）

情况一：等值查询，存在

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')这个区间内插入是不行的，但是更新是可以的

情况三：范围查找

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE id_card_no <= '1024' FOR UPDATE;
	INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (11,'潘凤','1011','2023-01-01',1); 1205 - Lock wait timeout exceeded; try restarting transaction
		INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (11,'潘凤','1031','2023-01-01',1); Affected rows: 1

主键索引上id=10和id=20都加了记录锁

辅助索引idx_card上加了Next-key锁，锁定范围为：(-∞, '1010']、('1010', '1020']、('1020', '1030']

2.3. 非唯一索引（普通索引）

情况一：等值查询，存在

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE birthday = '2023-12-01' FOR UPDATE;
	UPDATE t_user SET score = score + 1 WHERE birthday = '2023-12-11'; Affected rows: 0
		UPDATE t_user SET score = score + 1 WHERE birthday = '2023-12-09'; Affected rows: 0

主键索引id=10加记录锁

辅助索引idx_birthday上，'2023-12-01'上加Next-key锁，'2023-12-12'上加间隙锁

加锁区间：(-∞, 2023-12-01]、(2023-12-01, 2023-12-12)、id=10

因为是非唯一索引，所以当找到第一条birthday = '2023-12-01'的记录时，不确定后面还有没有这样的记录，所以必须继续往后找，直到遇到一条不是2023-12-01的记录未止。

间隙锁阻止其它事务插入，但是不阻止更新

情况二：范围查找

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE birthday >= '2023-11-11' AND birthday <='2023-11-28' FOR UPDATE;
	UPDATE t_user SET score = score + 1 WHERE birthday = '2023-11-29'; Affected rows: 0
		INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (13,'华雄','1033','2023-11-29',1); 1205 - Lock wait timeout exceeded; try restarting transaction

主键索引上加锁范围：id=30和id=40

辅助索引idx_birthday上加锁范围：(2023-01-01, 2023-11-12]、(2023-11-12, 2023-11-28]、(2023-11-28, 2023-11-30]

2.4. 不走索引

Session A	Session B	Session C
BEGIN; SELECT * FROM t_user WHERE score = 2 FOR UPDATE;
	UPDATE t_user SET score = score + 1 WHERE id = 33; Affected rows: 0
		INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (33,'颜良','1038','2023-12-20',1); Lock wait timeout exceeded; try restarting transaction