MySQL InnoDB 加锁机制

MySQL 版本: 8.0.25

隔离级别: 可重复读

InnoDB有两种不同的SELECT，即普通SELECT 和 锁定读SELECT. 锁定读SELECT 又有两种，即SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE; 锁定读SELECT 之外的则是 普通SELECT

不同的SELECT是否都需要加锁呢?

1. 普通SELECT 时使用一致性非锁定读，MVCC, 不加锁;
2. 锁定读SELECT 使用锁定读(当前读)，加锁;
3. 此外，DML(INSERT/UPDATE/DELETE)时，需要先查询表中的记录，此时也使用锁定读，加锁;

FOR SHARE 语法是 MySQL 8.0 时加入的，FOR SHARE 和 LOCK IN SHARE MODE 是等价的，但FOR SHARE 用于替代 LOCK IN SHARE MODE，不过，为了兼容，LOCK IN SHARE MODE依然可用

1.一致性非锁定读(consistent nonlocking read) InnoDB采用多版本并发控制(MVCC, multi version concurrency control)来增加读操作的并发性。MVCC是指，InnoDB使用基于时间点的快照来获取查询结果，读取时在访问的表上不设置任何锁，因此，在事务T1读取的同一时刻，事务T2可以自由的修改事务T1所读取的数据。这种读操作被称为一致性非锁定读。这里的读操作就是普通SELECT 隔离级别为RU和Serializable时不需要MVCC，因此，只有RC和RR时，才存在MVCC，才存在一致性非锁定读。 一致性非锁定读在两种隔离级别RC和RR时，是否有什么不同呢?是的，两种隔离级别下，拍得快照的时间点不同 RC时，同一个事务内的每一个一致性读总是设置和读取它自己的快照。也就是说，每次读取时，都再重新拍得一个的快照(所以，RC时总是可以读取到新提交的数据)。 RR时，同一个事务内的所有的一致性读 总是读取同一个快照，此快照是执行该事务的个一致性读时所拍得的

2.锁定读(locking read) 如果你先查询数据，然后，在同一个事务内插入/更新 相关数据，普通的SELECT语句是不能给你足够的保护的。其他事务可以 更新/删除 你刚刚查出的数据行。InnoDB提供两种锁定读，即：SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE。它俩都能提供额外的安全性。 这两种锁定读在搜索时所遇到的(注意：不是最终结果集中, 但MySQL会对其做一定的优化)每一条索引记录(Index Record)上设置排它锁或共享锁。此外，如果当前隔离级别是RR，它还会在每个索引记录前面的间隙上设置间隙锁(gap lock)

在这之前, 你有必要了解下InnoDB使用的B+Tree的结构

InnoDB提供的各种锁

1. Shared Locks, 共享锁, 缩写 S, 当前会话对某表/行加上了S锁时, 其他会话仍可以对其加上S锁, 但不能再加X锁, 也可理解为读锁
2. Exclusive Locks, 排他锁, 缩写X, 当前会话对某表/行加上了X锁时, 其他会话不能对其加上X锁, 也不能再加S锁, 也可理解为写锁S X并不是一种锁类型，而是其他各种锁的模式，每种锁都有Shard或Exclusive两种模式

3.Intention Locks, 意向锁, 缩写I, 表锁, 只作用于表, 含义是已经持有了表锁，稍候将获取该表上某个/些行的行锁. 事务在获取行锁之前, 首先要获取到意向锁 事务在获取行上的S锁之前, 事务必须首先获取 表上的 IS锁或表上的更强的锁 事务在获取行上的X锁之前, 事务必须首先获取 表上的 IX锁

4.锁类型的兼容性如下

