MySQL / 系统课时

推导范围更新的锁范围

推导范围更新的锁范围

范围 UPDATE 或 DELETE 的并发影响,不只取决于最终命中了几行,还取决于 InnoDB 通过哪个索引、扫描了多大的键范围。本课用两个会话制造阻塞,反推出记录锁与间隙锁覆盖的区间,并说明为什么条件稍微放宽就可能显著扩大冲突面。

学习目标

  • 根据执行使用的索引推导范围更新的大致加锁区间
  • 用并发插入实验区分记录冲突与间隙冲突
  • 识别隔离级别、索引选择对锁范围的影响

先把条件映射到索引扫描

InnoDB 的行锁本质上锁在索引记录上。默认 REPEATABLE READ 下,范围搜索为了避免幻读,通常会对扫描到的索引记录及其相邻间隙使用 next-key lock。于是同一条业务条件,如果走高选择性的二级索引,锁范围通常较集中;如果没有合适索引而扩大扫描,冲突面也会随之扩大。

先建立一个只有五个索引键值的实验表,后续所有会话都基于 idx_score 做范围扫描。

sql
DROP TABLE IF EXISTS range_lock_demo;
CREATE TABLE range_lock_demo (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  score INT NOT NULL,
  note VARCHAR(32),
  KEY idx_score (score)
) ENGINE=InnoDB;

INSERT INTO range_lock_demo(score, note)
VALUES (10, NULL), (20, NULL), (30, NULL), (40, NULL), (50, NULL);

SELECT id, score, note
FROM range_lock_demo
ORDER BY score;

预期结果

text
id  score  note
1   10     NULL
2   20     NULL
3   30     NULL
4   40     NULL
5   50     NULL

范围更新沿二级索引扫描并形成记录与间隙锁窗口

用阻塞反推 UPDATE 的锁窗口

在会话 A 开启事务并更新 20 <= score < 40。语句命中两行,但事务不提交,让锁保持住。

sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

UPDATE range_lock_demo
SET note = 'held'
WHERE score >= 20 AND score < 40;

SELECT ROW_COUNT() AS affected_rows;

预期结果

text
affected_rows
2

此时在会话 B 把锁等待时间缩短,再尝试向两个已存在键之间插入 score=25。它不与任何现有记录主键重复,却落在范围扫描保护的间隙内,因此会等待会话 A;超时后返回锁等待错误。

sql
SET SESSION innodb_lock_wait_timeout = 2;
START TRANSACTION;

INSERT INTO range_lock_demo(score, note)
VALUES (25, 'blocked');

预期结果

text
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这说明范围更新的冲突面不能只按“被修改的两行”理解。对范围索引扫描而言,插入到受保护间隙的新键也可能被阻塞。实验结束后先回滚会话 B,再回滚会话 A,恢复初始数据。

放宽条件为何会放大并发冲突

把上界从 < 40 放宽为 < 50,会多扫描并修改 score=40。重新在会话 A 执行下面的事务并保持不提交。

sql
START TRANSACTION;

UPDATE range_lock_demo
SET note = 'wider'
WHERE score >= 20 AND score < 50;

SELECT ROW_COUNT() AS affected_rows;

预期结果

text
affected_rows
3

随后在会话 B 插入 score=45。这个值在上一组较窄条件下位于扫描区间之外,而现在落入更大的索引范围,因此会被新的锁窗口阻塞。

sql
SET SESSION innodb_lock_wait_timeout = 2;
START TRANSACTION;

INSERT INTO range_lock_demo(score, note)
VALUES (45, 'blocked-by-wider-range');

预期结果

text
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

范围条件每多覆盖一段热门键空间,就可能把更多更新、删除和插入纳入等待关系。生产环境中,“只多查一点”可能意味着“多锁一片”,这也是批量状态更新经常突然放大锁竞争的原因。

DELETE 也按访问路径获得锁

DELETE 虽然删除的是记录,但定位候选行仍然要走索引扫描,因此同样需要先锁住相关索引记录;在 REPEATABLE READ 的范围条件下,也可能对间隙形成保护。回滚上一组两个会话后,在会话 A 执行:

sql
START TRANSACTION;

DELETE FROM range_lock_demo
WHERE score >= 20 AND score < 40;

SELECT ROW_COUNT() AS affected_rows;

预期结果

text
affected_rows
2

保持会话 A 不提交,再在会话 B 尝试向同一间隙插入新键。

sql
SET SESSION innodb_lock_wait_timeout = 2;
START TRANSACTION;

INSERT INTO range_lock_demo(score, note)
VALUES (25, 'blocked-by-delete');

预期结果

text
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

验证完成后回滚两个事务即可恢复数据。也就是说,判断锁范围时应先问“这条语句怎样扫描索引”,而不是先问“它是 UPDATE 还是 DELETE”。

推导时要带上三个边界条件

第一,先用 EXPLAIN 确认实际访问索引。条件写在 score 上不代表优化器一定使用 idx_score;如果扫描路径改变,锁住的索引记录也会改变。

第二,隔离级别会改变间隙锁行为。上述实验针对 InnoDB 的 REPEATABLE READ;在 READ COMMITTED 下,普通搜索与索引扫描通常会减少 gap lock 的使用,因此插入冲突现象可能不同。

第三,锁范围是“执行计划 + 索引结构 + 条件边界”的共同结果。遇到线上锁竞争时,应把阻塞 SQL、执行计划和索引键分布放在一起分析,才能判断是单行热点,还是范围扫描把冲突窗口扩得过大。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

推导范围更新的锁范围

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通