超全面MySQL语句加锁分析（中篇）（求转）

说在前面的话

本文是用来系统阐述在MySQL中，不同语句在各种条件下的加锁情况，并不是解释各种锁是什么（或者说加锁的本质是什么），大家如果不理解什么是MVCC、ReadView、正经记录锁、gap锁、next-key锁、插入意向锁这些概念的，可以参考MySQL的官方文档，或者直接参照《MySQL是怎样运行的：从根儿上理解MySQL》这本小册（里边有比官方文档更贴心，更详细的解释，文章中涉及到的所有概念均在小册中有详细解释，有疑惑，并且有兴趣的同学可以扫描下边二维码看看）：

建议：

        1. 本篇文章不适合碎片化时间阅读，最好使用电脑观看，或者将字体跳到最小效果好一些

        2. 可能一下子看不完，关注 + 收藏 + 好看 + 转发一波

        3. 不要跳着看

        4. 一定要先看过上一篇文章：超全面MySQL语句加锁分析（上篇）（求转）

REPEATABLE READ隔离级别下

采用加锁的方式解决并发事务产生的问题时，REPEATABLE READ隔离级别与READ UNCOMMITTED和READ COMMITTED这两个隔离级别相比，最主要的就是要解决幻读问题，幻读问题的解决还得靠我们之前讲过的gap锁。

对于使用主键进行等值查询的情况

• 使用SELECT ... LOCK IN SHARE MODE来为记录加锁，比方说：

  SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;


  我们知道主键具有唯一性，如果在一个事务中第一次执行上述语句时将得到的结果集中包含一条记录，第二次执行上述语句前肯定不会有别的事务插入多条number值为8的记录（主键具有唯一性），也就是说一个事务中两次执行上述语句并不会发生幻读，这种情况下和READ UNCOMMITTED／READ COMMITTED隔离级别下一样，我们只需要为这条number值为8的记录加一个S型正经记录锁就好了，如图所示：

  但是如果我们要查询主键值不存在的记录，比方说：

  SELECT * FROM hero WHERE number = 7 LOCK IN SHARE MODE;


  由于number值为7的记录不存在，为了禁止幻读现象（也就是避免在同一事务中下一次执行相同语句时得到的结果集中包含number值为7的记录），在当前事务提交前我们需要预防别的事务插入number值为7的新记录，所以需要在number值为8的记录上加一个gap锁，也就是不允许别的事务插入number值在(3, 8)这个区间的新记录。画个图表示一下：

  如果在READ UNCOMMITTED／READ COMMITTED隔离级别下一样查询了一条主键值不存在的记录，那么什么锁也不需要加，因为在READ UNCOMMITTED／READ COMMITTED隔离级别下，并不需要禁止幻读问题。

• 其余语句使用主键进行等值查询的情况与READ UNCOMMITTED／READ COMMITTED隔离级别下的情况类似，这里就不赘述了。

对于使用主键进行范围查询的情况

• 使用SELECT ... LOCK IN SHARE MODE语句来为记录加锁，比方说：

  SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;


  因为要解决幻读问题，所以需要禁止别的事务插入number值符合number >= 8的记录，又因为主键本身就是唯一的，所以我们不用担心在number值为8的前边有新记录插入，只需要保证不要让新记录插入到number值为8的后边就好了，所以：

• 为number值为8的聚簇索引记录加一个S型正经记录锁。

• 为number值大于8的所有聚簇索引记录都加一个S型next-key锁（包括Supremum伪记录）。

画个图就是这样子：

小贴士： 为什么不给Supremum记录加gap锁，而要加next-key锁呢？其实设计InnoDB的大叔在处理Supremum记录上加的next-key锁时就是当作gap锁看待的，只不过为了节省锁结构（我们前边说锁的类型不一样的话不能被放到一个锁结构中）才这么做的而已，大家不必在意。

与READ UNCOMMITTED/READ COMMITTED隔离级别类似，在REPEATABLE READ隔离级别下，下边这个范围查询也是有点特殊：

SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;


