Mysql锁理论 & 加锁实战

前言：

由于在上篇文章记一次线上间隙锁引发的死锁问题 中，讲到了间隙锁，Next-key lock和记录锁，所以我觉得有必要搞一个锁的文章，于是有了这篇长文（本文比较长，需要一定耐心）。

提起锁我想很多开发同学都不陌生，不管是工作还是面试中，锁都是一个绕不开的知识点。今天我们不讨论Java的锁，而只专注Mysql的锁。

本文将会 理论+实践 来分析演示下mysql都有哪些锁 ，以及在不同场景下mysql是如何加锁的。从而提升我们 对mysql锁的认知，使我们在开发+面试+排查问题时做到：得心应手 -> 手到擒来 -> 来者不拒 -> 拒绝bug ~~ ，哈哈~

开篇前的说明：

好，下边进入正文儿了~~

1、表锁

说起表锁，很明显，就是作用在某个表上的锁，一点毋庸置疑。同时我们要知道表锁粒度比行锁大，并发性不如行锁。
因为我们要实操，所以先建个表锁测试表（注：这个表仅仅用于表锁测试，后续的行锁演示会新建其他表），DDL如下：

create table mysql_table_lock_test
(
    id         bigint auto_increment comment '主键'
        primary key,
    name       varchar(200)                       not null comment '姓名',
    age        int                                not null comment '年龄',
    sex        int                                not null comment '性别 0女1男',
    createTime datetime default CURRENT_TIMESTAMP not null comment '创建时间',
    updateTime datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
)
    comment '表锁测试';

接下来我们看下都有哪些 表锁呢？

1.1、表级别的S/X锁

注：(mysql_table_lock_test表是我提前建好的)。

给表添加表级别 （S/X锁）的场景基本只有一个，就是使用语句 lock tables tableA read/write;时才会添加表级别的S/X锁。 注：（read对应的是表级别的S共享锁，write对应的是表级别的X排他锁）。下边我们演示下：

1.1.1 获取表级S/X的方式

获取表级别的S锁 ：lock tables mysql_table_lock_test read;

获取表级别的X锁 ：lock tables mysql_table_lock_test write;

1.1.2 表级 共享锁S & 排他锁X 特点总结

• read(S)锁：
  • 假设t1时刻，线程1持有表a的 表级别的 read(即S)锁，则有如下现象：
    • 线程1可以读a表，(但不能写a表)。
    • 其他线程可以同时获取a表的READ(即S)锁。
    • 其他线程可以在不获取read(S)锁的情况下读取a表。
    • 其他会话如果尝试获取a表的 write(即X)锁，则会被阻塞。
      

      	线程1	线程2
      时刻t1	持有表a的表级别read(S)锁	可 持有表a的表级别read(S)锁
      读 【表a】	允许	允许
      写 【表a】	不允许	不允许

• write(X)锁：
  • 假设t2时刻，线程1持有表a的 表级别的 write(即X)锁，则有如下现象：
    • 线程1可以读/写a表。
    • 其他线程不可以获取a表的S/X锁(注意是阻塞，不是报错)。
      

      	线程1	线程2
      时刻t2	持有表a的表级别write(X)锁	不可持有表a的表级别write(X)锁
      读 【表a】	允许	不允许
      写 【表a】	允许	不允许

• 表级别S/X锁的 注意事项：
  • 1、如果线程1持有表a的表S/X锁,那么他只能访问表a 不能访问未持有表锁的其他表。
  • 2、你lock tables xx read/write;时候的xx如果是表名那么在读写时候也访问表名，如果是别名那么在读写时候也访问别名，不能不一致。
    eg:

    mysql> LOCK TABLES t READ; 
    mysql> SELECT * FROM t AS myalias; 
    ERROR 1100: Table 'myalias' was not locked with LOCK TABLES
    

• 释放表级别的S/X锁
  • unlock tables; （释放锁比较简单不过多解释了）

事实上，此类 表级别的S/X锁 很少或者说极少用到），可能有些特殊场景比如说 崩溃恢复数据 过程中可能会用到，所以我们这里不过多展开了,如有兴趣请移步mysql官网表锁章节: lock-tables

