一，语句加锁分析

先给上一篇的hero表建立一个二级索引。

  1. alert table hero add index idx_name(name);

现在hero表就有了两个索引，主键聚簇索引&普通二级索引。

image.png

1.普通的select语句

在不同的隔离级别下，普通的select语句具有不同的表现。

  1. 在读未提交隔离级别下，不加锁，直接读取记录的最新版本；可能出现脏读，不可重复读，幻读。
  2. 在读已提交隔离级别下，不加锁，每次执行普通的select都会生成一个readview，避免脏读，但是没有避免幻读和不可重复读。
  3. 在可重复度隔离级别下，不加锁，只有在第一次执行普通的select语句时生成一个readview，这样就把脏读，不可重复读，幻读都避免了。

注意：

  1. # 事务T1，在可重复读隔离级别下：
  2. begin;
  3. select * from hero where number =30;
  4. # empty set
  7. # 此时事务T2执行了：
  8. insert into hero values(30,'g关羽','魏');
  9. # 语句，并提交
  11. update hero set country = '蜀' where number =30;
  13. select * from hero where number =30;
  15. # 查到了一条记录

在可重复读隔离级别下，T1第一次执行普通的select语句时生成了一个readview，之后T2向hero表中插入一条新记录并提交。readview并不能阻止T1执行update或者delete语句来改动这个新插入的记录（由于t2已经提交，因此改动该记录并不会造成阻塞），但是这样一来，这条新记录的事务id隐藏列的值就变成了T1的事务id。之后T1在使用普通的select语句去查询这条记录就可以看到这条记录了，也就可以把这条记录返回给客户端。因为这个特殊现象的存在，我们可以认为MVCC并不能完全禁止幻读。

  1. 在串行化隔离级别下，需要分两种情况看：
    1. 在禁止自动提交的时候，普通的select语句会被转化为 select…lock in share mode ,也就是读取记录前需要先获得记录的S锁。
    2. 在允许自动提交的时候，普通的select语句并不会加锁，只是利用MVCC生成一个readview来读取记录。为啥不加锁呢？因为启用自动提交意味着一个事物中只包含一条语句，而只执行一条语句也就不会出现不可重复读，幻读这样的现象了。

2.锁定读的语句

我们把下边四种语句放到一起讨论：

  • 语句一：SELECT … LOCK IN SHARE MODE;
  • 语句二：SELECT … FOR UPDATE;
  • 语句三：UPDATE …
  • 语句四：DELETE …

语句一和语句二是MySQL中规定的两种锁定读的语法格式，而语句三和语句四由于在执行过程需要首先定位到被改动的记录并给记录加锁，也可以被认为是一种锁定读。

2.1 READ UNCOMMITTED/READ COMMITTED隔离级别下

在READ UNCOMMITTED下语句的加锁方式和READ COMMITTED隔离级别下语句的加锁方式基本一致，所以就放到一块儿说了。值得注意的是，采用加锁方式解决并发事务带来的问题时，其实脏读和不可重复读在任何一个隔离级别下都不会发生（因为读-写操作需要排队进行）。

1）使用主键进行等值查询

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

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


    这个语句执行时只需要访问一下聚簇索引中number值为8的记录，所以只需要给它加一个S型正经记录锁就好了，如图所示：
    image.png

  • 使用SELECT … FOR UPDATE来为记录加锁，比方说：

    1. SELECT * FROM hero WHERE number = 8 FOR UPDATE;

    这个语句执行时只需要访问一下聚簇索引中number值为8的记录，所以只需要给它加一个X型正经记录锁就好了，如图所示：
    image.png

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

    1. UPDATE hero SET country = '汉' WHERE number = 8;

    这条UPDATE语句并没有更新二级索引列，加锁方式和上边所说的SELECT … FOR UPDATE语句一致。如果UPDATE语句中更新了二级索引列，比方说：

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

该语句的实际执行步骤是首先更新对应的number值为8的聚簇索引记录，再更新对应的二级索引记录，所以加锁的步骤就是：

  1. 为number值为8的聚簇索引记录加上X型正经记录锁（该记录对应的）。
  2. 为该聚簇索引记录对应的idx_name二级索引记录（也就是name值为’c曹操’，number值为8的那条二级索引记录）加上X型正经记录锁。