意向锁IS和IX 和任何行锁都兼容(即: 和行的X锁或S锁都兼容) 任何意向锁之间都是兼容的 意向锁只会阻塞 全表请求(例如: LOCK TABLES ... WRITE), 不会阻塞其他任何东西. 因为LOCK TABLES ... WRITE需要设置X表锁，这会被意向锁IS或IX所阻塞 使用意向锁, 实现了"表锁是否冲突"的快速判断. 意向锁就是协调行锁和表锁之间的关系的, 或者也可以说, 意向锁是协调表上面的读写锁和行上面的读写锁(也就是不同粒度的锁)之间的关系的 意向锁类似厕所门上的标识, 红的就是有人, 绿的就是没人. 如果没有这个标识, 还要推开厕所门才能知道厕所里有没有人

5.Gap Locks, 间隙锁 LOCK_MODE分别是: S,GAP或X,GAP索引记录之间间隙上的锁, 锁定尚未存在的记录, 即索引记录之间的间隙. 有Shard或Exclusive两种模式, 但, 两种模式没有任何区别, 二者等价 间隙锁是在索引记录之间的间隙上的锁, 或在第一条索引记录之前或最后一条索引记录之后的间隙上的锁, 且BTree+的索引是从小到大组织的. 例如, SELECT c1 FROM t WHERE c1 BETWEEN 1 AND 6 FOR UPDATE; 锁会阻止其他事务将值2插入 c1的索引记录中，因为该范围内所有现有值之间的间隙都已锁定, 防止满足条件的数据被插入. 此时联想一下可重复读的事务隔离级别 间隙锁实际锁的是索引记录叶子节点的next指针, 叶子节点存储的是主键id或行记录, 后面有演示 间隙锁可能跨越单个索引值、多个索引值，甚至是空的 使用唯一索引锁定行的语句不需要间隙锁定. (这不涵盖搜索条件仅使用多字段联合唯一索引的部分列的情况; 例status&age组成联合唯一索引, 但where条件仅使用age字段, 在这种情况下, 会发生间隙锁定) 例如，如果该no列具有唯一索引，则以下语句仅使用值为100的行的索引记录锁, 其他会话是否在前面的间隙中插入行无关紧要：SELECT * FROM test WHERE no = 100 FOR UPDATE;如果no是非唯一索引，则该语句会锁定搜索遇到的索引的间隙 这里还值得注意的是，不同的事务可以在间隙上持有冲突的锁。例如，事务 A 可以在一个间隙上持有一个共享间隙锁(S,GAP), 而事务 B 在同一个间隙上持有一个排他性间隙锁(X,GAP). 允许冲突间隙锁的原因是，如果从索引中清除记录，则必须合并不同事务在记录上持有的间隙锁。InnoDB间隙锁定是"纯粹的抑制性"，这意味着它们的唯一目的是防止其他事务插入到间隙中. 一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁. 既然都是为了阻止数据插入, 间隙锁之间冲突就失去了意义. 共享和独占间隙锁之间没有区别. 它们彼此不冲突, 并且执行相同的功能。 如果将事务隔离级别更改为 READ COMMITTED，可以显式禁用间隙锁定)。在这种情况下，间隙锁定对搜索和索引扫描禁用，仅用于外键约束检查和重复键检查。

6.Record Locks, 索引记录锁 LOCK_MODE分别是: S,REC_NOT_GAP或X,REC_NOT_GAP部分人称其为行锁, 其实该类锁其实锁定的是索引记录. 但如果锁定的是主键索引记录, 那么就是真正的行锁, 因为主键索引记录存放了对应的行记录. 所谓的"锁定某个行"或"在某个行上设置锁", 其实就是在某个索引的特定索引记录(或称索引条目/索引项/索引入口)上设置锁. 同样有Shard或Exclusive两种模式 行锁就是索引记录锁, 索引记录锁总是锁定索引记录, 即使表上并未定义索引. 创建表未定义索引时, InnoDB自动创建隐藏的聚集索引(索引名字是GEN_CLUST_INDEX)，使用该索引执行索引记录锁