1.2、意向锁（IS意向共享锁 & IX意向排他锁）

意向锁名称解释：
intention shared lock （意向共享锁简称：IS），intention exclusive lock （意向排他锁简称：IX）。

关于意向锁，首先 我们要知道的几个事情：

意向锁存在的意义：

我们知道，很多情况下表中数据比较多或者说很多，而如果没有IS/IX，那么在加表级别的S/X锁时，就需要遍历表中所有行（因为锁表时必须要等待行上的锁释放掉才可以锁表成功），这样非常慢效率也低。而有了IS/IX，在加表级别的S/X锁时，只需要判断是否存在IS/IX锁即可，不用遍历全表记录了。简而言之：IS锁和IX锁的使命只是为了在加 表级别的S锁和X锁时判断表中是否有已经被加锁的记录，从而避免了遍历操作，如此而已。

意向锁的规则（重要）：

1、在事务获得表中某一行的共享锁S之前，它必须首先获得该表上的IS(该表的意向共享锁)。
2、在事务获得表中某一行的排他锁X之前，它必须首先获得该表上的IX(该表的意向排他锁)。

注意：

意向锁只是作用于表上，行上是没有意向锁这个东西的

意向共享锁(IS)和意向独占锁(IX)不会和行级锁S/X 发生冲突，而且 意向锁之间也不会发生冲突 ，意向锁只会和表级共享锁S（lock tables ... read）和表级排他锁X（lock tables ... write）发生冲突,接下来我们看下这个表格，囊括了各个 表级锁之间的兼容性：

注意下边表格单纯是表级锁兼容关系，和行级锁没有一点关系，表中X代表 表级别X锁，S代表 表级别S锁,IS代表意向共享锁，IX代表意向排他锁。

从以上表格可以看出

• 意向读锁(IS)和意向写锁(IX)并不会发生冲突也就是说 意向锁 IS/IX 之间根本不会冲突(重要)。
• 意向读锁IS: 和 IX,S,IS都是兼容的，只是和X冲突
• 意向写锁IX：和 IS,IX都是兼容的，和 X,S 不兼容

好了关于意向锁我们暂且讨论这些，上边我们说的 意向锁的规则 以及 意向锁和表级S/X锁之间的兼容性 是比较重要的知识，需要了解并记住。

1.3、元数据类型的表锁

在对某个表执行一些（ALTER TABLE、DROP TABLE）这类的DDL语句时，其他事务对这个表并发执行的（SELECT、INSERT、DELETE、UPDATE）这类语句会发生阻塞，同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。
这个过程其实是通过在server层使用一种称之为元数据锁（Metadata Locks，简称MDL）来实现的。

关于元数据锁，暂且了解到这里。如果有兴趣，更多请见官网对他的描述： metadata-locking （里边有一些MDL加锁顺序，MDL锁释放等内容）。

1.4、AUTO-INC

在mysql中，我们一般都对主键id使用AUTO_INCREMENT修饰，以确保他是递增的。
而实现这种递增的效果的功臣之一就包含：AUTO-INC锁，理解起来也很简单，大概原理如下：

每次插入时在表级别加一个AUTO-INC锁，，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。正如mysql官网对他的描述一样：

AUTO-INC锁是一种特殊的表级锁，用于在具有AUTO_INCREMENT列的表中插入事务。在最简单的情况下，如果一个事务正在向表中插入数据，那么其他事务对该表的插入行为将阻塞（直到持有AUTO-INC锁的事务释放AUTO-INC锁），从而保证主键值连续。

从 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增，无需上个插入操作完成即释放锁，这种被称为轻量级锁。

轻量级锁大概如下：
在插入数据时，给 被AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，随后就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

这个AUTO-INC不是重点，我们简单了解有这么个东西就行了。

2、B+Tree数据结构【重要】

在讲解行锁之前，我们 有必要，很有必要，非常有必要 说一下MySql的数据结构，知道MySql内部是如何存储的以及不同索引长啥样，这样后续分析锁时才会事半功倍取得好效果。心中有图，永远不慌~~~哈哈哈。

2.1、【重要】主键索引B+Tree示意图：

