Previous article MySQL statement locking analysis

这个过程有意思的一点就是，如果你先在事务T1中执行：

# 事务T1BEGIN;SELECT * FROM hero WHERE number 
然后再到事务T2中执行：
# 事务T2BEGIN;SELECT * FROM hero WHERE number = 15 FOR UPDATE;
      
是没有问题的，因为在T2执行时，事务T1已经释放掉了number值为15的记录的锁，但是如果你先执行T2，再执行T1，由于T2已经持有了number值为15的记录的锁，事务T1将因为获取不到这个锁而等待。
      
我们再看一个使用主键进行范围查询的例子：
      
SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;
      
这个语句的执行过程其实和我们举的上一个例子类似。也是先到聚簇索引中定位到满足number >= 8这个条件的第一条记录，也就是number值为8的记录，然后就可以沿着由记录组成的单向链表一路向后找，每找到一条记录，就会为其加上锁，然后判断该记录符不符合范围查询的边界条件，不过这里的边界条件比较特殊：number >= 8，只要记录不小于8就算符合边界条件，所以判断和没判断是一样一样的。最后把这条记录返回给server层，server层再判断number >= 8这个条件是否成立，如果成立的话就发送给客户端，否则的话就结束查询。不过InnoDB存储引擎找到索引中的最后一条记录，也就是Supremum伪记录之后，在存储引擎内部就可以立即判断这是一条伪记录，不必要返回给server层处理，也没必要给它也加上锁（也就是说在第1步中就压根儿没给这条记录加锁）。整个过程会给number值为8、15、20这三条记录加上S型正经记录锁，画个图表示一下就是这样：
      

      

       
使用SELECT ... FOR UPDATE语句来为记录加锁：
和SELECT ... FOR UPDATE语句类似，只不过加的是X型正经记录锁。
       
使用UPDATE ...来为记录加锁，比方说：
UPDATE hero SET country = '汉' WHERE number >= 8;
这条UPDATE语句并没有更新二级索引列，加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。
如果UPDATE语句中更新了二级索引列，比方说：
UPDATE hero SET name = 'cao曹操' WHERE number >= 8;
这时候会首先更新聚簇索引记录，再更新对应的二级索引记录，所以加锁的步骤就是：
      
      

       
为number值为8的聚簇索引记录加上X型正经记录锁。
       
然后为上一步中的记录索引记录对应的idx_name二级索引记录加上X型正经记录锁。
       
为number值为15的聚簇索引记录加上X型正经记录锁。
       
然后为上一步中的记录索引记录对应的idx_name二级索引记录加上X型正经记录锁。
       
为number值为20的聚簇索引记录加上X型正经记录锁。
       
然后为上一步中的记录索引记录对应的idx_name二级索引记录加上X型正经记录锁。
      
      
画个图就是这样：
      

      
如果是下边这个语句：
      
UPDATE hero SET namey = '汉' WHERE number 
则会对number值为1、3、8聚簇索引记录以及它们对应的二级索引记录加X型正经记录锁，加锁顺序和上边语句中的加锁顺序类似，都是先对一条聚簇索引记录加锁后，再给对应的二级索引记录加锁。之后会继续对number值为15的聚簇索引记录加锁，但是随后InnoDB存储引擎判断它不符合边界条件，随即会释放掉该聚簇索引记录上的锁（注意这个过程中没有对number值为15的聚簇索引记录对应的二级索引记录加锁）。具体示意图就不画了。
      

       
使用DELETE ...来为记录加锁，比方说：
DELETE FROM hero WHERE number >= 8;
和
DELETE FROM hero WHERE number 
这两个语句的加锁情况和更新带有二级索引列的UPDATE语句一致，就不画图了。
      
      
对于使用二级索引进行等值查询的情况
      

       
小贴士： 在READ UNCOMMITTED和READ COMMITTED隔离级别下，使用普通的二级索引和唯一二级索引进行加锁的过程是一样的，所以我们也就不分开讨论了。
      
      

       
使用SELECT ... LOCK IN SHARE MODE来为记录加锁，比方说：
SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;
这个语句的执行过程是先通过二级索引idx_name定位到满足name = 'c曹操'条件的二级索引记录，然后进行回表操作。所以先要对二级索引记录加S型正经记录锁，然后再给对应的聚簇索引记录加S型正经记录锁，示意图如下：
      
      

      

       
这里需要再次强调一下这个语句的加锁顺序：
      
      

       
先对name列为'c曹操'二级索引记录进行加锁。
       