7.Next-Key Locks, 下一键锁 LOCK_MODE分别是: S或XNext-Key Lock 是 (索引记录上的索引记录锁) + (该索引记录前面的间隙上的锁) 二者的合体，它锁定索引记录以及该索引记录前的间隙. 有Shard或Exclusive两种模式 Record Lock是单个行记录上的锁。 Gap Lock是锁定一个范围，但不包括记录本身 Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的范围查询，都是采用该方法，主要目的是解决幻读的问题, InnoDB搜索或扫描索引时，在其遇上的索引记录上设置锁, 这个特性在主键唯一索引和非主键唯一索引上表现不一致. ref: https://segmentfault.com/a/1190000040129156 更多情况下的N-K Locks 例覆盖索引的情况

8.Insert Intention Locks, 插入意向锁 LOCK_MODE分别是: S,GAP,INSERT_INTENTION或X,GAP,INSERT_INTENTION一种特殊的GAP Lock 在插入/更新之前, InnoDB会首先在索引记录之间的间隙上设置Insert Intention Lock，该锁的范围是(插入值, 向下的一个索引值). 有Shard或Exclusive两种模式 插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，亦即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待 对于Insert而言如果没有堵塞(插入意向锁和GAP Lock堵塞), 那么始终为隐式锁. 注意这里我们看到了隐式锁，隐式锁不会占用row的结构体，因此在show engine innodb status里面是看不到的，除非有其他事务显示将其转换为显式锁, ref: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b4e988a4cc78eeb0f9901f0e8144201c1bbcf2f4756976ac84159bbb3c61e9b108c76bec6&idx=1&mid=2653934834&scene=21&sn=edcac29d5cdf290281357649c2983caa#wechat_redirect

1. GAP Lock会阻塞Insert Intention Lock
2. GAP Lock相互不会阻塞
3. Insert Intention Lock相互不会阻塞
4. Insert Intention Lock不会阻塞GAP Lock

存在一个情况, 当会话A开启事务, 会话B开启事务, 会话C开启事务, 会话A插入主键为100的记录, 然后会话B也插入主键为100的记录, 会话C也插入主键为100的记录, 这时会话B/C提示主键冲突, 插入失败, 然而会话B/C仍会对主键100的记录持有索引记录读锁S,REC_NOT_GAP, 此时会话B/C都尝试获取主键100的写锁(select * from user where id = 9999 for update;)X,REC_NOT_GAP, 就会提示死锁

9.AUTO-INC Locks, 自增锁 表锁. 向带有AUTO_INCREMENT列 的表时插入数据行时，事务需要首先获取到该表的AUTO-INC表级锁，以便可以生成连续的自增值。插入语句开始时请求该锁，插入语句结束后释放该锁(注意：是语句结束后，而不是事务结束后). 所以当事务回滚时, 自增id会出现不连续记录. 语句结束后释放锁而不是事务结束后也是出于并发性的考虑 https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode InnoDB AUTO_INCREMENT 锁定模式: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes

10.Predicate Locks for Spatial Indexes, 空间索引锁 用的较少, 直接放段官方文档原文InnoDB supports SPATIAL indexing of columns containing spatial data (see Section 11.4.9, “Optimizing Spatial Analysis”). To handle locking for operations involving SPATIAL indexes, next-key locking does not work well to support REPEATABLE READ or SERIALIZABLE transaction isolation levels. There is no absolute ordering concept in multidimensional data, so it is not clear which is the “next” key. To enable support of isolation levels for tables with SPATIAL indexes, InnoDB uses predicate locks. A SPATIAL index contains minimum bounding rectangle (MBR) values, so InnoDB enforces consistent read on the index by setting a predicate lock on the MBR value used for a query. Other transactions cannot insert or modify a row that would match the query condition.

示例表