• 主键索引（也称聚簇索引）特点是叶子节点存放全表的完整数据（包含全部字段）所以根据主键值查询时无需
  回表。
• 另外需要注意：聚簇索引的数据存储是根据主键id的大小来排序的（左边小，右边大），换句话说：（逻辑上的下一页）的主键id总是大于（逻辑上的上一页）的主键id。
• 具体结构和细节请见下图：
  

2.2、【重要】普通二级索引B+Tree示意图：

首先给t表 name列 添加一个普通二级索引：alter table t add index idx_name (name);

• 普通二级索引（注意不是唯一索引）特点是叶子仅包含索引列和主键id，想查询索引列和id之外的其他列数据，需要回表去聚簇索引查询剩余全部字段。
• 具体结构请见下图：
  

关于以上两张图，不再过多解释，不清楚的需要补充下索引 和 B+tree相关的知识了。要时刻记住MySql的B+Tree 数据存储结构和索引特征 ，这个知识点很重要！可以说是学习 索引，锁，事务以及其他MySql相关技术的 基石 ！

记住那句话： 心中有图，永远不慌~~~ ，接下来进入本文的重点：行锁~~~

3、行锁

行锁顾名思义就是作用在 （单/多） 行上的锁，行锁很明显比表锁粒度要细，所以并发度也更高些，同时有个很重要的点就是行锁是锁的索引记录(我们一定要对mysql的b+tree树有深入的理解至少你知道长啥样，知道mysql数据是如何存储，如何分布的，否则很多地方你都不易理解)，如果被操作的列上没有索引，行锁将退化为表锁。另外要知道，行锁会出现死锁，而表锁不会，粒度越粗的，互斥性越好把握，粒度越细的，越有可能同时被不同事务持有。同时行锁也必然更复杂，出现问题的几率也越高，但是也解决了很多重要问题。

行锁有一些 特征 我们有必要先说一下，这样更容易理解后续说的一些内容~~

• 不同隔离级别下加的行锁不一样：

  • mysql中不同隔离级别下行级锁是不一样的，比如在读已提交隔离级别（Read uncommitted）下，就没有间隙锁了。而在可重复读隔离（Repeatable read）级别下，就存在 临键锁(Next-Key lock)，记录锁(Record lock)，间隙锁(Gap lock) 这三种行锁
    

    说明：由于我们实际中用可重复读隔离级别较多，同时可重复读也是Mysql默认的隔离级别，所以我们本文 仅仅分析可重复读隔离级（Repeatable read）别下的场景，关于读已提交(Read committed)，读未提交（Read uncommitted），串行化（Serializable）这三个隔离级别我们直接忽略不讨论。

• 行锁是基于索引的：

  • 另外一个我们要一定清楚的事情就是：行锁是（基于或者说锁定的是）索引记录，并不是行上的所有记录数据，这一点一定要清楚，下面我们每次讲的给xx行加上锁这类的描述,锁定的对象都是索引记录，而非该行的全部数据。

• 行锁也有排他（X）和共享(S) 两种属性：

  • 行锁属性分两类（事实上表锁也是）：排他与共享，我们看下常用的crud语句都是加的什么锁？

    select * from t where xxx=yyy lock in share model;
    select * from t where xxx=yyy for update;
    insert into t values (…);
    update t set ? where ?;
    delete from t where ?;
    

  • 以上sql除了第一条语句，对读取的记录加 S 锁 (共享锁)外，其他的操作，都加的是 X 锁 (排它锁)。

• 行锁在结果集中的表示：

  • 在我们使用 SELECT * FROM performance_schema.data_locks;
    查看锁信息时，会有个LOCK_MODE字段，该字段可以看出加的是什么类型的行锁，
    • 如果 LOCK_MODE 为 X，说明是 X 型的 next-key 锁(也叫临键锁)
    • 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是 X 型的记录锁；
    • 如果 LOCK_MODE 为 X, GAP，说明是 X 型的间隙锁；

    当然也有S型的记录锁，间隙锁，next-key lock。

• 行锁兼容性总结：

  • 注意： (此表格不包含间隙锁，间隙锁比较特殊，下文会讲)
    

    行锁兼容性总结	X	S
    X	不兼容	不兼容
    IX	不兼容	兼容