image.png

用带圆圈的数字来表示为各条记录加锁的顺序。

  • 使用DELETE …来为记录加锁，比方说：
    1. DELETE FROM hero WHERE number = 8;
    “DELETE表中的一条记录”其实意味着对聚簇索引和所有的二级索引中对应的记录做DELETE操作，本例子中就是要先把number值为8的聚簇索引记录执行DELETE操作，然后把对应的idx_name二级索引记录删除，所以加锁的步骤和上边更新带有二级索引列的UPDATE语句一致。

2）使用主键进行范围查询

  • 使用SELECT … LOCK IN SHARE MODE来为记录加锁，比方说：SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

这个语句看起来十分简单，但它的执行过程还是有点复杂的：

  1. 先到聚簇索引中定位到满足number <= 8的第一条记录，也就是number值为1的记录，然后为其加锁。
  2. 判断一下该记录是否符合索引条件下推中的条件。不过需要注意的是，索引条件下推只是为了减少回表次数，也就是减少读取完整的聚簇索引记录的次数，从而减少IO操作。而对于聚簇索引而言不需要回表，它本身就包含着全部的列，也起不到减少IO操作的作用，所以InnoDB规定这个索引条件下推特性只适用于二级索引。也就是说在本例中与被使用索引有关的条件是：number <= 8，而number列又是聚簇索引列，所以本例中并没有符合索引条件下推的查询条件，自然也就不需要判断该记录是否符合索引条件下推中的条件。
  3. 判断一下该记录是否符合范围查询的边界条件因为在本例中是利用主键number进行范围查询，InnoDB规定每从聚簇索引中取出一条记录时都要判断一下该记录是否符合范围查询的边界条件，也就是number <= 8这个条件。如果符合的话将其返回给server层继续处理，否则的话需要释放掉在该记录上加的锁，并给server层返回一个查询完毕的信息。对于number值为1的记录是符合这个条件的，所以会将其返回到server层继续处理。
  4. 将该记录返回到server层继续判断。server层如果收到存储引擎层提供的查询完毕的信息，就结束查询，否则继续判断那些没有进行索引条件下推的条件，在本例中就是继续判断number <= 8这个条件是否成立。不是在第3步中已经判断过了么，怎么在这又判断一回？InnoDB把凡是没有经过索引条件下推的条件都需要放到server层再判断一遍。如果该记录符合剩余的条件（没有进行索引条件下推的条件），那么就把它发送给客户端，不然的话需要释放掉在该记录上加的锁。
  5. 然后刚刚查询得到的这条记录（也就是number值为1的记录）组成的单向链表继续向后查找，得到了number值为3的记录，然后重复第2，3，4、5这几个步骤。

但是这个过程有个问题，就是当找到number值为8的那条记录的时候，还得向后找一条记录（也就是number值为15的记录），在存储引擎读取这条记录的时候，也就是上述的第1步中，就得为这条记录加锁，然后在第3步时，判断该记录不符合number <= 8这个条件，又要释放掉这条记录的锁，这个过程导致number值为15的记录先被加锁，然后把锁释放掉，过程就是这样：
image.png

如果先在事务T1中执行：

  1. # 事务T1
  2. BEGIN;
  3. SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

然后再到事务T2中执行：

  1. # 事务T2
  2. BEGIN;
  3. SELECT * FROM hero WHERE number = 15 FOR UPDATE;

是没有问题的，因为在T2执行时，事务T1已经释放掉了number值为15的记录的锁，但是如果你先执行T2，再执行T1，由于T2已经持有了number值为15的记录的锁，事务T1将因为获取不到这个锁而等待。

我们再看一个使用主键进行范围查询的例子：

  1. 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型正经记录锁，画个图表示一下就是这样：
image.png

  • 使用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;

  • 这时候会首先更新聚簇索引记录，再更新对应的二级索引记录，所以加锁的步骤就是：

    1. 为number值为8的聚簇索引记录加上X型正经记录锁。
    2. 然后为上一步中的记录索引记录对应的idx_name二级索引记录加上X型正经记录锁。
    3. 为number值为15的聚簇索引记录加上X型正经记录锁。
    4. 然后为上一步中的记录索引记录对应的idx_name二级索引记录加上X型正经记录锁。
    5. 为number值为20的聚簇索引记录加上X型正经记录锁。
    6. 然后为上一步中的记录索引记录对应的idx_name二级索引记录加上X型正经记录锁。

