Mysql锁理论 & 加锁实战

站长

2023年09月29日 17:45 · 阅读数 10

前言：

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

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

开篇前的说明：

在开篇之前，我们先说明一下，mysql中的锁一般都有两种属性，一种是共享性，一种是排他性，在本文，S代表共享锁，X 代表排他锁。
本文使用的MySql版本为 8.0.27，存储引擎为InnoDB，事务隔离级别为mysql默认的：可重复读 REPEATABLE-READ。

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

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; （释放锁比较简单不过多解释了）

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

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

事实上，此类 表级别的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代表意向排他锁。

表级锁兼容性总结	X	IX	S	IS
X	`不兼容`	`不兼容`	`不兼容`	`不兼容`
IX	`不兼容`	兼容	`不兼容`	兼容
S	`不兼容`	`不兼容`	兼容	兼容
IS	`不兼容`	兼容	兼容	兼容

从以上表格可以看出

意向读锁(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 不兼容 兼容

行锁兼容性总结	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锁理论 & 加锁实战

注：我们先讲三种锁是什么，再去讲解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，幻读现象如下图演示：

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

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示意图： Mysql锁理论 & 加锁实战

3、Next-Key lock加锁演示： Mysql锁理论 & 加锁实战从上两图可以看到，当我执行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到这里，啰啰嗦嗦这么多，我们简单小总结下行锁的东西：

记录锁
- 锁定的一行记录
- LOCK_MODEL一般这么表示 X,REC_NOT_GAP / S,REC_NOT_GAP;
- X与X 互斥， X与S互斥，SS不互斥
间隙锁
- 锁定的是一个左开右开的开区间
- LOCK_MODEL一般这么表示 X,GAP / S,GAP;
- 间隙锁之间不互斥，间隙锁存在的目的是：防止在锁定范围(x,y) 时，其他事务插入数据到该区间。
Next-Key lock
- 锁定的是一个左开右闭的区间， (实际最大,supernum) 这个区间除外
- LOCK_MODEL一般这么表示 X / S;
- X与X 互斥， X与S互斥，SS不互斥

下边我们看下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为止（主键索引<=操作除外见第二条）。 在向右扫描过程中，如果索引是唯一索引，则第一个不满足条件的记录将会从Next-Key lock锁退化为间隙锁(因为唯一值y不符合条件，所以无需锁y即间隙锁开区间就可以保证幻读现象，如果第一个不满足条件的记录是实际记录的最大值，则不退化为间隙锁，仍旧是Next-Key lock锁。）；在范围查询时，如果不是唯一索引，向右扫描到的第一个不符合条件的记录不会发生Next-key lock退化为间隙锁的操作
  - 第二条：注意： 由于主键索引的特殊性，当是范围查询且目标值匹配到某个已经存在的右边界时（比如 id<=4 且4存在 ），这种情况下则会停止扫描也不会给4后边的记录加锁，因为主键列是有序且唯一的，已经找到一个相等的值后，再向右扫描没有意义（这个现象在下边演示 id小于等于且右边界值存在时会看到）。

希望下边的分析你会带着加锁思想和规则去慢慢体会和实践。

4、不同场景下的锁分析演示

对于 update delete 加锁情况和select * from t where xxx for update其实差不多，我们这里主要演示for update 时不同场景下的加锁情况。根据经验，一般列 索引属性 逃不出：主键/唯一键/普通非唯一索引/无索引 这4种情况，where条件一般就是两大类：等值查询/范围查询，下边我们来排列组合一下。

组合1：主键id等值查询
组合2：主键id范围查询
组合3：唯一索引等值查询
组合4：唯一索引范围查询
组合5：普通二级索引(非唯一)等值查询
组合6：普通二级索引(非唯一)范围查询
组合7：无索引查询

再次提醒： 此演示的隔离级别是REPEATABLE-READ id是自增主键，age我们加了唯一索引，演示非唯一的时候我们会去掉age的唯一索引加上普通二级索引。

首先我们看下表中 现有数据： Mysql锁理论 & 加锁实战

4.1、主键上的锁演示

首先我们要知道 id是自增主键，这是一个大前提。

4.1.1、主键列上的等值查询

目标值存在（很简单，就是给对应的值 加记录锁，锁住这一行）
- 加锁分析：
  1. 根据id=4找到了记录，由于符合规则3第一条由Next-Key lock退化为记录锁，锁定范围就是id=4的这行记录
目标值不存在（可能加间隙锁，也可能是Next-key lock锁）
- 目标值在已有记录的某个间隙，执行语句：select * from mysql_row_lock_test where id = 7 for update;
  - 加锁分析
    1. 首先根据索引树进行查找后得知7不存在且下一个记录是10，所以符合规则3第一条，因此得出加的是间隙锁，范围是(5,10) 加锁结果： 间隙锁，范围 (5,10) 。
- 目标值大于表中实际最大记录，也就是说目标值范围是（实际最大记录,supernum），此时我们执行语句：select * from mysql_row_lock_test where id = 18 for update;
  - 加锁分析
    1. 首先根据索引树进行查找后得知18不存在且下一个记录是supernum，所以符合规则3第一条，因此得出加的是间Next-Key lock，范围是(10,supernum)。

4.1.2、主键列上的范围查询

大于且边界值存在表中
- 加锁分析：
  1. 大于4的第一个是id=5的记录所以给id=5加Next-Key lock (4,5] , 同理继续往下走，给(5,10],(10,supernum)都加上了Next-Key lock，由于向右没遇到不符合条件的记录，所以不退化为间隙锁
大于等于且边界值存在表中
- 加锁分析：
  1. 大于等于其实可以拆开看，id=4存在且被扫描到所以可Next-Key lock退化为记录锁，而继续向右则属于范围查询符合规则4第一条，所以都是Next-Key lock锁，范围是(4,5],(5,10],(10,supernum)
小于且边界值存在表中
- 加锁分析：
  1. 小于是个范围查询从最小记录id=负无穷从左向右遍历直到遇到第一个不符合条件的记录4 才停止扫描而4这个记录符合 向右遍历时第一个不符合条件的记录从Next-key lock退化为间隙锁，也就是说符合规则4第一条所以得出 加锁结果：Next-Key lock + 间隙锁 共计锁定范围-> （负无穷，1],（1,2],(2,3],(3,4)