这个语句的执行过程我们在之前唠叨过，在READ UNCOMMITTED/READ COMMITTED隔离级别下，这个语句会为number值为1、3、8、15这4条记录都加上S型正经记录锁，然后由于number值为15的记录不满足边界条件number <= 8，随后便把这条记录的锁释放掉。在REPEATABLE READ隔离级别下的加锁过程与之类似，不过会为1、3、8、15这4条记录都加上S型next-key锁，但是有一点需要大家十分注意：REPEATABLE READ隔离级别下，在判断number值为15的记录不满足边界条件 number <= 8 后，并不会去释放加在该记录上的锁！！！ 所以在REPEATABLE READ隔离级别下，该语句的加锁示意图就如下所示：

这样如果别的事务想要插入的新记录的number值在(-∞, 1)、(1, 3)、(3, 8)、(8, 15)之间的话，是会进入等待状态的。

小贴士： 很显然这么粗暴的做法导致的一个后果就是别的事务竟然不允许插入number值在(8, 15)这个区间中的新记录，甚至不允许别的事务再获取number值为15的记录上的锁，而理论上只需要禁止别的事务插入number值在(-∞, 8)之间的新记录就好。

使用SELECT ... FOR UPDATE语句来为记录加锁：

和SELECT ... LOCK IN SHARE MODE语句类似，只不过需要将上边提到的S型next-key锁替换成X型next-key锁。

使用UPDATE ...来为记录加锁：

如果UPDATE语句未更新二级索引列，比方说：

UPDATE hero SET country = '汉' WHERE number >= 8;


这条UPDATE语句并没有更新二级索引列，加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。

如果UPDATE语句中更新了二级索引列，比方说：

UPDATE hero SET name = 'cao曹操' WHERE number >= 8;


对聚簇索引记录加锁的情况和SELECT ... FOR UPDATE语句一致，也就是对number值为8的聚簇索引记录加X型正经记录锁，对number值15、20的聚簇索引记录以及Supremum记录加X型next-key锁。但是因为也要更新二级索引idx_name，所以也会对number值为8、15、20的聚簇索引记录对应的idx_name二级索引记录加X型正经记录锁，画个图表示一下：

如果是下边这个语句：

UPDATE hero SET name = 'cao曹操' WHERE number <= 8;


则会对number值为1、3、8、15的聚簇索引记录加X型next-key，其中number值为15的聚簇索引记录不满足number <= 8的边界条件，虽然在REPEATABLE READ隔离级别下不会将它的锁释放掉，但是也并不会对这条聚簇索引记录对应的二级索引记录加锁，也就是说只会为number值为1、3、8的聚簇索引记录对应的idx_name二级索引记录加X型正经记录锁，加锁示意图如下所示：

使用DELETE ...来为记录加锁，比方说：

DELETE FROM hero WHERE number >= 8;


和

DELETE FROM hero WHERE number <= 8;


这两个语句的加锁情况和更新带有二级索引列的UPDATE语句一致，就不画图了。

对于使用唯一二级索引进行等值查询的情况

由于hero表并没有唯一二级索引，我们把原先的idx_name修改为一个唯一二级索引uk_name：

ALTER TABLE hero DROP INDEX idx_name, ADD UNIQUE KEY uk_name (name);


• 使用SELECT ... LOCK IN SHARE MODE语句来为记录加锁，比方说：

  SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;


  由于唯一二级索引具有唯一性，如果在一个事务中第一次执行上述语句时将得到一条记录，第二次执行上述语句前肯定不会有别的事务插入多条name值为'c曹操'的记录（二级索引具有唯一性），也就是说一个事务中两次执行上述语句并不会发生幻读，这种情况下和READ UNCOMMITTED／READ COMMITTED隔离级别下一样，我们只需要为这条name值为'c曹操'的二级索引记录加一个S型正经记录锁，然后再为它对应的聚簇索引记录加一个S型正经记录锁就好了，我们画个图看看：

  注意加锁顺序，是先对二级索引记录加锁，再对聚簇索引加锁。

  如果对唯一二级索引列进行等值查询的记录并不存在，比如：

  SELECT * FROM hero WHERE name = 'g关羽' LOCK IN SHARE MODE;


  为了禁止幻读，所以需要保证别的事务不能再插入name值为'g关羽'的新记录。在唯一二级索引uk_name中，键值比'g关羽'大的第一条记录的键值为l刘备，所以需要在这条二级索引记录上加一个gap锁，如图所示：

  注意，这里只对二级索引记录进行加锁，并不会对聚簇索引记录进行加锁。