image.png
如果是下边这个语句：

  1. UPDATE hero SET name = '汉' WHERE number <= 8;

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

  • 使用DELETE …来为记录加锁，比方说：DELETE FROM hero WHERE number >= 8;<br />和DELETE FROM hero WHERE number <= 8;
    这两个语句的加锁情况和更新带有二级索引列的UPDATE语句一致。

3）使用二级索引进行等值查询

在READ UNCOMMITTED和READ COMMITTED隔离级别下，使用普通的二级索引和唯一二级索引进行加锁的过程是一样的。

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

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

    这个语句的执行过程是先通过二级索引idx_name定位到满足name = ‘c曹操’条件的二级索引记录，然后进行回表操作。所以先要对二级索引记录加S型正经记录锁，然后再给对应的聚簇索引记录加S型正经记录锁，示意图如下：
    image.png
    这里需要再次强调一下这个语句的加锁顺序：

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

idx_name是一个普通的二级索引，到idx_name索引中定位到满足name= ‘c曹操’这个条件的第一条记录后，就可以沿着这条记录一路向后找。可是从上边的描述中可以看出来，并没有对下一条二级索引记录进行加锁，这是为什么呢？因为InnoDB对等值匹配的条件有特殊处理，他们规定在InnoDB存储引擎层查找到当前记录的下一条记录时，在对其加锁前就直接判断该记录是否满足等值匹配的条件，如果不满足直接返回（也就是不加锁了），否则的话需要将其加锁后再返回给server层。所以这里也就不需要对下一条二级索引记录进行加锁了。

我们假设上边这个语句在事务T1中运行，然后事务T2中运行下边一个我们之前介绍过的语句：

  1. UPDATE hero SET name = '曹操' WHERE number = 8;

这两个语句都是要对number值为8的聚簇索引记录和对应的二级索引记录加锁，但是不同点是加锁的顺序不一样。这个UPDATE语句是先对聚簇索引记录进行加锁，后对二级索引记录进行加锁，如果在不同事务中运行上述两个语句:

  • 事务T2持有了聚簇索引记录的锁，事务T1持有了二级索引记录的锁。
  • 事务T2在等待获取二级索引记录上的锁，事务T1在等待获取聚簇索引记录上的锁。

两个事务都分别持有一个锁，而且都在等待对方已经持有的那个锁，这种情况就是所谓的死锁，两个事务都无法运行下去，必须选择一个进行回滚，对性能影响比较大。

  • 使用SELECT … FOR UPDATE语句时，比如：

    1. SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;

  • 这种情况下与SELECT … LOCK IN SHARE MODE语句的加锁情况类似，都是给访问到的二级索引记录和对应的聚簇索引记录加锁，只不过加的是X型正经记录锁罢了。

  • 使用UPDATE …来为记录加锁，比方说：与更新二级索引记录的SELECT … FOR UPDATE的加锁情况类似，不过如果被更新的列中还有别的二级索引列的话，对应的二级索引记录也会被加锁。
  • 使用DELETE …来为记录加锁，比方说：与SELECT … FOR UPDATE的加锁情况类似，不过如果表中还有别的二级索引列的话，对应的二级索引记录也会被加锁。

4）使用二级索引进行范围查询

  • 使用SELECT … LOCK IN SHARE MODE来为记录加锁，比方说：
    1. 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型正经记录锁。不过需要注意一下加锁顺序，对一条二级索引记录加锁完后，会接着对它相应的聚簇索引记录加锁，完后才会对下一条二级索引记录进行加锁，以此类推。
    image.png

再来看下边这个语句：

  1. SELECT * FROM hero FORCE INDEX(idx_name) WHERE name <= 'c曹操' LOCK IN SHARE MODE;


在使用number <= 8这个条件的语句中，需要把number值为15的记录也加一个锁，之后又判断它不符合边界条件而把锁释放掉。而对于查询条件name <= ‘c曹操’的语句来说，执行该语句需要使用到二级索引，而与二级索引相关的条件是可以使用索引条件下推这个特性的。InnoDB规定，如果一条记录不符合索引条件下推中的条件的话，直接跳到下一条记录（这个过程根本不将其返回到server层），如果这已经是最后一条记录，那么直接向server层报告查询完毕。

