mysql锁场景_MySQL死锁系列-常见加锁场景分析

在上一篇文章《锁的类型以及加锁原理》主要总结了 MySQL 锁的类型和模式以及基本的加锁原理，今天咱们就从原理走向实战，分析常见 SQL 语句的加锁场景。了解了这几种场景，相信小伙伴们也能触类旁通，灵活地分析真实开发过程当中遇到的加锁问题。数据库

以下图所示，数据库的隔离等级，SQL 语句和当前数据库数据会共同影响该条 SQL 执行时数据库生成的锁模式，锁类型和锁数量。并发

下面，咱们会首先讲解一下隔离等级、不一样 SQL 语句 和 当前数据库数据对生成锁影响的基本规则，而后再依次具体 SQL 的加锁场景。工具

隔离等级对加锁的影响

MySQL 的隔离等级对加锁有影响，因此在分析具体加锁场景时，首先要肯定当前的隔离等级。.net

读未提交(Read Uncommitted 后续简称 RU)：能够读到未提交的读，基本上不会使用该隔离等级，因此暂时忽略。

读已提交(Read Committed 后续简称 RC)：存在幻读问题，对当前读获取的数据加记录锁。

可重复读(Repeatable Read 后续简称 RR)：不存在幻读问题，对当前读获取的数据加记录锁，同时对涉及的范围加间隙锁，防止新的数据插入，致使幻读。

序列化(Serializable)：从 MVCC 并发控制退化到基于锁的并发控制，不存在快照读，都是当前读，并发效率急剧降低，不建议使用。

这里说明一下，RC 老是读取记录的最新版本，而 RR 是读取该记录事务开始时的那个版本，虽然这两种读取的版本不一样，可是都是快照数据，并不会被写操做阻塞，因此这种读操做称为 快照读(Snapshot Read)线程

MySQL 还提供了另外一种读取方式叫当前读(Current Read)，它读的再也不是数据的快照版本，而是数据的最新版本，并会对数据加锁，根据语句和加锁的不一样，又分红三种状况：日志

SELECT ... LOCK IN SHARE MODE：加共享(S)锁

SELECT ... FOR UPDATE：加排他(X)锁

INSERT / UPDATE / DELETE：加排他(X)锁

当前读在 RR 和 RC 两种隔离级别下的实现也是不同的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题。blog

不一样 SQL 语句对加锁的影响

不一样的 SQL 语句固然会加不一样的锁，总结起来主要分为五种状况：索引

SELECT ... 语句正常状况下为快照读，不加锁；

SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁；

SELECT ... FOR UPDATE 语句为当前读，加 X 锁；

常见的 DML 语句(如 INSERT、DELETE、UPDATE)为当前读，加 X 锁；

常见的 DDL 语句(如 ALTER、CREATE 等)加表级锁，且这些语句为隐式提交，不能回滚。

其中，当前读的 SQL 语句的 where 从句的不一样也会影响加锁，包括是否使用索引，索引是不是惟一索引等等。事务

当前数据对加锁的影响

SQL 语句执行时数据库中的数据也会对加锁产生影响。开发

好比一条最简单的根据主键进行更新的 SQL 语句，若是主键存在，则只须要对其加记录锁，若是不存在，则须要在加间隙锁。

至于其余非惟一性索引更新或者插入时的加锁也都不一样程度的受到现存数据的影响，后续咱们会一一说明。

具体场景分析

具体 SQL 场景分析主要借鉴何登成前辈的《MySQL 加锁处理分析》文章和 aneasystone 的系列文章，在他们的基础上进行了总结和整理。

咱们使用下面这张 book 表做为实例，其中 id 为主键，ISBN(书号)为二级惟一索引，Author(做者)为二级非惟一索引，score(评分)无索引。

UPDATE 语句加锁分析

下面，咱们先来分析 UPDATE 相关 SQL 在使用较为简单 where 从句状况下加锁状况。其中的分析原则也适用于 UPDATE,DELETE 和 SELECT ... FOR UPDATE等当前读的语句。

聚簇索引，查询命中

聚簇索引就是 InnoDB 存储引擎下的主键索引，具体可参考《MySQL索引》。

下图展现了使用 UPDATE book SET score = 9.2 WHERE ID = 10 语句命中的状况下在 RC 和 RR 隔离等级下的加锁，两种隔离等级下没有任何区别，都是对 ID = 10 这个索引加排他记录锁。

聚簇索引，查询未命中

下图展现了 UPDATE book SET score = 9.2 WHERE ID = 16 语句未命中时 RR 隔离级别下的加锁状况。

在 RC 隔离等级下，不须要加锁；而在 RR 隔离级别会在 ID = 16 先后两个索引之间加上间隙锁。

值得注意的是，间隙锁和间隙锁之间是互不冲突的，间隙锁惟一的做用就是为了防止其余事务的插入新行，致使幻读，因此加间隙 S 锁和加间隙 X 锁没有任何区别。

二级惟一索引，查询命中

下图展现了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0003' 在 RC 和 RR 隔离等级下命中时的加锁状况。

在 InnoDB 存储引擎中，二级索引的叶子节点保存着主键索引的值，而后再拿主键索引去获取真正的数据行，因此在这种状况下，二级索引和主键索引都会加排他记录锁。

二级惟一索引，查询未命中

下图展现了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0008' 语句在 RR 隔离等级下未命中时的加锁状况，RC 隔离等级下该语句未命中不会加锁。

由于 N0008 大于 N0007，因此要锁住 (N0007,正无穷)这段区间，而 InnoDB 的索引通常都使用 Suprenum Record 和 Infimum Record 来分别表示记录的上下边界。Infimum 是比该页中任何记录都要小的值，而 Supremum 比该页中最大的记录值还要大，这两条记录在建立页的时候就有了，而且不会删除。