接下来我们就说说： 什么是记录锁，什么是间隙锁，什么是 next-key lock临键锁。

3.1、行级锁分类

• 准备工作：

  为了演示几种行锁，我先建个表并搞点数据进去：

  create table mysql_row_lock_test
  (
      id         bigint auto_increment comment '主键'
          primary key,
      name       varchar(200)                       not null comment '姓名',
      age        int                                not null comment '年龄',
      sex        int                                not null comment '性别 0女 1男',
      createTime datetime default CURRENT_TIMESTAMP not null comment '创建时间',
      updateTime datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
  )
      comment '行锁测试表';
      
      //插入点数据
      INSERT INTO mysql_row_lock_test (id, name, age, sex, createTime, updateTime) VALUES (1, 'a张无忌', 28, 1, now(), now());
      INSERT INTO mysql_row_lock_test (id, name, age, sex, createTime, updateTime) VALUES (2, 'b赵敏', 19, 0, now(), now());
      INSERT INTO mysql_row_lock_test (id, name, age, sex, createTime, updateTime) VALUES (3, 'c小昭', 18, 0, now(), now());
      INSERT INTO mysql_row_lock_test (id, name, age, sex, createTime, updateTime) VALUES (4, 'd珠儿', 22, 0, now(), now());
      INSERT INTO mysql_row_lock_test (id, name, age, sex, createTime, updateTime) VALUES (5, 'e陈友谅', 33, 1, now(), now());
      INSERT INTO mysql_row_lock_test (id, name, age, sex, createTime, updateTime) VALUES (6, 'f韦一笑', 40, 1, now(), now());
  

  表中数据如下：
  

注： 我们先讲三种锁是什么，再去讲解mysql是如何加锁的。

3.1.1 记录锁(Record lock)

1、记录锁特征：

• Record lock 记录锁，锁的是某一条索引记录。例如: select * from mysql_row_lock_test where id = 3 for update; 该sql会给 id=3 的这一索引记录加 X类型的记录锁，从而达到目的：防止任何其他事务插入、更新或删除 id=3 的这一行记录。
  

  如果 表mysql_row_lock_test 没有主键索引也无其他用户定义的索引。那么对于这种情况，InnoDB创建一个隐式的索引列被称为 GEN_CLUST_INDEX，（没有主键就拿唯一列，没有唯一列就生成一个6字节的单调递增的row ID来作为一行数据的唯一标识）并用该隐式索引进行 行记录锁定。下边为了方便演示，我们规定id列是自增类型的主键索引。

• X与X 互斥， X与S互斥，SS不互斥

2、记录锁示意图：

3、记录锁演示：
使用命令 SELECT * FROM performance_schema.data_locks; 来查看加锁信息

4、记录锁作用：

• 当事务a给记录1加上 排他(X) 类型的 记录锁后，其他事务 读取，插入、更新或删除 记录1的话，都将被阻塞。
  • 注意：(此处假设是S类型的读 即select * from t where 条件xxx lock in share model，普通的读比如 select * from t where 条件xxx 走的快照读，不会获取锁)
• 当事务a给记录1加上 共享(S) 类型的 记录锁后，其他事务插入、更新或删除 记录1的话，都将被阻塞。

3.1.2 间隙锁(Gap lock)

所谓间隙锁，其实比较好理解，间隙锁锁定的范围是两个记录之间的间隙，但是不包含记录本身，比如现有数据 [18,19,22,28,33,40] ，那么如下间隙都有可能被加上锁：（负无穷,18）（19,22）（22,28）（28,33）（33,40）（40,正无穷） 。

1、间隙锁特征：

• 两个记录之间的间隙，但是不包含记录本身
  • 永远是开区间
• 间隙锁与间隙锁互相兼容（无论是S还是X）

  • 正如其他行锁一样间隙锁也有X排他和共享之分，但是间隙锁比较特殊，不管是X还是S型的间隙锁，同一时刻可以被不同的事务获取。做个表格来说明的话就是这样的：

    间隙锁兼容性	(X) GAP	(S) GAP
    (X) GAP	兼容	兼容
    (S) GAP	兼容	兼容