但是这里头有个问题：先对一条记录加了锁，然后再判断该记录是不是符合索引条件下推的条件，如果不符合直接跳到下一条记录或者直接向server层报告查询完毕，这个过程中并没有把那条被加锁的记录上的锁释放掉！！！。本例中使用的查询条件是name <= ‘c曹操’，在为name值为’c曹操’的二级索引记录以及它对应的聚簇索引加锁之后，会接着二级索引中的下一条记录，也就是name值为’l刘备’的那条二级索引记录，由于该记录不符合索引条件下推的条件，而且是范围查询的最后一条记录，会直接向server层报告查询完毕，重点是这个过程中并不会释放name值为’l刘备’的二级索引记录上的锁，也就导致了语句执行完毕时的加锁情况如下所示：
image.png
这样子会造成一个尴尬情况，假如T1执行了上述语句并且尚未提交，T2再执行这个语句：

  1. SELECT * FROM hero WHERE name = 'l刘备' FOR UPDATE;


T2中的语句需要获取name值为l刘备的二级索引记录上的X型正经记录锁，而T1中仍然持有name值为l刘备的二级索引记录上的S型正经记录锁，这就造成了T2获取不到锁而进入等待状态。

为啥不能释放不符合索引条件下推中的条件的二级索引记录上的锁呢？我也不知道，人家就是这么规定的。

  • 使用SELECT … FOR UPDATE语句时：和SELECT … FOR UPDATE语句类似，只不过加的是X型正经记录锁。
  • 使用UPDATE …来为记录加锁，比方说：
    1. UPDATE hero SET country = '汉' WHERE name >= 'c曹操';

FORCE INDEX只对SELECT语句起作用，UPDATE语句虽然支持该语法，但实质上不起作用，DELETE语句压根儿不支持该语法。

假设该语句执行时使用了idx_name二级索引来进行锁定读，那么它的加锁方式和上边所说的SELECT … FOR UPDATE语句一致。如果有其他二级索引列也被更新，那么也会为对应的二级索引记录进行加锁。

还有一个情况：

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


索引条件下推这个特性只适用于SELECT语句，也就是说UPDATE语句中无法使用，那么这个语句就会为name值为’c曹操’和’l刘备’的二级索引记录以及它们对应的聚簇索引进行加锁，之后在判断边界条件时发现name值为’l刘备’的二级索引记录不符合name <= ‘c曹操’条件，再把该二级索引记录和对应的聚簇索引记录上的锁释放掉。这个过程如下图所示：
image.png

  • 使用DELETE …来为记录加锁，比方说： ```sql DELETE FROM hero WHERE name >= ‘c曹操’;

DELETE FROM hero WHERE name <= ‘c曹操’;

  1. <br />如果这两个语句采用二级索引来进行锁定读，那么它们的加锁情况和更新带有二级索引列的UPDATE语句一致。
  2. <a name="IG6Xc"></a>
  3. #### 5）全表扫描
  5. 比方说：
  6. ```sql
  7. SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

由于country列上未建索引，所以只能采用全表扫描的方式来执行这条查询语句，存储引擎每读取一条聚簇索引记录，就会为这条记录加锁一个S型正常记录锁，然后返回给server层，如果server层判断country = ‘魏’这个条件是否成立，如果成立则将其发送给客户端，否则会释放掉该记录上的锁，画个图就像这样：

image.png

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

对于UPDATE …和DELETE …的语句来说，在遍历聚簇索引中的记录，都会为该聚簇索引记录加上X型正经记录锁，然后：

  • 如果该聚簇索引记录不满足条件，直接把该记录上的锁释放掉。
  • 如果该聚簇索引记录满足条件，则会对相应的二级索引记录加上X型正经记录锁（DELETE语句会对所有二级索引列加锁，UPDATE语句只会为更新的二级索引列对应的二级索引记录加锁）。

    2.2 REPEATABLE READ隔离级别下

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