CREATE TABLE `user`  
(
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `age` int(0) NOT NULL,
  `value` int(0) NOT NULL,
  `uni` int(0) NOT NULL,
  `left` int(0) NOT NULL,
  `right` int(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uni`(`uni`) USING BTREE,
  INDEX `value`(`value`) USING BTREE,
  UNIQUE INDEX `uni_idx`(`left`, `right`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`, `left`, `right`) VALUES (440, 'Ed Venture', 57, 50, 76, 1, 2);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`, `left`, `right`) VALUES (514, 'Justin Casey Howells', 77, 17, 32, 5, 6);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`, `left`, `right`) VALUES (626, 'Dee Kay', 18, 3, 60, 5, 4);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`, `left`, `right`) VALUES (839, 'Bjorn Free', 75, 61, 80, 7, 8);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`, `left`, `right`) VALUES (880, 'Barb Dwyer', 70, 42, 52, 9, 10);

performance_schema.data_locks 可以查看加锁信息

data_lock需要重点关注的字段

1. ENGINE: 持有或请求锁的存储引擎
2. OBJECT_SCHEMA: 库名称
3. OBJECT_NAME: 表名
4. INDEX_NAME: 被锁的索引名称
5. LOCK_TYPE: TABLE/RECORD, 锁的是行还是表
6. LOCK_MODE: 锁模式, 上文中有对应
7. LOCK_DATA: 锁住的记录
8. OBJECT_INSTANCE_BEGIN: 锁在内存中的地址

什么SQL使用什么类型的锁

tips: 分析加什么锁之前要查看执行计划, 数据量过少时, mysql可能不使用索引而直接走全表扫描. 且由于MySQL会对锁的粒度做一定优化, 所以应以实际加锁为准.

1. 不使用索引的等值查询 start transaction; select * from user where age = 11 for update; 可以看到data_locks中的记录, 将表中的每一行数据都加上了Next-Key Lock, 加在了主键索引上, 还加了user表的IX锁, 还有一行LOCK_DATA为supremum pseudo-record, 当表中数据较多时, LOCK_DATA为supremum pseudo-record伪记录锁也会出现多个, 因为锁数据在内存中分成了多个页, 且LOCK_DATA可能为null, 因为被锁记录在保持锁的时候被写入了磁盘, ref: https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html N-K锁锁住当前记录和当前记录之前的索引间的空隙, 那么最小索引记录1之前, 即为"supremum"伪记录, 另一边锁住最大值上方的间隙, 对于最后一个间隔，next-key lock 锁定索引中最大值上方的间隙，并且“ supremum ” 伪记录的值高于索引中的任何实际值。上界不是真正的索引记录，因此，实际上，这个下一个键锁只锁定最大索引值之后的间隙. 在这种情况下, 记录本身和记录间的空隙都被锁住, 这是毫无疑问的"表"锁. 此时对该表进行更新/插入/删除/加锁都会被阻塞 由于age上并没有索引, 所以select * 和 select age 就没有区别 不使用索引的等值查询, 值不存在 start transaction; select * from user where age = 1000 for update; 加锁与1一致

2. 不使用索引的范围查询 start transaction; select * from user where age > 50 for update; 加锁与1一致

3. 使用普通非聚簇索引的等值查询

start transaction;
select * from user where value = 42 for update;

首先是表上的IX锁

然后锁住(17, 42)的间隙和value=42的索引记录, 升级为N-K锁

然后还锁住了value (42, 50)的间隙

最后是value=42对应的主键索引记录的行锁

以下是会被锁阻塞的操作

因为(17, 42) (42, 50)的间隙都被加了锁, 所以不能往这两个区间内新增记录

开启会话2, 这条sql会被阻塞

INSERT INTO `user`(`name`, `age`, `value`, `uni`) VALUES ('test', 70, 19, 6832897788165652);