• 间隙锁为何而生？
  • 正如mysql官方文档描述的那样，间隙锁的出现是"纯抑制的" (“purely inhibitive”) ，见：间隙锁官方描述 ，举个例子，在事务a锁定区间 （19,22） 时，其他事务如果想插入区间（19,22），那么只能阻塞，直到所有（不包含事务a本身）持有该区间间隙锁的的事务释放掉他们持有的间隙锁，事务a才可以获取插入意向锁成功，执行插入操作。因此可以得出间隙锁的作用就是在锁定某段间隙时，禁止其他事务插入数据到该间隙（利用间隙锁和和插入意向锁（Insert Intention Locks）的互斥性实现的），仅此而已。（其实看到这里应该不难联想到，间隙锁是为了解决幻读问题而出现的因为他锁定的是一个区间！实际上mysql真正解决幻读的是next-key lock 但是next-key lock是记录锁+间隙锁的组合，所以也可间接的说幻读的解决，是依赖于间隙锁的）。
    

    小插曲：什么是幻读？ 幻读指的是同一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（新插入的幻影记录，也可叫幻影行更多见：Mysql官网 Phantom Rows，幻读现象如下图演示：
    

2、间隙锁示意图： 为了方便示意和演示，我们给age字段加上唯一索引。

3、间隙锁加锁演示：

• 
  可以看到：select * from mysql_row_lock_test where age=20 for update;加的是间隙锁，作用于unique_idx_age这个索引上。

4、间隙锁的锁定范围是如何确定的？：

• 一般来说 LOCK_MODE是 GAP(间隙锁)或者X(临键锁) ，那么LOCK_DATA 就表示锁的 右边界(这条规律一定记住，对于我们 观察锁范围很有帮助)。上图事务 的 LOCK_DATA 是 22,4 （22代表age字段的值，4代表id的值），LOCK_MODE是间隙锁，所以可以得出该事务的锁定范围就是： 22到上一个age（19）的间隙。综上所述可以得知：该事务加的是 (19,22)区间的间隙锁。

5、间隙锁作用：

• 当事务a锁定区间 (x,y) 后，防止其他任何事务插入数据到区间(x,y)

3.1.3 临键锁(Next-Key lock) = Next-Key lock+ Gap lock

正如官网所说的那样：Next-Key lock 是 索引记录上的记录锁 和索引记录之前的间隙上的 间隙锁 的组合。 也就是说 Next-Key lock是一个左开右闭的区间，比如现有数据 [18,19,22,28,33,40] ，那么如下区间都有可能被加上Next-key lock ：（负无穷,18]（19,22]（22,28]（28,33]（33,40]（40,正无穷）。

ps：为啥其他区间段右边界都是闭区间，而（40,正无穷）这个区间的右边界是开区间呢？很简单因为无法确定这个Supremum的值，从而也就所以无法取右边界的闭区间。

1、Next-Key lock锁特征：

• 锁定范围是：左开右闭区间（实际最大记录到supernum除外）
• 为了解决幻读现象而生（解决幻读有两种是MVCC ,一种是加锁）
• Next-Key lock 是加行锁的基本单位（也就是说一上来要加的行锁都是Next-Key lock， 只是 一些情况下会退化成记录锁或者间隙锁），这一点很重要，后续我们会在加锁分析小节说明。
• X与X 互斥， X与S互斥，SS不互斥

2、Next-Key lock示意图：

3、Next-Key lock加锁演示：


从上两图可以看到，当我执行select * from mysql_row_lock_test where age >= 28 for update;时候（注意前提 表中已有age列数据[18,19,22,28,33,40]），mysql加的Next-Key lock如下：
(22,28],(28,33],(33,40],(40,正无穷) ,也就是说(22,正无穷) 这个区间都被锁住了，此时: 如果其他事务想读/写这个区间的数据，则会被阻塞。

4、Nexk-Key lock的锁定范围是如何确定的？：