1）使用主键进行等值查询

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

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

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

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


    由于number值为7的记录不存在，为了禁止幻读现象（也就是避免在同一事务中下一次执行相同语句时得到的结果集中包含number值为7的记录），在当前事务提交前我们需要预防别的事务插入number值为7的新记录，所以需要在number值为8的记录上加一个gap锁，也就是不允许别的事务插入number值在(3, 8)这个区间的新记录。
    image.png
    如果在READ UNCOMMITTED／READ COMMITTED隔离级别下一样查询了一条主键值不存在的记录，那么什么锁也不需要加，因为在READ UNCOMMITTED／READ COMMITTED隔离级别下，并不需要禁止幻读问题。

  • 其余语句使用主键进行等值查询的情况与READ UNCOMMITTED／READ COMMITTED隔离级别下的情况类似。

2）使用主键进行范围查询

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

    1. 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伪记录）。

image.png

为什么不给Supremum记录加gap锁，而要加next-key锁呢？其实InnoDB在处理Supremum记录上加的next-key锁时就是当作gap锁看待的，为了节省锁结构（锁的类型不一样的话不能被放到一个锁结构中）。

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

  1. 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隔离级别下，该语句的加锁示意图就如下所示：

image.png

这样如果别的事务想要插入的新记录的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语句未更新二级索引列，比方说：

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

    这条UPDATE语句并没有更新二级索引列，加锁方式和上边所说的SELECT … FOR UPDATE语句一致。如果UPDATE语句中更新了二级索引列，比方说：

    1. 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型正经记录锁，画个图表示一下：
    image.png
    如果是下边这个语句：

    1. 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型正经记录锁，加锁示意图如下所示：
    image.png

  • 使用DELETE …来为记录加锁，比方说： ```sql DELETE FROM hero WHERE number >= 8;

DELETE FROM hero WHERE number <= 8;

  1. 这两个语句的加锁情况和更新带有二级索引列的UPDATE语句一致。
  3. <a name="nllL9"></a>
  4. #### 3）使用唯一二级索引进行等值查询
  6. 由于hero表并没有唯一二级索引，我们把原先的idx_name修改为一个唯一二级索引uk_name：
  7. ```sql
  8. ALTER TABLE hero DROP INDEX idx_name, ADD UNIQUE KEY uk_name (name);

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

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

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

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


    为了禁止幻读，所以需要保证别的事务不能再插入name值为’g关羽’的新记录。在唯一二级索引uk_name中，键值比’g关羽’大的第一条记录的键值为l刘备，所以需要在这条二级索引记录上加一个gap锁，如图所示：
    image.png
    注意，这里只对二级索引记录进行加锁，并不会对聚簇索引记录进行加锁。

  • 使用SELECT … FOR UPDATE语句来为记录加锁，比如：和SELECT … LOCK IN SHARE MODE语句类似，只不过加的是X型正经记录锁。

  • 使用UPDATE …来为记录加锁，比方说：与SELECT … FOR UPDATE的加锁情况类似，不过如果被更新的列中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。

  • 使用DELETE …来为记录加锁，比方说：与SELECT … FOR UPDATE的加锁情况类似，不过如果表中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。

    4）使用唯一二级索引进行范围查询

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

    1. 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锁。不过需要注意一下加锁顺序，对一条二级索引记录加锁完后，会接着对它响应的聚簇索引记录加锁，完后才会对下一条二级索引记录进行加锁，以此类推～ 画个图表示一下就是这样：
    image.png
    唯一二级索引本身就能保证其自身的值是唯一的，那为啥还要给name值为’c曹操’的记录加上S型next-key锁，而不是S型正经记录锁呢？其实我也不知道，按理说只需要给这条二级索引记录加S型正经记录锁就好了，我也没想明白InnoDB是怎么想的。

再来看下边这个语句：

  1. 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刘备’)之间的新记录，从而防止幻读产生。所以这个过程的加锁示意图如下：
image.png
InnoDB在这里给name值为’l刘备’的二级索引记录加的是S型next-key锁，而不是简单的gap锁。

  • 使用SELECT … FOR UPDATE语句来为记录加锁：和SELECT … LOCK IN SHARE MODE语句类似，只不过加的是X型正经记录锁。

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

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

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

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


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

  • 使用DELETE …来为记录加锁，比方说： ```sql DELETE FROM hero WHERE name >= ‘c曹操’;

DELETE FROM hero WHERE name <= ‘c曹操’;

  1. <br />如果这两个语句采用二级索引来进行锁定读，那么它们的加锁情况和更新带有二级索引列的UPDATE语句一致。
  2. <a name="Bd0FD"></a>
  3. #### 5）使用普通二级索引进行等值查询
  5. 我们再把上边的唯一二级索引uk_name改回普通二级索引idx_name：
  6. ```sql
  7. ALTER TABLE hero DROP INDEX uk_name, ADD INDEX idx_name (name);

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

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


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

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

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

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

