MySQL / 系统课时

推导等值查询的锁范围

推导等值查询的锁范围

等值条件看起来最简单,但“唯一索引命中、普通索引命中、目标记录不存在”三种情况的锁范围并不相同。本节用同一张表逐个实验,建立一套从访问路径推导 InnoDB 锁范围的方法。

学习目标

  • 区分唯一索引命中与普通二级索引命中时的锁范围
  • 解释等值条件查不到记录时为什么仍可能产生 Gap Lock
  • 先根据访问路径推导,再用并发插入验证结论

先把等值条件翻译成索引访问

判断一条等值锁定读,第一步不是看 =,而是看它实际走什么索引。唯一索引能把一个键值定位到至多一条记录;普通二级索引允许重复值,执行器必须扫描完整的等值键区间;记录不存在时,执行器最终会停在该键应该插入的位置。

三种典型情况可以先记成下面的推导结果:

条件 访问路径 主要锁范围
唯一索引等值且命中 唯一索引精确定位 已命中的索引记录,通常不需要相邻 Gap Lock
普通二级索引等值且命中 扫描全部重复键 匹配记录及保证等值范围不出现新记录所需的间隙
等值条件未命中 定位到插入位置 对应插入位置的 Gap Lock

唯一命中、普通索引命中与未命中时的等值查询锁范围对比

建立可重复的测试数据

下面同时准备唯一索引 uk_email 和普通二级索引 idx_city。后续三个实验都在 REPEATABLE READ 下执行。

sql
DROP TABLE IF EXISTS customer_lock_demo;
CREATE TABLE customer_lock_demo (
  id BIGINT PRIMARY KEY,
  email VARCHAR(64) NOT NULL,
  city VARCHAR(32) NOT NULL,
  UNIQUE KEY uk_email (email),
  KEY idx_city (city)
) ENGINE=InnoDB;

INSERT INTO customer_lock_demo (id, email, city) VALUES
(1, 'a@example.com', 'Beijing'),
(2, 'b@example.com', 'Beijing'),
(3, 'd@example.com', 'Shanghai'),
(4, 'e@example.com', 'Shenzhen');

预期结果

text
表 customer_lock_demo 创建成功,并写入 4 行数据。

唯一索引命中只需精确锁定记录

会话 A 通过唯一索引命中 b@example.com,并保持事务不提交。

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

SELECT id, email, city
FROM customer_lock_demo
WHERE email = 'b@example.com'
FOR UPDATE;

预期输出

text
+----+---------------+---------+
| id | email         | city    |
+----+---------------+---------+
|  2 | b@example.com | Beijing |
+----+---------------+---------+
1 row in set

会话 B 插入另一个不同的唯一键。因为会话 A 不需要用相邻间隙来防止第二个 b@example.com 出现,这个插入可以完成。

sql
INSERT INTO customer_lock_demo (id, email, city)
VALUES (10, 'bb@example.com', 'Guangzhou');

预期输出

text
Query OK, 1 row affected

实验结束后在会话 A 提交。

sql
COMMIT;

预期输出

text
Query OK, 0 rows affected

普通二级索引命中要保护整个等值区间

现在会话 A 改为锁定所有 city = 'Beijing' 的记录。普通索引允许以后再出现第三个 Beijing,所以只锁两条现有记录并不能阻止幻行。

sql
START TRANSACTION;

SELECT id, email, city
FROM customer_lock_demo
WHERE city = 'Beijing'
FOR UPDATE;

预期输出

text
+----+---------------+---------+
| id | email         | city    |
+----+---------------+---------+
|  1 | a@example.com | Beijing |
|  2 | b@example.com | Beijing |
+----+---------------+---------+
2 rows in set

会话 B 尝试再插入一个 Beijing。为了让实验快速返回,先缩短锁等待时间。

sql
SET SESSION innodb_lock_wait_timeout = 2;

INSERT INTO customer_lock_demo (id, email, city)
VALUES (11, 'x@example.com', 'Beijing');

预期输出

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

会话 A 提交后,相关记录锁和间隙锁一起释放。

sql
COMMIT;

预期输出

text
Query OK, 0 rows affected

记录不存在时锁住的是插入位置

最后看唯一索引“查无此值”的情况。当前数据中没有 c@example.com,但它按索引顺序应位于 bb@example.com 与 d@example.com 之间。会话 A 的锁定读虽然返回空集,仍需要保护这个插入位置,避免事务期间突然出现满足条件的新记录。

sql
START TRANSACTION;

SELECT id, email, city
FROM customer_lock_demo
WHERE email = 'c@example.com'
FOR UPDATE;

预期输出

text
Empty set

会话 B 此时插入同一个唯一键会等待,并在超时后报错。

sql
SET SESSION innodb_lock_wait_timeout = 2;

INSERT INTO customer_lock_demo (id, email, city)
VALUES (12, 'c@example.com', 'Chengdu');

预期输出

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

完成实验后在会话 A 提交。

sql
COMMIT;

预期输出

text
Query OK, 0 rows affected

推导等值查询时,可以固定采用同一顺序:先用 EXPLAIN 确认访问索引,再判断该索引是否唯一、条件是否命中,随后把匹配键和插入位置放到有序索引线上,最后用并发写入验证。真正决定锁范围的是索引访问路径,不是 SQL 表面上有没有写等号。

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

目录与阅读设置

阅读设置

17

记录一个想法

推导等值查询的锁范围

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

登录哪都通