• 一般来说 LOCK_MODE是 GAP(间隙锁)或者X(临键锁) ，那么LOCK_DATA 就表示锁的 右边界(这条规律一定我们在上边说过，这里又简单提一嘴），而Next-Key lock是左开右闭区间，LOCK_MODE = X代表Next-Key lock锁，所以我们得出：该事务给区间 (22,28],(28,33],(33,40],(40,正无穷)加了Next-Key lock类型的锁。

5、Nexk-Key lock的作用：

• 在事务a 给 区间 (x，y]加上排他锁X时，防止其他事务 读取/写入 区间(x,y] 任何数据。
  • 注意：(此处假设是S类型的读 即select * from t where 条件xxx lock in share model，普通的读比如 select * from t where 条件xxx 走的快照读，不会获取锁)
• 在事务a 给 区间 (x，y]加上共享锁S时，防止其他事务 写入 区间(x,y] 任何数据。
• 解决了快照读场景下的 幻读问题： 在 Innodb RR（可重复读隔离级别下），Mysql通过Next-Key lock解决了快照读场景下的幻读问题(当然还有一种方式就是MVCC也可以解决快照读下的幻读问题)，（这个我们f会在后续文章：RR隔离级别下的幻读问题真的解决了吗？ 这篇文章里说明，敬请期待）。

ok到这里，啰啰嗦嗦这么多，我们简单小总结下行锁的东西：

下边我们看下Mysql是如何加锁的。

3.2 行锁加锁核心思想以及解决的问题

Next-key lock解决的问题（重要）： 首先我们要知道的一个非常重要的点，就是mysql在RR隔离级别下通过Next-key lock=（记录锁+间隙锁）解决了当前读场景下的幻读现象。

加锁核心思想（重要）： 通过搜索条件扫描到的任何区间（某一条记录我们也泛化当成一个区间），首先给他尝试加Next-key lock锁，如果不加Next-key lock 即间隙锁或者记录锁也可以保证不出现幻读的情况下，则退化为相应的间隙锁/记录锁。这个我本来想画图表示一下，但是感觉不好整，这是一个核心思想。画图太静态了不能够完美表达，干脆不画了，想深入体会这个核心思想就是看下边大量的加锁分析，就能恍然大悟了。

ps:下边所有加锁规则和分析，都是围绕这个核心思想去的！~~

3.2 行锁加锁规则

上边我们说了加锁的核心思想和要解决的问题，但是比较泛泛，接下来我们总结出几个规则（看到最后你可以理解下边的规则其实就是上边思想的实现罢了）。

ps：以下规则中的从左向右扫描都是在 order by asc基础上；如果是order by desc则扫描规则是从右到左，但是规则不变。

• 规则1：
  • 查询过程中访问到(扫描到)的对象才会加锁。
• 规则2：
  • 加锁的基本单位（或者说默认）是 Next-Key lock， 左开右闭区间比如 (x,y]
• 规则3：
  • 等值 查询时Mysql的优化：
    • 第一条：如果是 唯一索引，且 目标值存在 则next-key lock会 退化为记录锁，不存在 则当 找到第一个大于该目标值的索引记录 y 后，将 y 这个索引记录 上的 Next-Key lock 退化为间隙锁(因为y不符合条件，所以无需锁y即间隙锁开区间就可以保证幻读现象) （如果第一个大于该目标值的索引记录是supernum则还是 Next-Key lock，不退化为间隙锁）。
    • 第二条：如果是 普通索引（非唯一）不管有没有目标记录，仍然需要 从左向右访问到第一个不满足条件的值，相应的 next-key lock 也会 退化为间隙锁。
• 规则4：
  • 范围 查询时Mysql的优化：
    • 第一条：无论是否唯一索引，范围查询都需要从左向右访问，直到找到第一个不满足条件的值y为止（主键索引=22为啥要（19,22] 把22之前的记录也锁住？why？？？)。

    随后给唯一索引对应的主键都加上了记录锁于是整个 实际的加锁情况就是：唯一索引上的Next-Key lock,范围: （19,22]? ,(22,28],(28,33],(33,40],(40,supernum) , 主键上的记录锁 范围: 1,4,5,10