image.png

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

  1. 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的加锁情况类似，不过如果表中还有别的二级索引列的话，这些对应的二级索引记录也会被加锁。

6）使用普通二级索引进行范围查询

与唯一二级索引的加锁情况类似。

7）全表扫描

比方说：

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

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

image.png

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

使用SELECT … FOR UPDATE进行加锁的情况与上边类似，只不过加的是X型正经记录锁，就不赘述了。
对于UPDATE …语句来说，加锁情况与SELECT … FOR UPDATE类似，不过如果被更新的列中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。
和DELETE …的语句来说，加锁情况与SELECT … FOR UPDATE类似，不过如果表中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。

3.半一致读的语句

半一致性读是一种夹在一致性读和锁定读之间的读取方式。当隔离级别不大于读已提交且执行update语句时将使用半一致性读。所谓半一致性读，就是当update语句读取到已经被其他记录加了X锁的记录的时候，InnoDB会将该记录的最新提交版本读出来，然后判断该版本是否与update语句中的搜索条件相匹配。如果不匹配，则不对该记录加锁，从而跳到下一条记录；如果匹配，则再次读取该记录并对其进行加锁。这样处理只是为了让update语句尽量少被别的语句阻塞。

假如事务T1的隔离级别为读已提交，T1执行了下面这条语句：

  1. select * from hero where number =8 for update;

该语句在执行时对number值为8的聚簇索引记录加了X型记录锁，此时隔离级别也为读已提交的事务T2执行了如下语句：

  1. update hero set name = 'cao曹操' where number >=8 and number <20 and country !='魏'；

该语句在执行时需要依次获取number值为8，15，20的聚簇索引记录的X型记录锁。由于T1已经获取了number值为8的聚簇索引记录的X型锁，按理说此时事务T2应该由于获取不到number值为8的聚簇索引的X型记录锁而阻塞。但是由于进行的是半一致读，所以存储引擎会先获取number=8的聚簇索引记录最新提交的版本并返回server层。该版本的country =’魏’,很显然不符合country!=’魏’的条件，所以server层决定放弃获取number=8的聚簇索引记录上的X锁，转而让存储引擎读取下一条记录。

4.insert语句

insert语句在一般情况下不需要在内存中生成锁结构，并单纯依靠隐式锁保护插入的记录。不过当事务在插入一条记录前，需要先定位到该记录在B+树中的位置。如果该位置的下一条记录已经被加了意向锁，那么当前事务会为该记录加上一种类型为插入意向锁的锁，并且事务进入等待状态。

分析下在执行insert语句的时候，在内存中生成锁结构的两种特殊情况。

4.1 遇到重复键

在插入一条新记录的时候，首先要做的就是确定这条记录应该插入到B+树的哪个位置。如果在确定位置时发现现有记录的主键或者唯一二级索引与待插入记录的主键或者唯一二级索引列相同，此时会报错。

当然在生成报错信息前，其实会对聚簇索引中number值为20的记录加S锁。不过加的锁的具体类型在不同隔离级别下是不一样的：

  • 当隔离级别<=读未提交时，加的是S记录锁
  • 当隔离级别>=可重复读时，加的是S型 next-key锁

如果是唯一索引列的值重复，也会报错。不过在报错之前会为已经存在的那条记录的二级索引记录加一个S锁。不论是什么隔离级别，如果再插入新记录的时候遇到唯一二级索引列重复，都会对已经在B+树中的那条唯一二级索引记录加next-key锁。

按理来说，在读未提交&读已提交隔离级别下，不应该出现next-key锁，这主要是考虑到如果只加记录锁，可能出现多条记录的唯一二级索引列值都相同的情况。

另外，在使用insert...on duplicate key这样的语法来插入记录的时候，如果遇到的主键或者唯一二级索引列的值重复，会对B+树中已经存在的相同键值的记录加X锁，而不是S锁。