因此，在 N0007 和 Suprenum Record 之间加了间隙锁。

为何不在主键上也加 GAP 锁呢？欢迎留言说出你的想法。

二级非惟一索引，查询命中

下图展现了 UPDATE book SET score = 9.2 WHERE Author = 'Tom' 语句在 RC 隔离等级下命中时的加锁状况。

咱们能够看到，在 RC 等级下，二级惟一索引和二级非惟一索引的加锁状况是一致的，都是在涉及的二级索引和对应的主键索引上加上排他记录锁。

可是在 RR 隔离等级下，加锁的状况产生了变化，它不只对涉及的二级索引和主键索引加了排他记录锁，还在非惟一二级索引上加了三个间隙锁，锁住了两个 Tom 索引值相关的三个范围。

那为何惟一索引不须要加间隙锁呢？间隙锁的做用是为了解决幻读，防止其余事务插入相同索引值的记录，而惟一索引和主键约束都已经保证了该索引值确定只有一条记录，因此无需加间隙锁。

须要注意的是，上图虽然画着 4 个记录锁，三个间隙锁，可是实际上间隙锁和它右侧的记录锁会合并成 Next-Key 锁。

因此实际状况有两个 Next-Key 锁，一个间隙锁(Tom60,正无穷)和两个记录锁。

二级非惟一索引，查询未命中

下图展现了 UPDATE book SET score = 9.2 WHERE Author = 'Sarah' 在 RR 隔离等级下未命中的加锁状况，它会在二级索引 Rose 和 Tom 之间加间隙锁。而 RC 隔离等级下不须要加锁。

无索引

当 Where 从句的条件并不使用索引时，则会对全表进行扫描，在 RC 隔离等级下对全部的数据加排他记录锁。在RR 隔离等级下，除了给记录加锁，还会对记录和记录之间加间隙锁。和上边同样，间隙锁会和左侧的记录锁合并成 Next-Key 锁。

下图就是 UPDATE book SET score = 9.2 WHERE score = 22 语句在两种隔离等级下的加锁状况。

聚簇索引，范围查询

上面介绍的场景都是 where 从句的等值查询，而范围查询的加锁又是怎么样的呢？咱们慢慢来看。

下图是 UPDATE book SET score = 9.2 WHERE ID <= 25 在 RC 和 RR 隔离等级下的加锁状况。

RC 场景下与等值查询相似，只会在涉及的 ID = 10，ID = 18 和 ID = 25 索引上加排他记录锁。

而在 RR 隔离等级下则有所不一样，它会加上间隙锁，和对应的记录锁合并称为 Next-Key 锁。除此以外，它还会在(25, 30] 上分别加 Next-Key 锁。这一点是十分特殊的，具体缘由还须要再探究。

二级索引，范围查询

下图展现了 UPDATE book SET ISBN = N0001 WHERE score <= 7.9 在 RR 级别下的加锁状况。

修改索引值

UPDATE 语句修改索引值的状况能够分开分析，首先 Where 从句的加锁分析如上文所述，多了一步 Set 部分的加锁。

下图展现了 UPDATE book SET Author = 'John' WHERE ID = 10 在 RC 和 RR 隔离等级下的加锁状况。除了在主键 ID 上进行加锁，还会对二级索引上的 Bob(就值) 和 John(新值) 上进行加锁。

DELETE 语句加锁分析

通常来讲，DELETE 的加锁和 SELECT FOR UPDATE 或 UPDATE 并无太大的差别。

由于，在 MySQL 数据库中，执行 DELETE 语句其实并无直接删除记录，而是在记录上打上一个删除标记，而后经过后台的一个叫作 purge 的线程来清理。从这一点来看，DELETE 和 UPDATE 确实是很是相像。事实上，DELETE 和 UPDATE 的加锁也几乎是同样的。

INSERT 语句加锁分析

接下来，咱们来看一下 Insert 语句的加锁状况。

Insert 语句在两种状况下会加锁：

为了防止幻读，若是记录之间加有间隙锁，此时不能 Insert；

若是 Insert 的记录和已有记录形成惟一键冲突，此时不能 Insert；

除了上述状况，Insert 语句的锁都是隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁的机制来减小加锁的数量。

隐式锁的特色是只有在可能发生冲突时才加锁，减小了锁的数量。另外，隐式锁是针对被修改的 B+Tree 记录，所以都是记录类型的锁，不多是间隙锁或 Next-Key 类型。

具体 Insert 语句的加锁流程以下：

首先对插入的间隙加插入意向锁(Insert Intension Locks)

若是该间隙已被加上了间隙锁或 Next-Key 锁，则加锁失败进入等待；

若是没有，则加锁成功，表示能够插入；

而后判断插入记录是否有惟一键，若是有，则进行惟一性约束检查

若是不存在相同键值，则完成插入

若是存在相同键值，则判断该键值是否加锁

若是没有锁， 判断该记录是否被标记为删除

若是标记为删除，说明事务已经提交，还没来得及 purge，这时加 S 锁等待；

若是没有标记删除，则报 duplicate key 错误；

若是有锁，说明该记录正在处理(新增、删除或更新)，且事务还未提交，加 S 锁等待；

插入记录并对记录加 X 记录锁；

后记

本文中讲解的 SQL 语句都是十分简单的，当 SQL 语句包含多个查询条件时，加锁的分析过程就每每更加复杂。咱们须要使用 MySQL 相关的工具进行分析，而且有时甚至须要查询 MySQL 相关的日志信息来了解到底语句加了什么锁或者为何产生死锁，下篇文章中咱们就主要了解一下这些内容，请你们持续关注。