• 小于 且边界值存在表中
  

  • 加锁分析：
    

    由于小于是个范围查询，所以从最小记录age=负无穷左向右遍历直到遇到第一个不符合条件的记录 age=24 才停止扫描,被扫描到的唯一索引记录都加上Next-key lock锁，唯一索引记录对应的主键索引都加上记录锁，于是就有了 next-key lock锁范围是（负无穷，18],(18,19],(19,22] （但是此处的(19，22]并没有退化为间隙锁，我们上边的主键id这种情况下是退化为间隙锁了，什么原因暂时还不清楚，留个todo，要我说没必要给22这个取闭区间呀，直接取开区间（19,22）退化为间隙锁就行了）；以及记录锁 id=2 id=3

• 小于等于 且边界值存在表中
  

  • 加锁分析：
    

    首先从左边最小值依次往右遍历，遍历过程中给(负无穷,18],(18,19],(19,22],(22,28]这几个范围加了Next-key lock锁 ，但是（22,28]这个28 是第一个不符合目标值的索引记录，他也没有退化仍旧是Next-key lock，不清楚什么原因暂时也留个todo吧

在唯一索引下：对于大于等于为啥往前去了一个区间，以及小于、小于等于 第一个不符合条件的记录为啥没退化为间隙锁。这里有待讨论完善，欢迎评论区留言。

4.3、非唯一索引

主键索引和唯一索引都演示了，我们接下来搞下普通二级索引（非唯一）

首先为了方便，我们把age去掉唯一索引，添加上普通二级索引
执行此命令：

drop index unique_idx_age on mysql_row_lock_test;
alter table mysql_row_lock_test add index idx_age (age);

执行完后的age索引树长这样：

数据改成这样：

4.3.1、非唯一索引等值查询

• 值不存在
  
  • 加锁分析
    

    这个比较简单和主键等值查询值不存在一样，不再分析。

• 值存在
  
  • 加锁分析
    

    由于非唯一二级索引值会重复，需要绑定主键id来标识唯一性，所以符合条件的即age=22的都被扫描上，对于二级索引我们知道在值相同时会根据id进行从左到右的排序。于是从左到右的加锁情况就是 (19,id=1那个22],(19,id=4那个22],(19,id=5那个22],(22,40) 因为40是向右扫描过程中第一个不符合=22这个条件的记录，所以由(22,40]的NEXT-key lock退化为(22,40)的间隙锁。之后给扫描到的主键id加上记录锁。完事。

4.3.2、非唯一索引范围查询

• 大于，边界值存在
  
  • 加锁分析（这个和上边的类似比较简单不说了）
• 大于等于
  
  • 加锁分析
    

    由于age=22是不唯一的，为了防止幻读，必须在这个前后都加上next-key lock锁，所以就有了这个结果 (19,id=1那个22],(19,id=4那个22],(19,id=5那个22],(22,40],(40,supernum）以及id=1,id=4,id=5,id=10的这些id索引记录。

• 小于
  
  • 加锁分析
    

    从左到右依次遍历，(负无穷,18],(18,19],(19,22] 22是不符合条件的第一个记录但是也没有退化为间隙锁因为在规则3第二条说过在范围查询时，如果不是唯一索引，第一个不符合条件的记录则不会发生Next-key lock退化为间隙锁的操作

• 小于等于
  
  • 加锁分析
    

    从左到右依次遍历，(负无穷,18],(18,19],(19,id=1的那个22],(19,id=4的那个22],(19,id=5的那个22],(22,40], 40是不符合条件的第一个记录但是也没有退化为间隙锁因为在规则3第二条说过在范围查询时，如果不是唯一索引，向右扫描到的第一个不符合条件的记录则不会发生Next-key lock退化为间隙锁的操作

4.4、无索引

无索引比较简单粗暴，会遍历全表都加上锁，至于什么类型的锁就看情况了，总之这种无索引的列我们千万别去用于where条件，否则db并发将急剧下降，离跑路不远了哈哈。~~

5、总结：

到这里啰啰嗦嗦2.7万字符，正文字数1万+，我也不知道我讲明白没。
由于明天就十月一放假了，所以今天必须完结本文。这个加锁规则说实话很复杂，版本不一样也有出入，另外也和全局参数有关系。总之有点难度。但是基本上我们通过大量演示后，也就理解的八九不离十了。我们画图总结下，还是那句话 一图胜千言：

如果有不足请指出共同学习，如果有帮助请点赞收藏

参考：