MySQL——锁

一、解决并发事务带来的问题

1.1、【写-写】的情况

• 由于任何一种隔离级别都不允许脏写（写-写）的现象发生，所以，当多个未提交事务相继对一条记录进行改动的时候，就需要让它们排队执行。

• 这个排队的过程其实是通过为该记录加锁来实现的。这个锁本质上是一个内存中的结构。

• 【写-写】的具体操作流程如下：
  

• 【上图解释如下：】
  (1)、一开始是没有锁结构与记录进行关联的，即：上图第一个图例所示。
  (2)、当一个事务T1想对这条记录进行改动时，会看看内存中有没有与这条记录关联的锁结构，如果没有，就会在内存中生成一个锁结构与这条记录相关联，即：上图第二个图例所示。我们把该场景称之为获取锁成功或者加锁成功。
  (3)、此时又来了另一个事务T2要访问这条记录，发现这条记录已经有一个锁结构与之关联了，那么T2也会生成一个锁结构与这条记录关联，不过锁结构中的is_waiting属性值为true，表示需要等待。即：上图第三个图例所示。我们把该场景称之为获取锁失败/加锁失败。
  (4)、事务T1提交之后，就会把它生成的锁结构释放掉，然后检测一下还有没有与该记录关联的锁结构。结果发现了事务T2还在等待获取锁，所以把事务T2对应的锁结构的is_waiting属性设置为false，然后把该事务对应的线程唤醒，让T2继续执行。

1.2、 【读-写】或【写-读】的情况

• 为了避免在“读-写”或“写-读”情况下避免脏读、不可重复读、幻读现象，有如下两种可选的解决方案：
  （1）、读操作使用多版本并发控制（MVCC），写操作进行加锁。
  （2）、读、写操作都采用加锁的方式。

• MySQL与SQL标准不同的一点就是，MySQL在REPEATABLE READ隔离级别下很大程度地避免了幻读现象。

1.3、一致性读

• 一致性读/一致性无锁读/快照读
  定义：事务利用MVCC进行的读取操作。

• 所有普通的SELECT语句在READ COMMITTED或REPEATABLE READ隔离级别下都算是一致性读。比如：

  select * from student;
  
  select * from student s 
  left join address a on s.addr_id = a.id;
  

• 一致性读并不会对表中的任何记录进行加锁操作，其他事务可以自由地对表中的记录进行改动。

1.4、锁定读

• 在使用加锁的方式来解决读写问题的时候，由于既要允许读-读情况不受影响，又要使写-写或读-写情况中的操作互相阻塞，所以MySQL给锁分为以下两类：

• 共享锁（S锁）
  Shared Lock：在事务要读取一条记录时，需要先获取该记录的S锁。

• 独占锁（X锁）
  Exclusive Lock：在事务要修改一条记录时，需要先获取该记录的X锁。

• S锁和X锁的兼容关系
  

• 【上图解释如下：】

• 情况1：事务T1首先获取了一条记录的S锁
  如果事务T2也要获得这条记录的S锁，那么此时，T2是可以获得这条记录的S锁。如果事务T2要获得这条记录的X锁，那么操作会被阻塞，直到事务T1提交之后将S锁释放掉为止。

• 情况2：事务T1首先获取了一条记录的X锁
  那么无论事务T2要获得这条记录的S锁还是X锁，T2都会被阻塞，直到事务T1提交之后将X锁释放掉为止。

• 锁定读的语句
  （1）、对读取的记录加S锁

  SELECT ... LOCK IN SHARE MODE;
  

  （2）、对读取的记录加X锁

    SELECT ... FOR UPDATE;
  

1.5、写操作

• DELETE
  先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，最后再执行delete mark操作。

• INSERT
  一般情况下，新插入的一条记录受隐式锁保护，不需要在内存中为其生成对应的锁结构。

• UPDATE（分为如下3种情况）

• 第一种情况：未修改主键并且被更新的列在修改前后所占用的存储空间未发生变化。
  先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，最后在原记录的位置进行修改操作。

• 第二种情况：未修改主键并且被更新的列在修改前后所占用的存储空间发生变化。
  先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，之后将原记录彻底删除掉（即：把记录彻底移入垃圾链表），最后再插入一条新记录。