如果value正好处于左开区间上, 且指定主键值呢? 这条同样会被阻塞. 二级索引如果值相同, 则主键值更小的, 排列在前面. 间隙锁锁住了左区间索引值上的叶子节点, 也就是存储主键id的数据. 如果能插入数据, 则插入后, 索引顺序是这样的: 3,626, 17,514, 17,515,42,880, 但因为17,514的next指针已经被锁(间隙被锁), 所以这条sql会被阻塞

INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (515, 'test', 70, 17, 6832897788165652);

但这条不会, 如果能插入数据, 则插入后, 索引顺序是这样的: 3,626, 17,513, 17,514,42,880, 新记录索引处于17,514之前, 所以不会被阻塞

INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (513, 'test', 70, 17, 6832897788165652);

这条也会被阻塞

update user set value = 18 where id = 514

但这条sql不会被阻塞, 因为value=3叶子节点的next指针没有被锁

update user set value = 14 where id = 514

但value值只能减小, 且减小后不能再增大, 执行了上面的sql后, 下面这条sql被阻塞(仅当value=17只存在一条记录的情况, 如果value=17存在两条记录, 那么减小其中一个value后, 间隙锁范围仍然是(17, 42))

update user set value = 16 where id = 514

因为间隙锁锁住的是索引记录中的空隙, 间隙并没有确切的前后区间, innodb将新值插入索引记录时, 需要维护对应字段的索引记录, 即需要获取到对应的插入意向锁, 新值并不处于会话1的GAP范围内, 所以value=17可以被更新为value=14. 间隙锁的区间被扩大为(14, 42), 所以会话2中, value减小后不能增大.

这条sql会被阻塞, 新值处于间隙锁范围内

update user set value = 49 where id = 440

与之前情况类似, id = 440的value只能增加, 不能减小, 原因也是刚刚提到的插入意向锁与间隙冲突,

更新其他字段不会阻塞, 因为会话1并没有对id=514这条索引记录加锁

update user set age = 18 where id = 514

但更新主键, 将主键增加会被阻塞, 如果能插入数据, 则插入后, 索引顺序是这样的: 3,626, 17,514, 17,1000,42,880, 但实际上在获取value=42的插入意向锁时被阻塞了, 原因也是17,514叶子节点上的next指针已经被锁住了

update user set id = 1000 where id = 514

将主键减小不会被阻塞, 如果能插入数据, 则插入后, 索引顺序是这样的: 3,626, 17,513, 17,514,42,880, 那么这条update语句在获取插入意向锁时, 不会与在17,514上的next指针锁冲突

update user set id = 513 where id = 514

这样也不会被阻塞, 因为value=3的next指针没有被锁, 就能够获取到(3, 17)的插入意向锁

update user set id = 1000, value = 16 where id = 514

使用共享锁

start transaction;
select * from user where value = 42 for share;

加锁方式与排它锁一致, 只是X变为了S

如果是下面这种情况, 不会对主键索引记录加锁, 索引记录中存储了主键id

start transaction;
select id, value from user where value = 42 for share;

当值不存在时

start transaction;
select * from user where value = 30 for update;

(17, 42)间的间隙被锁, 应该没什么疑问

总结下, 对于使用普通非聚簇索引的等值查询, 需要锁住该记录前后的空隙才能保证不出现幻行. 锁住对应的索引记录, 也是为了满足可重复读. 也就是, 当等值查询时, InnoDB会对值前后的间隙加锁, 如果存在记录, 则加上记录锁

4.使用普通非聚簇索引的范围查询

a) 左右都是开区间, 且左右范围不存在记录

start transaction;
select * from user where value > 10 and value < 30 for update;

首先, 加锁规则是, 当查询使用普通非聚集索引且为范围查询时, InnoDB会对扫描到的索引记录加上N-K锁

那如何定义这个扫描呢, 官方文档如下

SELECT * FROM child WHERE id > 100 FOR UPDATE; The query scans the index starting from the first record where id is bigger than 100. 翻译: 该查询从 id 大于 100 的第一条记录开始扫描索引 ref: https://dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html

