MySQL 加锁规则

加锁规则

Tips：

间隙锁在可重复读级别下才有效
临键锁（Next-Key Lock）是由间隙锁和行锁组成的一个左开右闭区间

加锁规则：

原则1：加锁的基本单位是临键锁
原则2：查找过程中访问到的对象才会加锁
优化1：索引上的等值查询，给唯一索引加锁的时候，临键锁退化为行锁
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，临键锁退化为间隙锁
bug：~~唯一索引上的范围查询会访问到不满足条件的第一个值为止~~（8.0.18 已修复）

实验表结构及数据：

-- version: 8.0.32
DROP DATABASE IF EXISTS mysql45_21;
CREATE DATABASE mysql45_21;
USE mysql45_21;

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

INSERT INTO t VALUES
(0, 0, 0), (5, 5, 5), (10, 10, 10),
(15, 15, 15), (20, 20, 20), (25, 25, 25);

在可重复读隔离级别下，SELECT 查询使用快照读，是不会加任何读写锁的，下面查询会返回空：

SELECT * FROM performance_schema.data_locks;

索引等值查询临键锁退化为间隙锁

会话1：

-- 原则1：在主键索引查找 id = 7，加锁基本单位是临键锁，加临键锁 (5, 10]
-- 优化2：索引上的等值查询，最右一个值不满足等值条件，间临键锁退化为间隙锁 (5, 10)
BEGIN;
UPDATE t SET d = d + 1 WHERE id = 7;

会话2：

-- 被间隙锁 (5, 10) 阻塞
INSERT INTO t VALUES (8, 8, 8);

会话3：

-- 获取行锁（写锁） id = 10 成功
UPDATE t SET d = d + 1 WHERE id = 10;

非唯一索引等值锁

会话1：

-- 原则1：索引c 是普通索引（非唯一索引），扫描到第一行 c=5 的数据后还有向右扫描，加锁范围 (0, 5], (5, 10]
-- 原则2：覆盖索引不会访问到主键索引，主键索引没有加锁
-- 如果是 SELECT...FOR UPDATE，则会对主键索引上满足条件的行加锁
-- 优化2：索引等值查询，最右一个值不满足条件，临键锁退化为间隙锁 (5, 10)
-- 最终加锁范围：(0, 5], (5, 10)
BEGIN;
SELECT id FROM t WHERE c = 5 LOCK IN SHARE MODE;

会话2：

-- 主键索引没有加锁，更新成功
UPDATE t SET d = d + 1 WHERE id = 5;

会话3：

-- 因为间隙锁 (5, 10)，插入失败
INSERT INTO t VALUES (7, 7, 7);

补充：当会话1 执行 SELECT...FOR UPDATE 或 SELECT d FROM t WHERE c = 5 LOCK IN SHARE MODE; （非覆盖索引）后，以下语句会被阻塞

-- 获取行锁 id=5 被阻塞
UPDATE t SET d = d + 1 WHERE id = 5;

主键索引范围查询

会话1：

-- 原则1：查找 id=10，加锁基本单位，加临键锁 (5, 10]
-- 优化1：唯一索引等值查询，临键锁退化为间隙锁 id=10

-- 原则1：范围查询 id > 10，加临键锁 (10, 15]
-- 虽然这里是范围查询不满足优化2，但在 8.0.32 版本，是会退化为间隙锁 (10, 15) 的

-- 最终加锁范围：行锁 id=10, 间隙锁 (10, 15)
BEGIN;
SELECT * FROM t WHERE id >= 10 AND id < 11 FOR UPDATE;

会话2：

-- 临键锁(5, 10] 退化为 行锁id=10，插入成功
INSERT INTO t VALUES (8, 8, 8);

-- 被间隙锁 (10, 15) 阻塞
INSERT INTO t VALUES (13, 13, 13);

会话3：

-- 临键锁(10, 15] 退化为 间隙锁(10, 15)，获得行锁 id=5 成功
UPDATE t SET d = d + 1 WHERE id = 15;

非唯一索引范围查询

会话1：

-- 原则1：查找 c=10 加锁基本单位临键锁 (5, 10]
-- 非唯一索引，不符合 优化1，不会退化为行锁

-- c > 10 范围查询，继续向右扫描，加临键锁 (10, 15]
-- 8.0.32 索引c 不会像主键索引一样，将 (10, 15] 退化为间隙锁，但确实不会对主键 id=15 加锁

-- SELECT...FOR UDPATE，对主键加行锁 id=10

BEGIN;
SELECT * FROM t WHERE c >= 10 AND c < 11 FOR UPDATE;

会话2：

-- 间隙锁 (5, 10) 阻塞
INSERT INTO t VALUES (8, 8, 8);

会话3：

-- 被 索引c 临键锁 (10, 15] 阻塞
UPDATE t SET d = d + 1 WHERE c = 15;

-- 获取主键行锁 id=15 成功
UPDATE t SET d = d + 1 WHERE id = 15;