再对相应的聚簇索引记录进行加锁
      
      

       
小贴士： 我们知道idx_name是一个普通的二级索引，到idx_name索引中定位到满足name= ‘c曹操’这个条件的第一条记录后，就可以沿着这条记录一路向后找。可是从我们上边的描述中可以看出来，并没有对下一条二级索引记录进行加锁，这是为什么呢？这是因为设计InnoDB的大叔对等值匹配的条件有特殊处理，他们规定在InnoDB存储引擎层查找到当前记录的下一条记录时，在对其加锁前就直接判断该记录是否满足等值匹配的条件，如果不满足直接返回（也就是不加锁了），否则的话需要将其加锁后再返回给server层。所以这里也就不需要对下一条二级索引记录进行加锁了。
      
      
现在要介绍一个非常有趣的事情，我们假设上边这个语句在事务T1中运行，然后事务T2中运行下边一个我们之前介绍过的语句：
      
UPDATE hero SET name = '曹操' WHERE number = 8;
      
这两个语句都是要对number值为8的聚簇索引记录和对应的二级索引记录加锁，但是不同点是加锁的顺序不一样。这个UPDATE语句是先对聚簇索引记录进行加锁，后对二级索引记录进行加锁，如果在不同事务中运行上述两个语句，可能发生一种贼奇妙的事情 ——
      

       
事务T2持有了聚簇索引记录的锁，事务T1持有了二级索引记录的锁。
       
事务T2在等待获取二级索引记录上的锁，事务T1在等待获取聚簇索引记录上的锁。
      
      
两个事务都分别持有一个锁，而且都在等待对方已经持有的那个锁，这种情况就是所谓的死锁，两个事务都无法运行下去，必须选择一个进行回滚，对性能影响比较大。
      

       
使用SELECT ... FOR UPDATE语句时，比如：
SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;
这种情况下与SELECT ... LOCK IN SHARE MODE语句的加锁情况类似，都是给访问到的二级索引记录和对应的聚簇索引记录加锁，只不过加的是X型正经记录锁罢了。
       
使用UPDATE ...来为记录加锁，比方说：
与更新二级索引记录的SELECT ... FOR UPDATE的加锁情况类似，不过如果被更新的列中还有别的二级索引列的话，对应的二级索引记录也会被加锁。
       
使用DELETE ...来为记录加锁，比方说：
与SELECT ... FOR UPDATE的加锁情况类似，不过如果表中还有别的二级索引列的话，对应的二级索引记录也会被加锁。
      
      
对于使用二级索引进行范围查询的情况
      

       
使用SELECT ... LOCK IN SHARE MODE来为记录加锁，比方说：
SELECT * FROM hero FORCE INDEX(idx_name) WHERE name >= 'c曹操' LOCK IN SHARE MODE;
        

         
小贴士： 因为优化器会计算使用二级索引进行查询的成本，在成本较大时可能选择以全表扫描的方式来执行查询，所以我们这里使用FORCE INDEX(idx_name)来强制使用二级索引idx_name来执行查询。
        