对应上面的sql, 查询从满足条件的第一条记录开始, 即17开始加锁; 判断当前节点满足查询条件, 继续扫描; 然后又遇到了42, 继续加锁, 判断当前节点已经不满足条件了, 所以扫描到此为止. 我们对于42的加锁可能有些疑惑, InnoDB扫描到17时, 并不知道下一条记录是否符合< 30的条件, 所以只能继续扫描并加锁, 但扫描到42时, 就可以明确42之后的索引记录一定不符合条件, 所以扫描到此结束. 查询范围中存在一条符合条件的记录且因为是select * 要进行回表查询, 所以为其加上主键索引记录锁

整理一下就是

查询data_locks, 符合

b) 左右都是开区间, 左范围存在记录

start transaction;
select * from user where value > 17 and value < 30 for update;

对于这条sql, 查询是从42开始, 也是42结束, 且区间内不存在记录, 所以加锁为

查询data_locks, 符合

加锁规则就是这样, 下面练习一下

c) 左右都是开区间, 右范围存在记录

start transaction;
select * from user where value > 10 and value < 42 for update;

对于这条sql, 查询是从17开始, 也是42结束, 且区间内存在记录, 所以加锁与a)一致

d) 左右都是开区间, 左右范围都存在记录

start transaction;
select * from user where value > 17 and value < 42 for update;

对于这条sql, 查询是从42开始, 也是42结束, 且区间内不存在记录, 所以加锁与b)一致

e) 左右都是闭区间, 且左右范围不存在记录

start transaction;
select * from user where value >= 10 and value <= 30 for update;

对于这条sql, 查询是从17开始, 到42结束, 且区间内存在记录, 所以加锁与a)一致

f) 左右都是闭区间, 左范围存在记录

start transaction;
select * from user where value >= 17 and value <= 30 for update;

对于这条sql, 查询是从17开始, 到42结束, 且区间内存在记录, 所以加锁与a)一致

g) 左右都是闭区间, 右范围存在记录

start transaction;
select * from user where value >= 10 and value <= 42 for update;

对于这条sql, 查询是从17开始, 到50结束, 且区间内存在两条记录, 所以加锁如下

h) 左右都是闭区间, 左右范围都存在记录

start transaction;
select * from user where value >= 17 and value <= 42 for update;

对于这条sql, 查询是从17开始, 到50结束, 且区间内存在记录, 所以加锁与g)一致

i) 右区间不存在

start transaction;
select * from user where value >= 10000 for update;

innodb会为其加上伪记录(supremum pseudo-record) 的N-K锁

Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value. ref: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks 假设索引包含值 10, 11, 13 和 20. 该索引可能的 next-key 锁涵盖以下区间, 其中圆括号表示开区间, 方括号表示闭区间 对于最后一个间隔，next-key lock 锁定索引中最大值上方的间隙，并且"supremum"伪记录的值高于索引中的任何实际值。上界不是真正的索引记录，所以这个下一个键锁实际只锁定最大索引值之后的间隙 (negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity)

加锁如下

j) 左区间不存在

start transaction;
select * from user where value <= 17 for update;

对于这条sql, 因为没有左区间, 所以查询是从3开始, 到42结束, 且区间内存在记录, 加锁如下

N-K锁的范围为 (negative infinity, 3]

值得注意的是, 这条sql的加锁相较于上面会多一个主键的索引记录锁, LOCK_DATA为880

start transaction;
select id from user where value <= 17 for update;

个人感觉这id=880这行没必要加记录锁, 因为value=42这里已经加上了N-K锁了, 所以id是不能被其他会话更新的, 且只select id, 更新其它字段也可以满足RR隔离级别. 目前不清楚这是BUG还是一个优化方案, 待大佬解答

5.使用非聚簇唯一索引的等值查询

a) 记录存在

start transaction;
SELECT * FROM user WHERE uni = 52 FOR UPDATE;