4.2 外键检查

  1. 待插入记录的外键值在主表能找到

    在插入成功之前，无论当前事务的隔离级别是什么，只需要直接给主表外键值对应的记录加一个S型记录锁即可。

  2. 待插入记录的外键值在主表找不到

    此时会插入失败，但是在这个过程中需要根据隔离级别对主表中外键的值的聚簇索引记录进行加锁：

    • 当隔离级别<=读已提交的时候，并不对记录加锁
    • 当隔离级别>=可重复读的时候，加的是意向锁。

二，查看事务加锁情况

1.获取锁信息-information_schema

在 information_schema数据库中，有几个与事务和锁紧密相关的表，具体如下。

  • INNODB_TRX:该表存储了InnoDB 存储引擎当前正在执行的事务信息，包括事务id (如果没有为该事务分配唯一的事务id 则会输出该事务对应的内存结构的指针)、事务状态(比如事务是正在运行还是在等拿待获取某个锁、事务正在执行的语句、事务是何时开启的)等。

比如我们可以在一个会话中执行事务T1:

  1. # 事务T1
  2. begin;
  4. select * from hero where number =8 for update;
number name country
8 c曹操 魏

然后再到另一个会话中查询INNODB_TRX表：

  1. select * from information_schema.INNODB_TRX\G

从执行结果可以看到，当前系统中有一个事务id = 46671的事务，他的状态为正在运行，隔离级别为可重复读。

  • innodb_locks :该表记录了一些锁信息，主要包括下面两个方面的锁信息；
    • 如果一个事务想要获得某个锁但是没获取到，则记录该锁的信息
    • 如果一个事务获取到了某个锁，但是这个锁阻塞了别的事务，则记录该锁信息

刚好刚才在事务T1中执行了一个加锁语句，现在来查询下innodb_locks：

  1. select * from information_schema.innodb_locks;

结果什么都没有！只有当系统中发生了某个事务因为获取不到锁而被阻塞的情况时，该表中才会有记录。

再到另一个会话中开启事务T2，然后执行：

  1. begin;
  3. select * from hero where number =8 for update; # 进入阻塞状态

此时再次查询innodb_locks，可以看到trx_id=46672&46671的两个事务被显示出来了，但是我们无法仅凭上述内容区分到底是谁获取到了其他事物需要的锁，以及谁因为没有获取到锁而阻塞。可以到innodb_lock_waits表查看更多信息。

  • innodb_lock_waits：表明每一个阻塞的事务是因为获取不到哪个事务持有的锁而阻塞。接着上面T2因为获取不到T1的锁而阻塞的例子，查询一下innodb_lock_waits表：
    1. select * from information_schema.innodb_lock_waits;
    | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | | —- | —- | —- | —- | | 46672 | 46672:202:3:4 | 46671 | 46671:202:3:4 |

requesting_trx_id:因为获取不到锁而被阻塞的事务的事务id blocking_trx_id：表示因为获取不到别的事务需要的锁而导致其被阻塞的事务的事务id

在本例中：requesting_trx_id = T2，blocking_trx_id=T1。

这两张表在MySQL8.0被干掉了。

2.获取锁信息-show eninge innodb status

现在假设前文使用的T1，T2事务都提交了，我们在新开启几个事务：

  1. # 可重复读隔离级别
  3. begin;
  5. select * from hero force index(idx_name) where name > 'c曹操' and name <='x荀彧' and country != '吴' order by name desc for update;
number name country
15 x荀彧 魏
1 l刘备 蜀

可以直接通过show eninge innodb status 语句获取当前系统中各个事务的加锁情况。

上述命令输出信息过多，可以使用set global innodb_status_output_locks = on;

如果某个事务没有被分配唯一的事务id，则执行SHOW ENGINE INNODB STATUS语句时并不会显示该事务在执行过程中持有的锁。比如，事务T4只执行了SELECT*FROM hero WHERE number=1 LOCK IN SHARE MODE语句，那么事务T4所持有的锁是不显示 的。另外，SHOW ENGINE INNODB STATUS 不显示隐式锁。