• 使用SELECT ... FOR UPDATE语句来为记录加锁，比如：

  和SELECT ... LOCK IN SHARE MODE语句类似，只不过加的是X型正经记录锁。

• 使用UPDATE ...来为记录加锁，比方说：

  与SELECT ... FOR UPDATE的加锁情况类似，不过如果被更新的列中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。

• 使用DELETE ...来为记录加锁，比方说：

  与SELECT ... FOR UPDATE的加锁情况类似，不过如果表中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。

对于使用唯一二级索引进行范围查询的情况

• 使用SELECT ... LOCK IN SHARE MODE语句来为记录加锁，比方说：

  SELECT * FROM hero FORCE INDEX(uk_name) WHERE name >= 'c曹操' LOCK IN SHARE MODE;


  这个语句的执行过程其实是先到二级索引中定位到满足name >= 'c曹操'的第一条记录，也就是name值为c曹操的记录，然后就可以沿着由记录组成的单向链表一路向后找。从二级索引idx_name的示意图中可以看出，所有的用户记录都满足name >= 'c曹操'的这个条件，所以所有的二级索引记录都会被加S型next-key锁，它们对应的聚簇索引记录也会被加S型正经记录锁，二级索引的最后一条Supremum记录也会被加S型next-key锁。不过需要注意一下加锁顺序，对一条二级索引记录加锁完后，会接着对它响应的聚簇索引记录加锁，完后才会对下一条二级索引记录进行加锁，以此类推～ 画个图表示一下就是这样：

  稍等一下，不是说uk_name是唯一二级索引么？唯一二级索引本身就能保证其自身的值是唯一的，那为啥还要给name值为'c曹操'的记录加上S型next-key锁，而不是S型正经记录锁呢？其实我也不知道，按理说只需要给这条二级索引记录加S型正经记录锁就好了，我也没想明白设计InnoDB的大叔是怎么想的，有知道的小伙伴赶紧添加我微信：xiaohaizi4919联系我哈（聊八卦的同学请勿添加）～

  再来看下边这个语句：

  SELECT * FROM hero WHERE name <= 'c曹操' LOCK IN SHARE MODE;


  这个语句先会为name值为'c曹操'的二级索引记录加S型next-key锁以及它对应的聚簇索引记录加S型正经记录锁。然后还要给name值为'l刘备'的二级索引记录加S型next-key锁，name值为'l刘备'的二级索引记录不满足索引条件下推的name <= 'c曹操'条件，压根儿不会释放掉该记录的锁就直接报告server层查询完毕了。这样可以禁止其他事务插入name值在('c曹操', 'l刘备')之间的新记录，从而防止幻读产生。所以这个过程的加锁示意图如下：

  这里大家要注意一下，设计InnoDB的大叔在这里给name值为'l刘备'的二级索引记录加的是S型next-key锁，而不是简单的gap锁。

• 使用SELECT ... FOR UPDATE语句来为记录加锁：

  和SELECT ... LOCK IN SHARE MODE语句类似，只不过加的是X型正经记录锁。

• 使用UPDATE ...来为记录加锁，比方说：

  UPDATE hero SET country = '汉' WHERE name >= 'c曹操';


  假设该语句执行时使用了uk_name二级索引来进行锁定读（如果二级索引扫描的记录太多，也可能因为成本过大直接使用全表扫描的方式进行锁定读），而这条UPDATE语句并没有更新二级索引列，那么它的加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。如果有其他二级索引列也被更新，那么也会为这些二级索引记录进行加锁，就不赘述了。不过还需要强调一种情况，比方说：

  UPDATE hero SET country = '汉' WHERE name <= 'c曹操';


  我们前边说的索引条件下推这个特性只适用于SELECT语句，也就是说UPDATE语句中无法使用，无法使用索引条件下推这个特性时需要先进行回表操作，那么这个语句就会为name值为'c曹操'和'l刘备'的二级索引记录加X型next-key锁，对它们对应的聚簇索引记录进行加X型正经记录锁。不过之后在判断边界条件时，虽然name值为'l刘备'的二级索引记录不符合name <= 'c曹操'的边界条件，但是在REPEATABLE READ隔离级别下并不会释放该记录上加的锁，整个过程的加锁示意图就是：

