解决死锁之路-常见SQL语句的加锁分析
摘抄⾃：www.aneasystone/archives/2017/12/solving-dead-locks-three.html
解决死锁之路 - 常见 SQL 语句的加锁分析
这篇博客将对⼀些常见的 SQL 语句进⾏加锁分析，看看我们平时执⾏的那些 SQL 都会加什么锁。只有对我们所写的 SQL 语句加锁过程了如指掌，才能在遇到死锁问题时倒推出是什么锁导致的问题。在前⾯的博客中我们已经学习了 MySQL 下不同的锁模式和锁类型，我们要特别注意它们的兼容矩阵，熟悉哪些锁是不兼容的，这些不兼容的锁往往就是导致死锁的罪魁祸⾸。总体来说，MySQL 中的锁可以分成两个粒度：表锁和⾏锁，表锁有：表级读锁，表级写锁，读意向锁，写意向锁，⾃增锁；⾏锁有：读记录锁，写记录锁，间隙锁，Next-key 锁，插⼊意向锁。不出意外，绝⼤多数的死锁问题都是由这些锁之间的冲突导致的。
我们知道，不同的隔离级别加锁也是不⼀样的，譬如 RR 隔离级别下有间隙锁和 Next-key 锁，这在 RC 隔离级别下是没有的（也有例外），所以在对 SQL 进⾏加锁分析时，必须得知道数据库的隔离级别。由于 RR 和 RC ⽤的⽐较多，所以这篇博客只对这两种隔离级别做分析。
这是《解决死锁之路》系列博⽂中的⼀篇，你还可以阅读其他⼏篇：
1.
2.
3. 掌握常见 SQL 语句的加锁分析
4.
⼀、基本的加锁规则
虽然 MySQL 的锁各式各样，但是有些基本的加锁原则是保持不变的，譬如：快照读是不加锁的，更新语句肯定是加排它锁的，RC 隔离级别是没有间隙锁的等等。这些规则整理如下，后⾯就不再重复介绍了：
常见语句的加锁
SELECT ... 语句正常情况下为快照读，不加锁；
SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁；
SELECT ... FOR UPDATE 语句为当前读，加 X 锁；
常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；
常见的 DDL 语句（如 ALTER、CREATE 等）加表级锁，且这些语句为隐式提交，不能回滚；
表锁
表锁（分 S 锁和 X 锁）
意向锁（分 IS 锁和 IX 锁）
⾃增锁（⼀般见不到，只有在 innodb_autoinc_lock_mode = 0 或者 Bulk inserts 时才可能有）
⾏锁
记录锁（分 S 锁和 X 锁）
间隙锁（分 S 锁和 X 锁）
Next-key 锁（分 S 锁和 X 锁）
插⼊意向锁
⾏锁分析
⾏锁都是加在索引上的，最终都会落在聚簇索引上；
加⾏锁的过程是⼀条⼀条记录加的；
锁冲突
S 锁和 S 锁兼容，X 锁和 X 锁冲突，X 锁和 S 锁冲突；
表锁和⾏锁的冲突矩阵参见前⾯的博客；
不同隔离级别下的锁
上⾯说 SELECT ... 语句正常情况下为快照读，不加锁；但是在 Serializable 隔离级别下为当前读，加 S 锁；
RC 隔离级别下没有间隙锁和 Next-key 锁（特殊情况下也会有：purge + unique key）；
不同隔离级别下锁的区别，参见前⾯的博客；
⼆、简单 SQL 的加锁分析
sql语句查询不包含何登成前辈在他的博客《MySQL 加锁处理分析》中对⼀些常见的 SQL 加锁进⾏了细致的分析，这篇博客可以说是⽹上介绍 MySQL 加锁分析的⼀个范本，⽹上⼏乎所有关于加锁分析的博客都是参考了这篇博客，勘称经典，强烈推荐。我这⾥也不例外，只是在他的基础上进⾏了⼀些整理和总结。
我们使⽤下⾯这张 students 表作为实例，其中 id 为主键，no（学号）为⼆级唯⼀索引，name（姓名）和 age（年龄）为⼆级⾮唯⼀索引，score（学分）⽆索引。
这⼀节我们只分析最简单的⼀种 SQL，它只包含⼀个 WHERE 条件，等值查询或范围查询。虽然 SQL ⾮常简单，但是针对不同类型的列，我们还是会⾯对各种情况：
聚簇索引，查询命中：UPDATE students SET score = 100 WHERE id = 15;
聚簇索引，查询未命中：UPDATE students SET score = 100 WHERE id = 16;
⼆级唯⼀索引，查询命中：UPDATE students SET score = 100 WHERE no = 'S0003';
⼆级唯⼀索引，查询未命中：UPDATE students SET score = 100 WHERE no = 'S0008';
⼆级⾮唯⼀索引，查询命中：UPDATE students SET score = 100 WHERE name = 'Tom';
⼆级⾮唯⼀索引，查询未命中：UPDATE students SET score = 100 WHERE name = 'John';
⽆索引：UPDATE students SET score = 100 WHERE score = 22;
聚簇索引，范围查询：UPDATE students SET score = 100 WHERE id <= 20;
⼆级索引，范围查询：UPDATE students SET score = 100 WHERE age <= 23;
修改索引值：UPDATE students SET name = 'John' WHERE id = 15;
2.1 聚簇索引，查询命中
语句UPDATE students SET score = 100 WHERE id = 15在 RC 和 RR 隔离级别下加锁情况⼀样，都是对 id 这个聚簇索引加 X 锁，如下：
2.2 聚簇索引，查询未命中
如果查询未命中纪录，在 RC 和 RR 隔离级别下加锁是不⼀样的，因为 RR 有 GAP 锁。语句UPDATE students SET score = 100 WHERE id = 16在 RC 和 RR 隔离级别下的加锁情况如下（RC 不加锁）：
2.3 ⼆级唯⼀索引，查询命中
语句UPDATE students SET score = 100 WHERE no = 'S0003'命中⼆级唯⼀索引，上⼀篇博客中我们介绍了索引的结构，我们知道⼆级索引的叶⼦节点中保存了主键索引的位置，在给⼆级索引加锁的时候，主键索引也会⼀并加锁。这个在 RC 和 RR 两种隔离级别下没有区别：
那么，为什么主键索引上的记录也要加锁呢？因为有可能其他事务会根据主键对 students 表进⾏更新，如：UPDATE students SET score = 100 WHERE id = 20，试想⼀下，如果主键索引没有加锁，那么显然会存在并发问题。
2.4 ⼆级唯⼀索引，查询未命中
如果查询未命中纪录，和 2.2 情况⼀样，RR 隔离级别会加 GAP 锁，RC ⽆锁。语句UPDATE students SET score = 100 WHERE no = 'S0008'加锁情况如下：
这种情况下只会在⼆级索引加锁，不会在聚簇索引上加锁。
2.5 ⼆级⾮唯⼀索引，查询命中
如果查询命中的是⼆级⾮唯⼀索引，在 RR 隔离级别下，还会加 GAP 锁。语句UPDATE students SET score = 100 WHERE name = 'Tom'加锁如下：
为什么⾮唯⼀索引会加 GAP 锁，⽽唯⼀索引不⽤加 GAP 锁呢？原因很简单，GAP 锁的作⽤是为了解决幻读，防⽌其他事务插⼊相同索引值的记录，⽽唯⼀索引和主键约束都已经保证了该索引值肯定只有⼀条记录，所以⽆需加 GAP 锁。
这⾥还有⼀点要注意⼀下，数⼀数右图中的锁你可能会觉得⼀共加了 7 把锁，实际情况不是，要注意的是 (Tom, 37) 上的记录锁和它前⾯的 GAP 锁合起来是⼀个 Next-key 锁，这个锁加在 (Tom, 37) 这个索引上，另外 (Tom, 49) 上也有⼀把 Next-key 锁。那么最右边的 GAP 锁加在哪呢？右边已经没有任何记录了啊。其实，在 InnoDb 存储引擎⾥，每个数据页中都会有两个虚拟的⾏记录，⽤来限定记录的边界，分别是：Infimum Record和Supremum Record，Infimum 是⽐该页中任何记录都要⼩的值，⽽ Supremum ⽐该页中最⼤的记录值还要⼤，这两条记录在创建页的时候就有了，并且不会删除。上⾯右边的 GAP 锁就是加在 Supremum Record 上。所以说，上⾯右图中共有 2 把 Next-key 锁，1 把 GAP 锁，2 把记录锁，⼀共 5 把锁。
2.6 ⼆级⾮唯⼀索引，查询未命中
如果查询未命中纪录，和 2.2、2.4 情况⼀样，RR 隔离级别会加 GAP 锁，RC ⽆锁。语句UPDATE students SET score = 100 WHERE name = 'John'加锁情况如下：
2.7 ⽆索引
如果 WHERE 条件不能⾛索引，MySQL 会如何加锁呢？有的⼈说会在表上加 X 锁，也有⼈说会根据 WHERE 条件将筛选出来的记录在聚簇索引上加上 X 锁，那么究竟如何，我们看下图：
在没有索引的时候，只能⾛聚簇索引，对表中的记录进⾏全表扫描。在 RC 隔离级别下会给所有记录加⾏锁，在 RR 隔离级别下，不仅会给所有记录加⾏锁，所有聚簇索引和聚簇索引之间还会加上 GAP 锁。
语句UPDATE students SET score = 100 WHERE score = 22满⾜条件的虽然只有 1 条记录，但是聚簇索引上所有的记录，都被加上了 X 锁。那么，为什么不是只在满⾜条件的记录上加锁呢？这是由于 MySQL 的实现决定的。如果⼀个条件⽆法通过索引快速过滤，那么存储引擎层⾯就会将所有记录加锁后返回，然后由 MySQL Server 层进⾏过滤，因此也就把所有的记录都锁上了。
不过在实际的实现中，MySQL 有⼀些改进，如果是 RC 隔离级别，在 MySQL Server 过滤条件发现不满⾜后，会调⽤ unlock_row ⽅法，把不满⾜条件的记录锁释放掉（违背了2PL 的约束）。这样做可以保证最后只会持有满⾜条件记录上的锁，但是每条记录的加锁操作还是不能省略的。如果是 RR 隔离
级别，⼀般情况下 MySQL 是不能这样优化的，除⾮设置了innodb_locks_unsafe_for_binlog参数，这时也会提前释放锁，并且不加 GAP 锁，这就是所谓的 semi-consistent read。
2.8 聚簇索引，范围查询
上⾯所介绍的各种情况其实都是⾮常常见的 SQL，它们有⼀个特点：全部都只有⼀个 WHERE 条件，并且都是等值查询。那么问题来了，如果不是等值查询⽽是范围查询，加锁情况会怎么样呢？有⼈可能会觉得这很简单，根据上⾯的加锁经验，我们只要给查询范围内的所有记录加上锁即可，如果隔离级别是 RR，所有记录之间再加上间隙锁。事实究竟如何，我们看下⾯的图：
SQL 语句为 UPDATE students SET score = 100 WHERE id <= 20，按理说我们只需要将 id = 20、18、15 三条记录锁住即可，但是看右边的图，在 RR 隔离级别下，我们还把 id = 30 这条记录以及 (20, 30] 之间的间隙也锁起来了，很显然这是⼀个 Next-key 锁。如果 WHERE 条件是 id < 20，则会把 id = 20 这条记录锁住。为什么会这样我也不清楚，⽹上搜了很久，有⼈说是为了防⽌幻读，但 id 是唯⼀主键，(20, 30] 之间是不可能再插⼊⼀条 id = 20 的，所以具体的原因还需要再分析下，如果你知道，还请不吝赐教。
所以对于范围查询，如果 WHERE 条件是 id <= N ，那么 N 后⼀条记录也会被加上 Next-key 锁；如果条件是 id < N ，那么 N 这条记录会被加上 Next-key 锁。另外，如果
WHERE 条件是 id >= N ，只会给 N 加上记录锁，以及给⽐ N ⼤的记录加锁，不会给 N 前⼀条记录加锁；如果条件是 id > N ，也不会锁前⼀条记录，连 N 这条记录都不会锁。====================== 11⽉26号补充 =========================
我在做实验的时候发现，在 RR 隔离级别，条件是 id >= 20，有时会对 id < 20 的记录加锁，有时候⼜不加，感觉不到任何规律，请以实际情况为准。我对范围查询的加锁原理还不是很明⽩，后⾯有时间再仔细研究下，也欢迎有兴趣的同学⼀起讨论下。
下⾯是我做的⼀个简单的实验，表很简单，只有⼀列主键 id ：
1
23456789
mysql> show create table t1;
+-------+--------------------------------------------+| Table | Create Table                              |+-------+--------------------------------------------+| t1    | CREATE TABLE `t1` (                        |
|      |    `id` int(11) NOT NULL AUTO_INCREMENT,  |
|      |    PRIMARY KEY (`id`)                      ||      | ) ENGINE=InnoDB DEFAULT CHARSET=utf8      |+-------+--------------------------------------------+表⾥⼀共三条数据：
1
23456789
mysql> select * from t1;
+----+| id |+----+|  2 |
|  4 |
|  6 |+----+3 rows in set (0.00 sec)执⾏ delete from t1 where id > 2 时加锁情况是：(2, 4], (4, 6], (6, +∞)
执⾏ select * from t1 where id > 2 for update  时加锁情况是：(-∞, 2], (2, 4], (4, 6], (6, +∞)
可见 select for update 和 delete 的加锁还是有所区别的，⾄于 select for update 为什么加 (-∞, 2] 这个锁，我还是百思不得其解。后来⽆意中给表 t1 加了⼀个字段 a int(11) NOT NULL ，竟然发现 select * from t1 where id > 2 for update  就不会给 (-∞, 2] 加锁了，真的⾮常奇怪。
====================== 12⽉3号补充 =========================
经过⼏天的搜索，终于到了⼀个像样的解释（但不好去证实）：当数据表中数据⾮常少时，譬如上⾯那个的例⼦，select ... [lock in share mode | for update] 语句会⾛全表扫描，这样表中所有记录都会被锁住，这就是 (-∞, 2] 被锁的原因。⽽ delete 语句并不会⾛全表扫描。
2.9 ⼆级索引，范围查询
然后我们把范围查询应⽤到⼆级⾮唯⼀索引上来，SQL 语句为：UPDATE students SET score = 100 WHERE age <= 23
，加锁情况如下图所⽰：
可以看出和聚簇索引的范围查询⼀样，除了 WHERE 条件范围内的记录加锁之外，后⾯⼀条记录也会加上 Next-key 锁，这⾥有意思的⼀点是，尽管满⾜ age = 24 的记录有两条，但只有第⼀条被加锁，第⼆条没有加锁，并且第⼀条和第⼆条之间也没有加锁。
2.10 修改索引值
这种情况⽐较容易理解，WHERE 部分的索引加锁原则和上⾯介绍的⼀样，多的是 SET 部分的加锁。譬如 UPDATE students SET name = 'John' WHERE id = 15 不仅在 id = 15 记录上加锁之外，还会在 name = 'Bob'（原值）和 name = 'John'（新值） 上加锁。⽰意图如下（<span style='color:red;'>此处理解有误，参见下⾯的评论区</span>）：
RC 和 RR 没有区别。
三、复杂条件加锁分析
前⾯的例⼦都是⾮常简单的 SQL，只包含⼀个 WHERE 条件，并且是等值查询，当 SQL 语句中包含多个条件时，对索引的分析就相当重要了。因为我们知道⾏锁最终都是加在索引上的，如果我们连执⾏ SQL 语句时会使⽤哪个索引都不知道，⼜怎么去分析这个 SQL 所加的锁呢？
MySQL 的索引是⼀个很复杂的话题，甚⾄可以写⼀本书出来了。这⾥就只是学习⼀下在对复杂 SQL
加锁分析之前如何先对索引进⾏分析。譬如下⾯这样的 SQL：
1mysql> DELETE FROM students WHERE name = 'Tom' AND age = 22;
其中 name 和 age 两个字段都是索引，那么该如何加锁？这其实取决于 MySQL ⽤哪个索引。可以⽤分析 MySQL 是如何执⾏这条 SQL 的，通过这个命令可以知道 MySQL 会使⽤哪些索引以及怎么⽤索引来执⾏ SQL 的，只有执⾏会⽤到的索引才有可能被加锁，没有使⽤的索引是不加锁的，这⾥有可以参考。也可以使⽤ MySQL 的来对 SQL 进⾏分析，它⽀持将执⾏的 SQL 的查询计划树记录下来，这个稍微有点难度，有兴趣的同学可以研究下。那么 MySQL 是如何选择合适的索引呢？其实 MySQL 会给每⼀个索引⼀个指标，叫做索引的选择性，这个值越⾼表⽰使⽤这个索引能最⼤程度的过滤更多的记录，关于这个，⼜是另⼀个话题了。
当然，从两个索引中选择⼀个索引来⽤，这种情况的加锁分析和我们上⼀节讨论的情形并没有本质的区别，只需要将那个没有⽤索引的 WHERE 条件当成普通的过滤条件就好了。这⾥我们会把⽤到的索引称为 Index Key，⽽另⼀个条件称为 Table Filter。譬如这⾥如果⽤到的索引为 age，那么 age 就是 Index Key，⽽ name = 'Tom' 就是 Table Filter。Index Key ⼜分为 First Key 和 Last Key，如果 Index Key 是范围查询的话，如下⾯的例⼦：
1mysql> DELETE FROM students WHERE name = 'Tom' AND age > 22 AND age < 25;
其中 First Key 为 age > 22，Last Key 为 age < 25。
所以我们在加锁分析时，只需要确定 Index Key 即可，锁是加在 First Key 和 Last Key 之间的记录上的，如果隔离级别为 RR，同样会有间隙锁。要注意的是，当索引为复合索引时，Index Key 可能会有多个，何登成的这篇博客《SQL中的where条件，在数据库中提取与应⽤浅析》详细介绍了如何从⼀个复杂的 WHERE 条件中提取出 Index Key，推荐⼀读。也有⼀篇博客介绍了 MySQL 是如何利⽤索引的。
当索引为复合索引时，不仅可能有多个 Index Key，⽽且还可能有 Index Filter。所谓 Index Filter，就是复合索引中除 Index Key 之外的其他可⽤于过滤的条件。如果 MySQL 是5.6 之前的版本，Index Filter 和 Table Filter 没有区别，统统将 Index First Key 与 Index Last Key 范围内的索引记录，回表读取完整记录，然后返回给 MySQL Server 层进⾏过滤。⽽在 MySQL 5.6 之后，Index Filter 与 Table Filter 分离，Index Filter 下降到 InnoDB 的索引层⾯进⾏过滤，减少了回表与返回 MySQL Server 层的记录交互开销，提⾼了SQL的执⾏效率，这就是传说中的，使⽤ Index Filter 过滤不满⾜条件的记录，⽆需加锁。
这⾥引⽤何登成前辈博客中的⼀个例⼦：
可以看到 pubtime > 1 and pubtime < 20 为 Index First Key 和 Index Last Key，MySQL 会在这个范围内加上记录锁和间隙锁；userid = 'hdc' 为 Index Filter，这个过滤条件可以在索引层⾯就可以过滤掉⼀条记录，因此如果数据库⽀持 ICP 的话，(4, yyy, 3) 这条记录就不会加锁；comment is not NULL 为 Table Filter，虽然这个条件也可以过滤⼀条记录，但是它不能在索引层⾯过滤，⽽是在根据索引读取了整条记录之后才过滤的，因此加锁并不能省略。
四、DELETE 语句加锁分析
⼀般来说，DELETE 的加锁和 SELECT FOR UPDATE 或 UPDATE 并没有太⼤的差异，DELETE 语句⼀样会有下⾯这些情况：
聚簇索引，查询命中：DELETE FROM students WHERE id = 15;
聚簇索引，查询未命中：DELETE FROM students WHERE id = 16;
⼆级唯⼀索引，查询命中：DELETE FROM students WHERE no = 'S0003';
⼆级唯⼀索引，查询未命中：DELETE FROM students WHERE no = 'S0008';
⼆级⾮唯⼀索引，查询命中：DELETE FROM students WHERE name = 'Tom';
⼆级⾮唯⼀索引，查询未命中：DELETE FROM students WHERE name = 'John';
⽆索引：DELETE FROM students WHERE score = 22;
聚簇索引，范围查询：DELETE FROM students WHERE id <= 20;
⼆级索引，范围查询：DELETE FROM students WHERE age <= 23;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。