另外，我们在SHOW ENGINE INNODB TATUS的输出中可以看到，hero表的 number列的值都是“800000XX”的形式，这是 因为number列是存储有符号数的(也就是既可以存储负数，也可以存储非负数)，InnoDB规定在储存有符号数的时候需要将首位置为1。

三，死锁

假设我们开启了两个事物T1&T2，他们的具体执行流程如下：

发生时间编号 T1 T2
① begin
② begin
③ select * from hero where number=1 for update;
④ select * from hero where number=3 for update;
⑤ select * from hero where number=3 for update;(此操作阻塞)
⑥ select * from hero where number=1 for update;（死锁发生，记录日志，服务器回滚一个事务）

innodb有一个死锁检测机制，当它检测到死锁发生时，会选择一个较小的事务进行回滚，并向客户端发送一条消息：

  1. 尝试获取锁的时候发生死锁，重启事务。

从上面的例子可以看出：当不同的事务以不同的顺序获取某些记录的锁时，可能会发生死锁。当死锁发生时，InnoDB会回滚一个事务以释放掉该事务所获取的锁。

我们有必要找出那些发生死锁的语句，通过优化语句来改变加锁顺序，或者建立合适的索引以改变加锁过程，从而避免死锁问题。不过，在实际应用中我们可能压根儿不知道哪些语句发生了死锁，因此需要根据死锁发生时的死锁日志来逆向定位产生死锁的语句，然后再优化业务。

show engine innodb status 只会显示最近一次发生的死锁信息。如果死锁频繁出现，可以将全局系统变量innodb_print_all_deadlocks设置为ON,这样可以将每个死锁发生时的信息都记录在MySQL的错误日志中，然后就可以通过查看错误日志来分析更多的死锁情况了。

四，总结

MVCC 和加锁是解决并发事务带来的一致性问题的两种方式。

共享锁简称为S锁，独占锁简称为X锁。S锁与S锁兼容;锁与S锁不兼容，与X锁也不兼容。

事务利用 MVCC 进行的读取操作称为一致性读，在读取记录前加锁的读取操作称为锁定读。InnoDB 规定下面两种语法来进行锁定读:

  • SELECT..LOCK IN SHARE MODE 语句为读取的记录加S锁;
  • SELECT…FOR UPDATE 语句为读取的记录加X锁。

INSERT语句一般情况下不需要在内存中生成锁结构，并单纯依靠隐式锁保护插入的记录。UPDATE和DELETE语句在执行过程中，在B+树中定位到待改动记录并给该记录加锁的过程也算是一个锁定读。

IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时，可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。

InnoDB 中的行级锁类型有下面这些。

  • Record Lock:被我们称为正经记录锁，只对记录本身加锁。
  • Gap Lock:锁住记录前的间隙，防止别的事务向该间隙插入新记录。
  • Next-Key Lock: Record Lock 和Gap Lock的结合体，既保护记录本身，也防止别的事务向该间隙插入新记录。
  • Insert Intention Lock:很“鸡肋”的锁，仅仅是为了解决“在当前事务插入记录时因碰到别的事务加的gap锁而进入等待状态时，也生成一个锁结构”而提出的。某个事务获取一条记录的该类型的锁后，不会阻止别的事务继续获取该记录上任何类型的锁。
  • 隐式锁:依靠记录的trx_id属性来保护不被别的事务改动该记录。

InnoDB存储引擎的锁都在内存中对应着一个锁结构。有时为了节省锁结构，会把符合下面条件的锁放到同一个锁结构中:

  • 在同一个事务中进行加锁操作
  • 被加锁的记录在同一个页面中
  • 加锁的类型是一样的
  • 等待状态是一样的

语句加锁的情况受到所在事务的隔离级别、语句执行时使用的索引类型、是否是精确匹配、是否是唯一性搜索、具体执行的语句类型等情况的制约，需要具体情况具体分析。

可以通过information_schema 数据库下的 INNODB_TRX、INNODB LOCKS、INNODB LOCK WAITS 表来查看事务和锁的相关信息，也可以通过 SHOW ENGINE INNODB STATUS语句查看事务和锁的相关信息。

不同事务由于互相持有对方需要的锁而导致事务都无法继续执行的情况称为死锁。死锁发生时，InnoDB会选择一个较小的事务进行回滚。可以通过查看死锁日志来分析死锁发生过程。