• 第三种情况：修改主键。
  相当于在原记录上执行DELETE操作之后再来一次INSERT操作。加锁操作就需要按照DELETE和INSERT的规则进行了。

二、多粒度锁

2.1、行级锁/行锁

• 以上所提到的锁都是针对记录的，可以称为行级锁/行锁；对一条记录加行锁，影响的只是该行记录而已，所以行锁的粒度比较细。

2.2、表级锁/表锁

• 如果一个事务在表级别进行加锁，就称为表级锁/表锁。它会影响表中的所有数据，锁的粒度比较粗。

2.3、表锁的划分

• 表锁也可以分为共享锁（S锁）和独占锁（X锁）。
• case1：一个事务给表加了S锁。
  （1）、 其他事务可以继续获得该表/该表中的某些记录的S锁。
  （2）、其他事务不可以继续获得该表/该表中的某些记录的X锁。
• case2：一个事务给表加了X锁
  （1）、其他事务不可以继续获得该表/该表中的某些记录的X锁或S锁。
• 每当要对表上S锁的时，需要表中的记录和表没有X锁；当要对表上X锁的时候，需要表中的记录和表即没有X锁也没有S锁。
• 表上的锁比较好判断，记录上的锁怎么判断呢？总不能一行一行的来判断是不是有X锁或者S锁把。那么，为了解决这个问题，InnoDB提出了意向锁的概念。即：
  （1）、意向共享锁（IS锁）：ntention Shared Lock：当事务准备在某条记录上加S锁时，首先需要在表级别加一个IS锁。
  （2）、意向独占锁（IX锁）：Intention Exclusive Lock：当事务准备在某条记录上加X锁时，首先需要在表级别加一个IX锁。
• IS锁和IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录；也就是说，其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的，IS和IS也是兼容的。兼容性关系如下所示：
  

三、MySQL中的行锁和表锁

3.1、其他存储引擎中的锁

• 对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁，而且这些存储引擎并不支持事务。

3.2、 InnoDB存储引擎中的锁

• InnoDB存储引擎既支持表级锁也支持行级锁。

3.2.1、InnoDB中的表级锁

3.2.1.1、S锁、X锁

• InnoDB存储引擎提供的表级S锁或者X锁相当“鸡肋”，在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的，只会在一些特殊情况下（比如系统崩溃恢复时）用到。
• 在对某个表执行DDL语句时，其他事务在对这个表并发执行DML语句时，会发生阻塞；反之亦然。这个过程其实是通过在server层使用一种称为元数据锁（Metadata Lock，MDL）的东西来实现的，也不会使用S锁和X锁。
• DDL语句在执行时会隐式提交当前会话中的事务。 因为DDL语句的执行一般都会在若干个特殊事务中完成。在开启这些特殊事务前，需要将当前会话中的事务提交掉。
• 虽然表级S锁或X锁相当鸡肋，不过我们还是可以手动获取一下的，比如在系统变量autocommit=0、innodb_table_lock=1时，可以按照下面来写语句：
  （1）、LOCK_TABLES t READ：对表t加表级别的S锁。
  （2）、LOCK_TABLES t WRITE：对表t加表级别的X锁。

3.2.1.2、IS锁、IX锁

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

3.2.1.3、AUTO-INC锁

• 系统自动给AUTO_INCREMENT修饰的列进行递增赋值的实现方式主要有下面两个：
• AUTO-INC锁
  （1）、执行插入语句时就加一个表级别的AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值。
  （2）、在该语句执行结束后，再把AUTO-INC锁释放掉。这样一来，一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞。
  （3）、AUTO-INC锁的作用范围只是单个插入语句，在插入语句执行完成后，这个锁就被释放了。
• 轻量级锁
  （1）、在通过AUTO_INCREMENT获得修饰的列的值时获取这个轻量级锁，就把该轻量级锁释放掉，而不需要等到整个插入语句执行完后才释放锁。
• innodb_autonic_lock_mode系统变量，用来控制到底使用上述两种方式中的哪一种
  （1）、innodb_autonic_lock_mode=0 表示一律采用AUTO_INC锁。
  （2）、innodb_autonic_lock_mode=2 表示一律采用轻量级锁。
  （3）、innodb_autonic_lock_mode=1 表示两种方式混着来，即：插入记录的数量确定时采用轻量级锁，不确定时采用AUTO-INC锁。其中，不确定插入记录数量的情况。例如：INSERT…SELECT、REPLACE…SELECT、LOAD DATA。