• 使用DELETE ...来为记录加锁，比方说：

  DELETE FROM hero WHERE name >= 'c曹操';


  和

  DELETE FROM hero WHERE name <= 'c曹操';


  如果这两个语句采用二级索引来进行锁定读，那么它们的加锁情况和更新带有二级索引列的UPDATE语句一致，就不画图了。

对于使用普通二级索引进行等值查询的情况

我们再把上边的唯一二级索引uk_name改回普通二级索引idx_name：

ALTER TABLE hero DROP INDEX uk_name, ADD INDEX idx_name (name);


• 使用SELECT ... LOCK IN SHARE MODE语句来为记录加锁，比方说：

  SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;


  由于普通的二级索引没有唯一性，所以一个事务在执行上述语句之后，要阻止别的事务插入name值为'c曹操'的新记录，设计InnoDB的大叔采用下边的方式对上述语句进行加锁：

• 对所有name值为'c曹操'的二级索引记录加S型next-key锁，它们对应的聚簇索引记录加S型正经就锁。

• 对最后一个name值为'c曹操'的二级索引记录的下一条二级索引记录加gap锁。

所以整个加锁示意图就如下所示：

如果对普通二级索引等值查询的值并不存在，比如：

SELECT * FROM hero WHERE name = 'g关羽' LOCK IN SHARE MODE;


加锁方式和我们上边唠叨过的唯一二级索引的情况是一样的，就不赘述了。

使用SELECT ... FOR UPDATE语句来为记录加锁，比如：

和SELECT ... LOCK IN SHARE MODE语句类似，只不过加的是X型正经记录锁。

使用UPDATE ...来为记录加锁，比方说：

与SELECT ... FOR UPDATE的加锁情况类似，不过如果被更新的列中还有别的二级索引列的话，这些对应的二级索引记录也会被加锁。

使用DELETE ...来为记录加锁，比方说：

与SELECT ... FOR UPDATE的加锁情况类似，不过如果表中还有别的二级索引列的话，这些对应的二级索引记录也会被加锁。

对于使用普通二级索引进行范围查询的情况

与唯一二级索引的加锁情况类似，就不多唠叨了哈～

全表扫描的情况

比方说：

SELECT * FROM hero WHERE country = '魏' LOCK IN SHARE MODE;


由于country列上未建索引，所以只能采用全表扫描的方式来执行这条查询语句，存储引擎每读取一条聚簇索引记录，就会为这条记录加锁一个S型next-key锁，然后返回给server层，如果server层判断country = '魏'这个条件是否成立，如果成立则将其发送给客户端，否则会向InnoDB存储引擎发送释放掉该记录上的锁的消息，不过在REPEATABLE READ隔离级别下，InnoDB存储引擎并不会真正的释放掉锁，所以聚簇索引的全部记录都会被加锁，并且在事务提交前不释放。画个图就像这样：

大家看到了么：全部记录都被加了next-key锁！此时别的事务别说想向表中插入啥新记录了，就是对某条记录加X锁都不可以，这种情况下会极大影响访问该表的并发事务处理能力，所以如果可能的话，尽可能为表建立合适的索引吧～

使用SELECT ... FOR UPDATE进行加锁的情况与上边类似，只不过加的是X型正经记录锁，就不赘述了。

对于UPDATE ...语句来说，加锁情况与SELECT ... FOR UPDATE类似，不过如果被更新的列中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。

和DELETE ...的语句来说，加锁情况与SELECT ... FOR UPDATE类似，不过如果表中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。

未完待续

下一章节继续唠叨INSERT语句的加锁情况，敬请期待。如果文中有任何问题，请联系作者：xiaohaizi4919（正经微信，扯犊子的请勿添加）。

关注小青蛙，全都是技术干货哈：

原文链接

大家可以点击原文链接，查看《MySQL是怎样运行的：从根儿上理解MySQL》的完整内容，通俗到爆炸💥的MySQL进阶读物，原来学习可以这么有趣（也可以扫下边的二维码直接观看）：