通过data_locks可以看到, InnoDB对uni=52的索引记录, 还有id=880这行加锁. 相对于使用普通非聚簇索引的查询来说, 没有出现间隙锁.

start transaction;
SELECT id, uni FROM user WHERE uni = 52 FOR SHARE;

使用写锁/共享锁, 没有对主键索引记录加锁.

b) 记录不存在

start transaction;
SELECT * FROM user WHERE uni = 55 FOR UPDATE;

对uni=60前的间隙(52, 60)加了锁, 间隙锁的表现与使用普通非聚集索引的一致

总结下, 对于使用唯一非聚簇索引的等值查询, 分两种情况: 当值存在, 只需要锁住记录本身就可以保证可重复读, 因为存在唯一约束, 所以不需要对记录前后的记录进行间隙锁定; 当值不存在, 需要锁住该记录前后的空隙才能保证不出现幻行

6.使用非聚簇唯一索引的范围查询

a) 左右都是开区间, 且左右范围不存在记录

start transaction;
select * from user where uni > 50 and uni < 55 for update;

对于唯一索引来说, 范围查询同样需要锁定记录间的间隙才能保证可重复读, 不出现幻行

所以加锁规则与普通索引的范围查询一致, 从符合条件的52开始加N-K锁, 加锁到60结束, 同时对id=880的主键索引记录加锁

b) 左闭右开, 左范围存在记录

start transaction;
select * from user where uni >= 52 and uni < 55 for update;

加锁与a)一致

c) 左开右闭, 右范围存在记录

start transaction;
select * from user where uni > 50 and uni <= 52 for update;

加锁与a)一致, 这里为什么会对60这个记录加锁? uni是唯一索引, 那么52之后必定是比52大的索引记录, 即扫描可以到此为止, 不需要再对60加锁. 有人认为这是InnoDB的一个BUG, 后面介绍主键索引范围查询加锁情况的时候再提.

其他区间情况大家可以自己试一试

总结, 唯一非聚簇索引范围查询加锁与普通非聚簇索引加锁表现一致

7.使用非聚簇唯一联合索引的等值查询

a) 符合最左前缀的条件, 值存在

start transaction;
SELECT * FROM user WHERE `left` = 5 FOR UPDATE;

前面介绍间隙锁概念时有提到, 当查询条件使用联合唯一索引的一部分列时, 仍会发生间隙锁定.

通过查看data_locks的数据也可以看到, InnoDB对5, 4, 626, 5, 6, 514, 7, 8, 839加上了间隙锁. 该查询有两条符合条件的数据, 5, 4, 626, 5, 6, 514升级为了N-K锁. 同时对应的主键记录也加了索引记录锁

b) 符合最左前缀的条件, 值不存在

start transaction;
SELECT * FROM user WHERE `left` = 3 FOR UPDATE;

只加了个间隙锁, lock_data为5, 4, 626, 表现与非聚簇唯一索引是一致的

c) 使用联合索引的所有列, 值存在

start transaction;
SELECT * FROM user WHERE `left` = 5 AND `right` = 6 FOR UPDATE;

只对5, 6这行加锁, 无需间隙锁

d) 使用联合索引的所有列, 值不存在

start transaction;
SELECT * FROM user WHERE `left` = 5 AND `right` = 5 FOR UPDATE;

加了间隙锁, lock_data为5, 6, 626, 在这个联合索引结构中, 5, 4在5, 6之前, 所以间隙锁的范围也是5, 4和5, 6之间

e) 不符合最左前缀的条件

start transaction;
SELECT * FROM user WHERE `right` = 6 FOR UPDATE;

由于不符合最左前缀, InnoDB只能进行全表扫描, 所以对所有主键记录都加上了N-K锁. 不论查询条件的值是否存在