3.2.2、InnoDB中的行级锁

• 行级锁，也称为记录锁，顾名思义就是在记录上加锁。
• 不过行锁也根据不同的类型分为了多种。也就是说，即使对同一条记录加行锁，如果记录的类型不同，起到的功效也是不同的。

3.2.2.1、Record Lock

• 官方名称：LOCK_REC_NOT_GAP，也称为记录锁：也就是仅仅负责把一条记录锁上的锁。
• 记录锁也分为：S型记录锁和X型记录锁。如下图所示：
  

3.2.2.2、Gap Lock

• 官方名称：LOCK_GAP，也称为gap锁：锁住了指定的记录以及记录前面的间隙，防止其间插入新记录。
• gap锁的提出仅仅是为了防止插入幻象记录（即：幻读现象）而提出的。如下图所示：
  
• 【上图解释】
• 意味着不允许别的事务在no值为5的记录前面的间隙插入新记录，即：no列的值在区间（3，5）的新记录是不允许立即插入的，当gap锁释放才可以插入。
• 如何锁定no值为5之后的记录呢？
  为Supremum记录加一个gap锁，则可以阻止其他事务插入no值在区间（5, +∞）的新纪录。

3.2.2.3、Next-Key Lock

• 官方名称：LOCK_ORDINARY，也称为next-key锁：本质就是一个记录锁+gap锁的合体。它既能保护该条记录，又能阻止别的事务将新纪录插入到被保护记录前面的间隙中。如下图所示：
  

3.2.2.4、Insert Intention Lock

• 官方名称：LOCK_INSERT_INTENTION，也称为插入意向锁：事务在等待时也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在处于等待状态。如下图所示：
  

• 【上图解释】

• type属性，用来表明该锁的类型。
  （1）、由于T1持有no=9的gap锁（即：no等于5~9之间不能插入记录），所以T2和T3分别想插入no=6和no=7的两条记录时会生成插入意向锁的锁结构并且处于等待状态。
  （2）、 当T1提交后会把gap锁释放掉，这时候，T2和T3之间也并不会相互阻塞，他们可以同时获取到number值为9的插入意向锁，然后执行插入操作。

• 事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁，就是这么鸡肋。

3.2.2.5、隐式锁

• 一般情况下，执行INSERT语句是不需要在内存中生成锁结构的。