小于等于且边界值存在表中
- 加锁分析：
  1. 由于条件是id<=4 所以从左到右遍历就是(负无穷,1],（1,2],(2,3],(3,4],(4,5),而由于索引列是主键id所以在找到=4这个记录后，其实不需要向后遍历找不符合条件的第一个记录（即id=5那条记录了)也就是说(4,5)这个退化的间隙锁可以省略掉，所以我们真正看到的锁就是Next-key lock范围是：负无穷,1],（1,2],(2,3],(3,4]

其他的主键范围查询比如 id > x and id <y ，或者x不存在或者y不存在等等这种的我们就不演示了，因为这个组合有点多。不过都可以根据上边的几个规则推导出来。

4.2、唯一索引

唯一索引和主键索引有个共同点就是唯一。但是他们在加锁时也有些不同点，所以我们这里单独拿出来说一下唯一索引。

注意点1：唯一索引和主键索引加锁不一样的地方就是：除了把符合规则的唯一索引加锁外（加锁情况和主键索引一样），会额外把对应的主键索引加上记录锁。

4.2.1、唯一索引上的等值查询

目标值存在
- 加锁分析：
  1. 根据 唯一索引目标值存在则将next-key lock会退化为记录锁这个规则以及上边的注意点1 ，得出给唯一索引树的age=22和主键索引树主键id=4的这两个b+tree（实际是同一行记录）都加了记录锁
    唯一索引等值匹配且存在时的加锁示意图如下：
目标值不存在
- 加锁分析：
  1. 可以看到目标值不存在加的是间隙锁，这个和主键id等值查询目标值不存在的加锁情况是一样的。都是符合规则1 规则2 和规则3第一条 ，可以上图看出加的是间隙锁(X,GAP)范围是 (22,28)。

4.2.2、唯一索引上的范围查询

大于且边界值存在表中
- 加锁分析：
  1. 首先遍历索引树找到22的下一个记录是28，所以加了(22,28]的Next-key lock锁，继续依次向右遍历加了这些锁 (28,33],(33,40],(40,supernum) ，同时在扫描过程中因为需要给扫描到的唯一索引对应的主键记录加记录锁，于是有了记录锁锁定记录分别是id=1,id=5,id=10这三个记录，于是整个加锁情况加速记录锁+Next-key lock 锁定范围是 (22,28]，(28,33],(33,40],(40,supernum) 以及 id=1,id=5,id=10这三个主键索引记录
大于等于且边界值存在表中
- 加锁分析：
  1. 从age=22这个记录开始依次向右遍历，给遍历到的唯一索引记录都加上Next-key lock锁
  - 但是这里第一个扫描的 age=22这个记录并没有退化为记录锁！（我们上边演示的主键id>=4时候是看到4被加上了记录锁而不是Next-key lock）这里我百思不得其解(我的条件是id>=22为啥要（19,22] 把22之前的记录也锁住？why？？？)。
  随后给唯一索引对应的主键都加上了记录锁于是整个 实际的加锁情况就是：唯一索引上的Next-Key lock,范围: （19,22]? ,(22,28],(28,33],(33,40],(40,supernum) , 主键上的记录锁 范围: 1,4,5,10
小于且边界值存在表中
- 加锁分析：
  1. 由于小于是个范围查询，所以从最小记录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
小于等于且边界值存在表中
- 加锁分析：
  1. 首先从左边最小值依次往右遍历，遍历过程中给(负无穷,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索引树长这样： Mysql锁理论 & 加锁实战数据改成这样：

4.3.1、非唯一索引等值查询

值不存在
- 加锁分析
  
  这个比较简单和主键等值查询值不存在一样，不再分析。
值存在
- 加锁分析
  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并发将急剧下降，离跑路不远了哈哈。~~ Mysql锁理论 & 加锁实战

5、总结：

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

Mysql锁理论 & 加锁实战

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

参考：

Mysql锁理论 & 加锁实战

前言：

开篇前的说明：

1、表锁

1.1、表级别的S/X锁

1.1.1 获取表级S/X的方式

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

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

1.3、元数据类型的表锁

1.4、AUTO-INC

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

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

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

3、行锁

3.1、行级锁分类

3.1.1 记录锁(Record lock)

3.1.2 间隙锁(Gap lock)

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

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

3.2 行锁加锁规则

4、不同场景下的锁分析演示

4.1、主键上的锁演示

4.1.1、主键列上的等值查询

4.1.2、主键列上的范围查询

4.2、唯一索引

4.2.1、唯一索引上的等值查询

4.2.2、唯一索引上的范围查询

4.3、非唯一索引

4.3.1、非唯一索引等值查询

4.3.2、非唯一索引范围查询

4.4、无索引

5、总结：

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