使用非聚簇联合唯一索引的等值查询, 1. 当查询条件为组成联合索引的所有列时, 值存在, 加记录锁; 值不存在, 加间隙锁. 与普通非聚簇唯一索引是一致的. 2. 当查询条件只使用部分列但符合最左前缀时, 仍会发生间隙锁定, 不论值是否存在. 3. 当查询条件只使用部分列且不符合最左前缀时, 全表扫描加锁

8.使用非聚簇唯一联合索引的范围查询

a) 符合最左前缀

BEGIN;
SELECT * FROM user WHERE `left` > 1 AND `left` < 7 FOR UPDATE;

走索引的扫描都是加N-K锁, 所以LOCK_DATA为5,4, 5,6, 7,8, 还有俩主键索引记录锁

b) 使用联合索引的所有列

BEGIN;
SELECT * FROM user WHERE `left` > 1 AND `right` > 2 AND `left` < 7 AND `right` < 8 FOR UPDATE;

同a)一致

c) 不符合最左前缀规则

BEGIN;
SELECT * FROM user WHERE `right` > 2 AND `right` < 8 FOR UPDATE;

无法使用索引, 所以进行全表扫描, 所有记录加锁

加锁规则与普通非聚簇唯一索引一致

9.使用聚簇索引的等值查询

聚簇索引指主键

a) 值存在

BEGIN;
SELECT * FROM user WHERE id = 514 FOR UPDATE;

主键记录加锁.

b) 值不存在

BEGIN;
SELECT * FROM user WHERE id = 600 FOR UPDATE;

锁定了(514, 626)的间隙

加锁规则同唯一索引差不多, 只是这里只会对主键加锁

10.使用聚簇索引的范围查询

范围查询扫描都是加N-K锁不再赘述. 这里主要展示一下与唯一索引范围查询加锁的不同

一下是类似 6. c) 的一个sql, 左值不存在, 右值存在

start transaction;
select * from user where id > 600 and id <= 626 for update;

如果是普通唯一索引, 锁一直会加到626之后的839, 但此处只加了626的N-K锁.

看一段官方文档

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html#mysqld-8-0-18-bug

InnoDB: An unnecessary next key lock was taken when performing a SELECT...FOR [SHARE|UPDATE\] query with a WHERE condition that specifies a range, causing one too many rows to be locked. The most common occurrences of this issue have been addressed so that only rows and gaps that intersect the searched range are locked. (Bug #29508068)

这里官方说已解决此问题最常见的情况, 意思是只处理了关于主键的多余加锁BUG?

11.使用聚簇联合索引的等值查询

加锁与非聚簇联合唯一索引一致

12.使用聚簇联合索引的范围查询

与非聚簇唯一索引范围查询加锁有一点不同, 那就是主键的范围查询不会进行多余加锁

13.混合使用多个索引的查询

a) 主键和普通索引, AND条件

BEGIN;
SELECT * FROM user WHERE id = 5 AND value = 55 FOR UPDATE;

加了440的间隙锁

BEGIN;
SELECT * FROM user WHERE id < 500 AND value > 20 FOR UPDATE;

加锁与下面的sql是一致的

BEGIN;
SELECT * FROM user WHERE id < 500 FOR UPDATE;

通过查看执行计划, 可以看到以下sql是使用主键id作为过滤条件的

EXPLAIN SELECT * FROM user WHERE id < 500 AND value > 20

所以加锁与只使用id为条件的sql是一样的

b) 主键和普通索引, OR条件

BEGIN;
SELECT * FROM user WHERE id = 5 OR value = 55 FOR UPDATE;

该查询使用id和value两个索引, 所以加了440的主键间隙锁和61的索引间隙锁.

有人问hash索引是怎么处理加锁的. 答: InnoDB并不支持hash索引

若有谬误, 欢迎指正

ref

MySQL8 文档 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁 https://blog.csdn.net/iceman1952/article/details/85504278 什么是间隙锁 https://blog.csdn.net/qq_21729419/article/details/113643359 MySQL8更新日志 https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-25.html