• 但是也会有例外，比方说：一个事务首先插入了一条记录 (此时并没有与该记录关联的锁结构），然后另一个事务执行如下操作：
  （1）、立即使用 SELECT… LOCK IN SHARE MODE 语句读取这条记录 （也就是要获取这条记录的S锁），或者使用 SELECT … FOR UPDATE 语句读取这条记录（也就是要获取这条记录的X锁），该咋办？如果允许这种情况的发生，那么可能出现脏读现象。
  （2）、立即修改这条记录（也就是要获取这条记录的X锁），该咋办？如果允许这种情况的发生，那么可能出现脏写现象。

• 解决办法：使用事务id，我们把聚簇索引和二级索引中的记录分开看一下
  （1）、对于聚簇索引
  有一个trx_id隐藏列，该隐藏列记录着最后改动该记录的事务id。在当前事务中新插入一条聚簇索引记录后，该记录的trx_id隐藏列代表的就是当前事务的事务id。如果其他事务此时想对该记录添加S锁或者X锁，首先会看一下该记录的trx_id隐藏列代表的事务是否是当前的活跃事务。如果不是的话就可以正常读取：如果是的话，那么就帮助当前事务创建一个X锁的锁结构，该锁结构的is_waiting属性为false：然后为自己也创建一个锁结构，该锁结构的is_ waiting属性为true，之后自己进入等待状态。
  （2）、对于二级索引
  本身并没有trx_id隐藏列，但是在二级索引页面的Page Header 部分有一个PAGE_MAX_TRX_ID属性，该属性代表对该页面做改动的最大的事务id。如果PAGE_MAX_TRX_ID属性值小于当前最小的活跃事务id，那就说明对该页面做修改的事务都己经提交了，否则就需要在页面中定位到对应的二级索引记录，然后通过回表操作找到它对应的聚筷索引记录，然后再重复情景1的做法。

• 综上所述，隐式锁起到了延迟生成锁结构的用处。即：一般情况不生成隐式锁，如果发生上述冲突的锁操作，则采用隐式锁结构来保护记录。

3.3 、InnoDB锁的内存结构

• 以上已经介绍过，对一条记录加锁的本质就是在内存中创建一个锁结构跟这条记录相关联，那么如果我们在操作一个事务的时候，对应多条记录的时候，是不是要针对多条记录生成多个内存的锁结构呢？比如我们执行select * from tb_user for update的时候，tb_user表中如果存在1万条数，那么难道要生成1万个内存的锁结构吗？那当然不会是这样的。其实，如果符合以下几个条件，那么这些记录的锁就可以放到一个内存中的锁结构里了，条件如下所示：
  （1）、加锁操作时在同一个事务中。
  （2）、需要被加锁的记录在同一个页中。
  （3）、需要加锁的类型是一致的。
  （4）、锁的等待状态是一致的。

• 那么这么多次的锁结构，它到底是怎么组成的呢？
  主要是由6部分组成的。分别为：锁所在的事务信息、索引信息、表锁或行锁信息、type_mode、其他信息、与heap_no对应的比特位。如下图所示：
  - 【上图解释】

• 锁所在的事务信息
  一个锁结构对应一个事务，那么这里就存储着锁对应的事务信息。它其实只是一个指针，可以通过它获取到内存中关于该事务的更多信息，比如：事务id是多少。

• 索引信息
  对于行级锁来说，这里记录的就是加锁的记录属于哪个索引。

• 表锁/行锁信息
  (1)、对于表锁，主要是来记录对哪张表进行的加锁操作以及其他的信息。
  (2)、对于行锁，内容包括3部分：
  Space ID：记录所在的表空间ID。
  Page Number：记录所在的页号。
  n_bits：一条记录对应一个bit，那么当我们对多条记录进行加锁操作的时候，就会对应多个bit，那么这个值就是用来记录有多少个bit的，而具体哪条记录对应哪个bit，是在【与heap_no对应的比特位】这块内容中有mapping映射的。但是，大家需要注意的是，并不是有多少条记录n_bits的值就是多少。为了之后在页面中插入新记录的时候也不至于重新分配锁结构，n_bits的值一般都比页面中的记录条数多一些。

• type_mode
  它是由32个bit组成的，分别为：lock_mode、lock_type、lock_wait和rec_lock_type，如下图所示：
  

• 其他信息
  为了更好的管理系统运行过程中生成的各种锁结构，而设计了各种哈希表和链表。

• 与heap_no对应的比特位
  如果是行级锁，会通过这部分的比特位来对应n_bit属性的值。在每条记录的头信息中保存一个叫做heap_no的属性，它是用来表示记录在堆中的相对位置的。即：Infimum的heap_no为0，Supermum的heap_no为1，然后插入记录的heap_no依次类推，每次加一。那么，这个所谓的“与heap_no对应的比特位”就是一个bit与heap_no的对应关系。以n_bit=16为例，如下所示：
  

• 示例

  假设要开启一个事务T1，往tb_user表（已存在5条记录）中表空间为67，页号为3的页面上，插入一个number=15的记录（number是主键），并位这个记录加S锁，那么我们分析一下它所生成的行级锁结构是怎么样的？

• 由于开启的是事务T1，所以【锁所在的事务信息】指的就是T1这个事务。

• 由于要直接对number这个聚簇索引加锁，所以【索引信息】值的就是PRIMARY索引。

• 由于是行级锁，所以在【表锁/行锁信息】中【Space ID】等于67，【Page Number】等于3，【n_bits】等于72
  其中，n_recs包含伪记录（Infimum和Supermum）共2条记录和正常记录5条记录，共7条记录。那么根据上面的公式计算，得出如下结果 n_bits=(1+((7+64)/ 8))*8=72。

• 【type_mode】由四部分组成，其中lock_mode=LOCK_S=2，lock_type=LOCK_REC=32，lock_wait=NOT_WAITING=0，rec_lock_type=LOCK_REC_NOT_GAP=1024;那么组合在一起就是 type_mode=2|32|0|1024=1058。

• 【其他信息】不做重点讨论。

• 【与heap_no对应的比特位】，因为之前已经存在5条记录了，所以number=15对应的no_heap=7，它对应的bit位，如下所示：
  

• 综上所述：锁结构为如下所示：
  

四、语句加锁分析

示例： tb_user表的表结构和表中存在的数据如下图所示。

• 【上图解释】
• 在tb_user表中，有三个字段，分别为学号（number），学生姓名（name）和学生年龄（age）这三个字段。那么其中number字段为聚簇索引字段，name字段为二级索引字段。
• 针对语句加锁分析，我们根据以下4类进行分析，分别为：
  （1）、普通的SELECT语句。
  （2）、 锁定读的语句。
  （3）、半一致性读的语句。
  （4）、INSERT语句。

4.1、普通的SELECT语句

• 普通的select语句其实针对于不同的隔离级别，会有不同的处理方式。如下表所示：

4.2、锁定读的语句

4.2.1、 流程概述

• 针对锁定读的语句，其实可以归类为以下四种语句：
  （1）、语句1：SELECT … LOCK IN SHARE MODE;
  （2）、语句2：SELECT … FOR UPDATE;
  （3）、语句3：UPDATE …
  （4）、语句4：DELETE …

• 解释：因为语句3和语句4在操作update和delete之前，都要隐式的去查找相应的数据，所以也可以认为是一种锁定读。

• 锁定读的过程如下所示：

• 步骤1：快速在B+树中定位到该扫描区间（即select的查询区间）中的第一条记录，把该记录作为当前记录。

• 步骤2：根据不同的隔离级别，为当前记录加不同类型的锁
  

• 步骤3：判断索引条件下推（ICP：Index Condition Pushdown）的条件是否成立。如果符合索引条件下推，则执行步骤4，否则，则获取记录所在的单向链表的下一条记录，并做为新的记录，跳到步骤2继续执行。另外，本步骤还会判断当前记录是否符合扫描区间的边界条件，如果超出了扫描边界，则跳过步骤4和步骤5，直接向server层返回查询完毕。注意，步骤3不会释放锁。

• ICP：只适用于二级索引，且只适用于select语句。它是用来把查询中与被使用索引有关的搜索条件下推到存储引擎中去判断，而不是返回到server层再去判断。ICP只是为了减少回表次数，也就是减少读取完整的聚簇索引记录的次数，从而减少I/O操作。

• 步骤4：执行回表操作，获取到对应的聚簇索引记录，并加锁。

• 步骤5：判断边界条件是否成立，如果还在边界内，则执行步骤6，否则，如果隔离级别为READ UNCOMMITTED或READ COMMITTED，则要释放掉加在该记录上面的锁，如果隔离级别为REPEATABLE READ或SERIALIZABLE，则不去释放记录上面的锁。

• 步骤6：server层判断其余搜索条件是否成立。如果不满足搜索条件，也要像步骤5中描述的那样，根据不同的隔离级别来确定对当前记录是否加锁or释放锁。

• 步骤7：获取当前记录所在单向链表的下一条记录，并跳到步骤2。

4.2.2、 SELECT … LOCK IN SHARE MODE示例

那么针对上面的步骤描述，我们通过几个示例的演示，加深一下上面步骤的理解。

【示例一】针对聚簇索引number作为搜索条件，隔离级别为READ UNCOMMITTED或READ COMMITTED，执行select * from tb_user where number >2 AND number <=7 AND age=25 LOCK IN SHARE MODE;

• 【步骤】
• 步骤1：首先扫描区间为(2,7]中的第一条记录，即：number=3。
• 步骤2：为number=3的记录加S行的记录锁。
• 步骤3：由于查询条件为聚簇索引，所以不符合ICP。
• 步骤4：由于查询条件为聚簇索引，所以不需要回表。
• 步骤5：扫描区间为(2,7]，当前区间为number=3，符合扫描区间
• 步骤6：server层判断number=3记录上面的其他条件，它的age=11，不满足查询条件，所以释放掉该记录上的锁。
• 步骤7：获取number=3记录所在单向链表的下一条记录，即：number=5，继续执行步骤2的操作，下面针对其他number的操作就不在赘述了。最终加锁结果如下图所示：
  

、

【示例二】针对聚簇索引number作为搜索条件，隔离级别为REPEATABLE READ或SERIALIZABLE，执行select * from tb_user where number >2 AND number <=7 AND age=25 LOCK IN SHARE MODE;

• 示例二与示例一的区别只在于隔离级别上。那么从上面我们介绍步骤原理的时候，也说过，如果是READ COMMITTED或SERIALIZABLE的隔离级别的话，如果不满足条件是不会解锁的。所以，我们具体步骤就不再赘述了，可以参照实例一中的具体步骤，我们就来看一下加锁情况变成了怎样？
  

  【示例三】针对二级索引name作为搜索条件，隔离级别为READ UNCOMMITTED或READ COMMITTED，执行select * from tb_user FORCE INDEX(idx_name) where name >= ‘rose’ AND name <= ‘john’ AND age =20 LOCK IN SHARE MODE;

• 【步骤】

• 步骤1：首先扫描区间为[‘rose’,‘john’]中的第一条记录，即：name=‘rose’。

• 步骤2：为name='rose’的二级索引记录加S型的记录锁。

• 步骤3：由于查询条件为二级索引，所以符合ICP。

• 步骤4：执行回表操作，找到相应的聚簇索引记录，也就是number=9，然后为该聚簇索引记录加一个S型的记录锁。

• 步骤5：扫描区间为[‘rose’,‘john’]，当前区间为name=‘rose’，符合扫描区间

• 步骤6：server层判断number=9记录上面的其他条件，它的age=11，不满足查询条件，所以释放掉该记录在二级索引和聚簇索引上的锁。

• 步骤7：获取name='rose’记录所在单向链表的下一条记录，即：name=‘john’，继续执行步骤2的操作，下面针对其他name的操作就不在赘述了。最终加锁结果如下图所示：
  

  【示例四】针对二级索引name作为搜索条件，隔离级别为REPEATABLE READ或SERIALIZABLE，执行select * from tb_user FORCE INDEX(idx_name) where name >= ‘rose’ AND name <=‘john’ AND age=20 LOCK IN SHARE MODE;

  • 示例四与示例三的区别只在于隔离级别上。那么从上面我们介绍步骤原理的时候，也说过，如果是READ COMMITTED或SERIALIZABLE的隔离级别的话，如果不满足条件是不会解锁的。所以，我们具体步骤就不再赘述了，可以参照实例三中的具体步骤，我们就来看一下加锁情况变成了怎样？
    

4.2.3、 SELECT … FOR UPDATE示例

• SELECT … FOR UPDATE语句的加锁过程与SELECT … LOCK IN SHARE MODE语句类似，区别是为记录加X锁。

4.2.4、 UPDATE … 示例

• 加锁方式与SELECT … FOR UPDATE语句加锁类似。只不过如果更新了二级索引列，那么所有被更新的二级索引记录在更新之前都需要加X型记录锁。

【示例一】隔离级别为READ UNCOMMITTED或READ COMMITTED，执行update tb_user set name = ‘unknown’ where number >2 AND number <=7 AND age<25;

• 【上图解释】
• 由于更新了name列，而name列又是一个索引列，所以在更新前也需要为idx_name二级索引中对应的记录加锁。

【示例二】隔离级别为REPEATABLE READ或SERIALIZABLE，执行update tb_user set name = ‘unknown’ where number >2 AND number <=7 AND age<25;

4.2.5、DELETE … 示例

• 与UPDATE的处理方式相同，当表中包含二级索引，那么二级索引记录在被删除之前都需要加X型记录锁。

4.2.6、 补充说明

• 对于UPDATE和DELETE语句来说，在对被更新或者被删除的二级索引记录加锁的时候，实际上加的是隐式锁，但是效果与X型记录锁一样。
• 对于隔离级别为READ UNCOMMITTED和READ COMMITTED的情况，采用的是一种称为半一致读的方式来执行UPDATE语句。

4.2.6.1、二级索引精准匹配加锁流程

• 当隔离级别为READ UNCOMMITTED和READ COMMITTED的情况，如果匹配的模式为精准匹配，那么将不会为扫描区间后面的下一条记录加锁。比如我们执行select * from tb_use where name=‘xz’ for update。那么加锁情况如下所示：
  
• 当隔离级别为REPEATABLE READ或SERIALIZABLE的情况，如果匹配的模式为精准匹配，那么会为扫描区间后面的下一条记录加gap锁。比如我们执行select * from tb_use where name=‘xz’ for update。那么加锁情况如下所示：
  

4.2.6.2、二级索引找不到记录

• 当扫描区间中没有记录，且为精确查找，隔离级别为REPEATABLE READ或SERIALIZABLE，那么也要为扫描区间后面的下一条记录加一个gap锁。比如执行：select * from tb_user where name=‘moon’ FOR UPDATE。如下所示：
  
• 当扫描区间中没有记录，且不是精确查找，隔离级别为REPEATABLE READ或SERIALIZABLE，那么也要为扫描区间后面的下一条记录加一个next-key锁。比如执行：select * from tb_user where name>‘m’ and name<‘j’ FOR UPDATE。如下所示：
  

4.2.6.3、左闭区间加锁

• 当隔离级别为REPEATABLE READ或SERIALIZABLE，使用聚簇索引，并且扫描区间为左闭区间，如果定位到的第一个聚簇索引记录的number值正好与扫描区间中最小的值相同，那么会为该聚簇索引记录加X类型的记录锁。例如：select * from tb_user where number>=3 FOR UPDATE；加锁情况如下所示：
  

4.2.6.4、自右向左扫描加锁

• 当隔离级别为REPEATABLE READ或SERIALIZABLE，从右向左的顺序扫描记录，会给匹配到的第一条记录的下一条记录加gap锁。例如：select * from tb_user where name >=‘tom’ and name <= ‘john’ and age=20 order by name DESC FOR UPDATE;
  
• 【上图解释】
• 在tony记录加gap锁，目的是为了防止有name='john’的记录插入。

4.3、 半一致性读的语句

• 当隔离级别为READ UNCOMMITTED或READ COMMITTED且执行UPDATE语句时，将会使用半一致读。
• 那么什么是半一致读呢？
• 就是当UPDATE语句读取到已经被其他事务加了X锁的记录时，InnoDB会将该记录的最新提交版本读出来，然后判断该版本是否与UPDATE语句中的搜索条件相匹配。如果不匹配，则不对该记录加锁，从而跳到下一条记录；如果匹配，则再次读取该记录并对其进行加锁。这样做的目的就是让UPDATE语句尽量少被别的语句阻塞。
• 比如tb_user(number, name, age)表中有三条记录，分别为(1, ‘a’, 10),(2, ‘b’, 20),(3, ‘c’, 30)。那么id=1被T1事务锁住了，当我们在事务T2中执行update … where number>=1 and number<=3 and age > 10的时候，会先判断被锁住的这个id=1的记录age是不是大于10，我们发现这条记录的age等于10，所以不满足update的where条件，所以就无需等待，继续去更新id为2和3。

4.4、 INSERT语句

• insert语句在一般情况下不需要在内存中生成锁结构，只是单纯依靠隐式锁保护插入的记录。
• 不过在当前事务插入一条记录之前，需要先定位该记录在B+树中的位置。如果该位置的下一条记录已经被加了gap锁或next-key锁，那么当前事务就会为该记录加上插入意向锁，并且事务进入等待状态。
• 下面介绍在执行insert语句时，会在内存中生成锁结构的两种特殊情况。

4.4.1、 重复键

• 当插入记录的主键与已存在的主键列值重复的时候，会引发插入报错。但是在报错之前，会对该主键值加S锁操作，具体如下所示：
  （1）、当隔离级别为READ UNCOMMITTED或READ COMMITTED时，加S型记录锁；
  （2）、当隔离级别为REPEATABLE READ或SERIALIZABLE时，加S型next-key锁；

• 如果与唯一二级索引重复，那么无论是什么隔离级别，都会对已经存在的B+树中的那条唯一二级索引记录加next-key锁。

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

4.4.2、 外键检查

• 待插入记录的外键在主表中能找到
  在插入成功之前，无论当前事务的隔离级别是什么，只需要直接给主表对应的那条记录加S型记录锁即可。

• 待插入记录的外键在主表中找不到
  (1)、当隔离级别为READ UNCOMMITTED或READ COMMITTED时，并不对记录加锁；
  (2)、当隔离级别为REPEATABLE READ或SERIALIZABLE时，对主表查询不到的那个键值附近加gap锁；