这个语句的执行过程其实是先到二级索引中定位到满足name >= 'c曹操'的第一条记录，也就是name值为c曹操的记录，然后就可以沿着这条记录的链表一路向后找，从二级索引idx_name的示意图中可以看出，所有的用户记录都满足name >= 'c曹操'的这个条件，所以所有的二级索引记录都会被加S型正经记录锁，它们对应的聚簇索引记录也会被加S型正经记录锁。不过需要注意一下加锁顺序，对一条二级索引记录加锁完后，会接着对它相应的聚簇索引记录加锁，完后才会对下一条二级索引记录进行加锁，以此类推～ 画个图表示一下就是这样：
      
      

      

       
再来看下边这个语句：
SELECT * FROM hero FORCE INDEX(idx_name) WHERE name 
这个语句的加锁情况就有点儿有趣了。前边说在使用number 这个条件的语句中，需要把number值为15的记录也加一个锁，之后又判断它不符合边界条件而把锁释放掉。而对于查询条件name 的语句来说，执行该语句需要使用到二级索引，而与二级索引相关的条件是可以使用索引条件下推这个特性的。设计InnoDB的大叔规定，如果一条记录不符合索引条件下推中的条件的话，直接跳到下一条记录（这个过程根本不将其返回到server层），如果这已经是最后一条记录，那么直接向server层报告查询完毕。但是这里头有个问题呀：先对一条记录加了锁，然后再判断该记录是不是符合索引条件下推的条件，如果不符合直接跳到下一条记录或者直接向server层报告查询完毕，这个过程中并没有把那条被加锁的记录上的锁释放掉呀！！！。本例中使用的查询条件是name ，在为name值为'c曹操'的二级索引记录以及它对应的聚簇索引加锁之后，会接着二级索引中的下一条记录，也就是name值为'l刘备'的那条二级索引记录，由于该记录不符合索引条件下推的条件，而且是范围查询的最后一条记录，会直接向server层报告查询完毕，重点是这个过程中并不会释放name值为'l刘备'的二级索引记录上的锁，也就导致了语句执行完毕时的加锁情况如下所示：
      
      

      

       
这样子会造成一个尴尬情况，假如T1执行了上述语句并且尚未提交，T2再执行这个语句：
SELECT * FROM hero WHERE name = 'l刘备' FOR UPDATE;
T2中的语句需要获取name值为l刘备的二级索引记录上的X型正经记录锁，而T1中仍然持有name值为l刘备的二级索引记录上的S型正经记录锁，这就造成了T2获取不到锁而进入等待状态。
        

         
小贴士： 为啥不能释放不符合索引条件下推中的条件的二级索引记录上的锁呢？这个问题我也没想明白，人家就是这么规定的，如果有明白的小伙伴可以加我微信 xiaohaizi4919 来讨论一下哈～ 再强调一下，我使用的MySQL版本是5.7.21，不保证其他版本中的加锁情景是否完全一致。
        
       
使用SELECT ... FOR UPDATE语句时：
和SELECT ... FOR UPDATE语句类似，只不过加的是X型正经记录锁。
       
使用UPDATE ...来为记录加锁，比方说：
UPDATE hero SET country = '汉' WHERE name >= 'c曹操';
        

         
小贴士： FORCE INDEX只对SELECT语句起作用，UPDATE语句虽然支持该语法，但实质上不起作用，DELETE语句压根儿不支持该语法。
        
假设该语句执行时使用了idx_name二级索引来进行锁定读，那么它的加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。如果有其他二级索引列也被更新，那么也会为对应的二级索引记录进行加锁，就不赘述了。不过还有一个有趣的情况，比方说：
UPDATE hero SET country = '汉' WHERE name 
我们前边说的索引条件下推这个特性只适用于SELECT语句，也就是说UPDATE语句中无法使用，那么这个语句就会为name值为'c曹操'和'l刘备'的二级索引记录以及它们对应的聚簇索引进行加锁，之后在判断边界条件时发现name值为'l刘备'的二级索引记录不符合name 条件，再把该二级索引记录和对应的聚簇索引记录上的锁释放掉。这个过程如下图所示：
      
      

      

       

       
使用DELETE ...来为记录加锁，比方说：
DELETE FROM hero WHERE name >= 'c曹操';
和
DELETE FROM hero WHERE name 
如果这两个语句采用二级索引来进行锁定读，那么它们的加锁情况和更新带有二级索引列的UPDATE语句一致，就不画图了。
      
      
全表扫描的情况
      
比方说：
      
SELECT * FROM hero WHERE country = '魏' LOCK IN SHARE MODE;
      
由于country列上未建索引，所以只能采用全表扫描的方式来执行这条查询语句，存储引擎每读取一条聚簇索引记录，就会为这条记录加锁一个S型正常记录锁，然后返回给server层，如果server层判断country = '魏'这个条件是否成立，如果成立则将其发送给客户端，否则会释放掉该记录上的锁，画个图就像这样：
      

      
使用SELECT ... FOR UPDATE进行加锁的情况与上边类似，只不过加的是X型正经记录锁，就不赘述了。
      
对于UPDATE ...和DELETE ...的语句来说，在遍历聚簇索引中的记录，都会为该聚簇索引记录加上X型正经记录锁，然后：
      

       
如果该聚簇索引记录不满足条件，直接把该记录上的锁释放掉。
       
如果该聚簇索引记录满足条件，则会对相应的二级索引记录加上X型正经记录锁（DELETE语句会对所有二级索引列加锁，UPDATE语句只会为更新的二级索引列对应的二级索引记录加锁）。
      
      
The above is the detailed content of Previous article MySQL statement locking analysis. For more information, please follow other related articles on the PHP Chinese website!