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 ASession 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 ASession BSession 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 ASession BSession 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 ASession BSession 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 ASession 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 ASession BSession 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 ASession BSession 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 ASession BSession 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 ASession BSession 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 ASession BSession 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

在所有记录的主键上加next-key锁

加锁范围：(-∞, 10]、(10, 20]、(20, 30]、(30, 40]、(40, 50]、(50, +∞)

3. 总结

1、主键索引

  • 等值查询，命中，则被命中的主键索引加记录锁
  • 等值查询，未命中，则继续向后（向右）查找，直到找到第一个不满足的记录，对该记录加间隙锁，即锁住该记录之前的间隙，以防止其它事务向其中插入数据
  • 范围查找，找到的（满足条件的）记录的主键加记录锁，扫描过的区间加间隙锁

2、非主键唯一索引

  • 与主键索引类似，唯一的区别是锁住辅助索引记录的同时会锁住对应的主键索引

3、非唯一索引

  • 向右查找直到遇到一条不满足条件的记录，然后对扫描到的区间加间隙锁，对扫描到的辅助索引记录加记录锁，同时对与其对应的主键加记录锁

4、不走索引

  • 表中所有记录的主键加next-key锁

总结几个规律：

  1. 命中的索引记录会加记录锁，如果它是一个辅助索引，则对应的主键索引也会被加上记录锁
  2. 没有命中的记录不会被加记录锁
  3. 非唯一索引上查找时，当找到第一条满足条件的索引记录时，还会继续向右查找，直到遇到一条不满足条件的记录（PS：幸亏索引是有序的，不然找到累死）
  4. 当一条SQL没有走索引时，那么将会在每一条聚集索引上加X锁，这个类似于表锁，但原理上和表锁是完全不同的

建议：

  1. 尽量控制事务大小，减少锁定资源量和时间长度
  2. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的。如果 MySQL 认为全表扫描效率更高，它就不会使用索引。因此，在分析锁冲突时，可以查看执行计划（explain）以确认是否真正使用了索引

最后，重要的事情说三遍：

  • 加锁的单位是next-key锁
  • 加锁的单位是next-key锁
  • 加锁的单位是next-key锁

参考

https://www.cnblogs.com/harda/p/16820592.html

https://blog.csdn.net/qq_42604176/article/details/115431744

https://zhuanlan.zhihu.com/p/378306056

https://cloud.tencent.com/developer/article/1971381

https://cloud.tencent.com/developer/article/1844928