本文的原文地址

一：尼恩说在前面：

在40岁老架构师尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、shein 希音、shopee、百度、网易的面试资格，遇到很多很重要的面试题：

明明加的是行锁，结果偷偷变成表锁，你说说为什么？

mysql 行锁和表锁的原理是什么？

前几天小伙伴面试滴滴，遇到了这个问题。但是由于没有回答好，导致面试挂了。

小伙伴面试完了之后，来求助尼恩。那么，遇到这个问题，该如何才能回答得很漂亮，才能让面试官刮目相看、口水直流。

所以，尼恩给大家做一下系统化、体系化的梳理，使得大家内力猛增，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

当然，这道面试题，以及参考答案，也会收入咱们的《尼恩Java面试宝典》V145版本PDF集群，供后面的小伙伴参考，提升大家的 3高架构、设计、开发水平。

最新《尼恩架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请关注本公众号【技术自由圈】获取，后台回复：领电子书

二：锁膨胀 (行锁变表锁) 的现象

锁膨胀在面试中都是属于偏难的问题，也是偏琐碎的一类问题。

锁膨胀与锁的原理和索引、隔离级别都有关，所以很容易从锁这个角度联想到另外两个地方，又或者从索引和隔离级别里面跳到锁这里。

MySQL的存储引擎是从MyISAM到InnoDB，锁从表锁到行锁。

行锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，但并发度高。仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作

表锁：开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度低。直接锁定整张表，在锁定期间，其它进程无法对该表进行写操作。如果是写锁，则其它进程则读也不允许。

行锁的出现, 从某种程度上是弥补前者的不足。比如：MyISAM不支持事务，InnoDB支持事务。

表锁虽然开销小，锁表快，但高并发下性能低。
行锁虽然开销大，锁表慢，但高并发下相比之下性能更高。

锁膨胀常常偷偷产生：有的时候加的是行锁，却偷偷的变成了表锁，导致性能急速下降。

哪些场景会造成行锁升表锁？

无索引
索引失效

那为什么行锁会升级成表锁呢，我们首先需要从行锁的原理说起

三：Mysql 锁基础原理

3.1 从操作的类型进行的mysql锁的分类

从操作的类型分，可分为读锁和写锁。

读锁（S锁、共享锁）

共享锁简称S锁(Shared)，也称为读锁：读读兼容，当前事务获取S锁后，其它事务也可以获得S锁，但会阻塞其它事务获得X锁；

读锁（S锁）：针对同一份数据, 多个读操作可以同时加上读锁，而不会互相影响。

事务A对记录添加了S锁，可以对记录进行读操作，不能做修改。

其他事务可以对该记录追加S锁，但是不能追加 X锁，要追加X锁，需要等记录的S锁全部释放。

写锁（X锁、排它锁）

排它锁：简称X锁(eXclusive)，也称为写锁：读写/写写均不兼容，当前事务获取X锁后，会阻塞其它事务获取S锁和X锁。

写锁（X锁）：当前写操作没有完成前, 它会阻断其他X锁（写锁）和读锁（S锁）

事务A对记录添加了X锁，可以对记录进行读和修改操作，但是，其他事务不能对记录做读和修改操作。

意向锁

IS: 意向共享锁，表级锁，已加S锁的表，肯定会有IS锁，反过来，有IS锁的表，不一定会有S锁
IX: 意向排它锁，表级锁，已加X锁的表，肯定会有IX锁，反过来，有IX锁的表，不一定会有X锁

意向锁是表级锁。

SQL语句对应上的行锁说明如下：

操作	锁的模式	说明
普通select语句	无锁	无锁，基于MVCC机制，普通的 select 语句属于快照读
select…lock in share mode	S	当前读，显示(explicit)读锁，上锁后，其它事务对锁定的索引记录仍可以上S锁，但阻塞其它事务对锁定的索引记录上X锁
select…for update	X	显式(explicit)写锁，上锁后，阻塞其它事务对锁定的索引记录上S或X锁
insert/update/delete	X	隐式(implicit)写锁，上锁后，阻塞其它事务对锁定的索引记录上S或X锁

3.2 从操作的粒度进行的mysql锁的分类

从操作的粒度可分为表级锁、行级锁和页级锁。

表级锁：

每次操作锁住整张表。

表锁锁定粒度大，发生锁冲突的概率最高，表级锁并发度最低。

表锁应用在MyISAM、InnoDB、BDB 等存储引擎中。

表锁的特点：

开销小，加锁快
不会出现死锁
锁定粒度大，发生锁冲突的概率最高，并发度最低

行级锁：

每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。

行锁应用在InnoDB 存储引擎中。

行锁的特点：

开销大，加锁慢
会出现死锁
锁定粒度小，发生锁冲突的概率最低，并发度最高

InnoDB 引擎支持行锁和表锁，而 MyISAM 引擎只支持表锁。

MySQL服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。

InnoDB 的行锁是实现在索引上的，而不是锁在物理行记录上。

也就是说，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

例如：


t_user(uid, uname, age, sex) innodb; uid PK，无其他索引
update t_user set age=10 where uid=1; 命中索引，行锁。
update t_user set age=10 where uid != 1; 未命中索引，表锁。
update t_user set age=10 where name='chackca'; 无索引，表锁。

总结：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

3.3 InnoDB存储引擎三种行锁模式

InnoDB引擎的行锁是对记录加锁实现的。

InnoDB引擎的行锁主要有 3 种：Record Lock、Gap Lock 和 Next-key Lock,也就是InnoDB的三种行锁模式。

Record Lock锁(行锁)：

Record Lock锁(行锁) , 锁定单个行记录的锁。

RecordLock锁是记录锁， RC、RR隔离级别都支持

Gap Lock锁：

间隙锁，锁定索引记录间隙(不包括记录本身)，确保索引记录的间隙不变。

GapLock 是范围锁，RR隔离级别支持。RC隔离级别不支持

Next-key Lock 锁(临键锁)：

记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。

记录锁+范围锁，RR隔离级别支持。RC隔离级别不支持

1 记录锁(Record Locks)

（1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。

（2）record lock锁住的永远是索引，而非记录本身

即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

2 间隙锁(Gap Locks)

（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。

（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据

比如在 100、10000中，间隙锁的可能值有 (∞, 100)，(100, 10000)，(10000, ∞)，

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

间隙锁锁定的是索引记录之前的间隙，白话说就是：每个索引值管着前面的间隙;

举个例子：当索引的值有10,20,30,40时，那么索引就存在如下间隙（圆括号表示不包括区间点）：


(下界限, 10)
    (10, 20)
    (20, 30)
    (30, 40)
    (40, 上界限supremun)

因为是锁定索引之前的间隙，所以就存在如下间隙锁：

间隙范围	索引记录
(下界限, 10)	10
(10, 20)	20
(20, 30)	30
(30, 40)	40
(40, 上界限supremun)	supremun

特殊说明：由于间隙锁是为了解决事务隔离RR级别下当前读的幻读问题，所以在读已提交（RC）事务隔离级别是显示禁用间隙锁的。

3 临键锁(Next-Key Locks)

（1）临键锁= 记录锁+范围锁。临键锁(Next-Key Locks) = gap lock + record lock, 左开右闭区间。

（2）默认情况下，innodb 事务的隔离级别是 RR 不是RC，所以 select … for update 默认使用next-key locks来锁定记录。

（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

（4）Next-Key Lock在不同的场景中会退化:

在这里插入图片描述

45岁老架构师尼恩提示，临键锁(Next-Key Locks) 这里的关键是左开右闭。比如在 100、10000中，临键锁(Next-Key Locks)的可能有 (∞, 100]，(100, 10000] ,

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

Record Lock + Gap Lock 的组合，既锁索引记录又锁间隙，很多地方都称它是临键锁或邻键锁，但我觉得直接翻译成下一个键锁会更好理解，意思是锁由“下一个键负责”，原则：左开右闭或称前开后闭。

上面的例子的区间为（圆括号表示不包括区间点，方括号表示包括区间点）：


(下界限, 10]
    (10, 20]
    (20, 30]
    (30, 40]
    (40, 上界限supremun)

当给索引值20加上了Next-key Lock，那么这个范围是 (10,20] 包括20 ，而不包括10。

由于上界限supremun实际是个伪值，所以上界限并不是真正的索引记录。

因此，实际上，这个Next-key Lock只锁定最大索引值之后的间隙。

3.4 快照读 (snapshot read)与当前读 (current read)

Mysql中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)

快照读指的是简单的select操作，属于快照读，一般不加锁


select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

SELECT ... LOCK IN SHARE MODE：

会给读取到的记录加上共享锁（S锁），允许其他事务也获取这些记录的S锁，但阻止X锁。

SELECT ... FOR UPDATE、INSERT、UPDATE、DELETE：

会给涉及的记录加上排他锁（X锁），阻止其他任何事务获取这些记录的任何类型的锁。

快照读的底层原理：

基于数据快照 undo-log去读，不是直接访问物理存储的最新数据版本，或者说不是直接访问 Buffer Pool 缓冲池数据页（Data Buffer ）的最新数据版本
读取的是历史版本数据或者说，读的是 Buffer Pool 缓冲池的 Undo页（Undo Buffer ）数据，不感知其他事务的后续修改

当前读的底层原理：

直接访问物理存储的最新数据版本，或者说直接访问 Buffer Pool 缓冲池数据页（Data Buffer ）的最新数据版本
总是读取其他事务已提交的最新修改

Mysql实现当前读是通过共享锁+排他锁+Next-Key Lock实现的。

每次对行数据进行读取的时候，加共享锁。此时就不允许修改，但是允许其他事务读取，所以每次都可以读到最新的数据。
每次对行数据进行修改的时候，加排他锁，不允许其他事务读取和修改。这种情况下其他事务读取的数据也一定是最新的数据。
每次对范围行数据进行读取的时候，对这个范围加一个范围共享锁。
每次对范围行数据进行修改的时候，读这个范围加一个范围排它锁。

MySQL Buffer Pool 缓冲池中的组件详解

在MySQL的InnoDB存储引擎中，缓冲池（Buffer Pool）是一个关键的内存结构，用于缓存数据和索引，以减少对物理磁盘的I/O操作。以下是缓冲池中一些重要组件的详细解释：

1. 索引页（Index Pages）

索引页存储了InnoDB表的索引结构，包括主键索引（聚集索引）和辅助索引（非聚集索引）。这些索引页被加载到缓冲池中，以加速对表中数据的查找和访问。当执行查询操作时，InnoDB会首先检查所需的索引页是否已经在缓冲池中，如果在，则直接从缓冲池中读取，这称为缓冲池命中；如果不在，则需要从磁盘加载到缓冲池中，这称为缓冲池未命中。

2. 数据页（Data Pages）

数据页存储了InnoDB表的实际数据行。在InnoDB中，数据是按页存储的，每个数据页通常包含多行数据。当需要读取或修改表中的数据时，相关的数据页会被加载到缓冲池中。通过将数据页缓存在内存中，InnoDB可以快速地读取和修改数据，而无需每次都从磁盘加载。

3. Undo页（Undo Pages）

Undo页存储了旧版本的数据，用于支持事务的ACID属性中的隔离性（Isolation）和持久性（Durability）。当执行一个事务时，对数据的修改不会立即生效，而是先记录在Undo页中。如果其他事务需要读取被修改的数据，它可以通过Undo页来获取数据修改前的版本，从而实现多版本并发控制（MVCC）。此外，如果事务失败或回滚，Undo页中的数据可以用于恢复数据到事务开始前的状态。

4. 插入缓存（Insert Buffer）

插入缓存是InnoDB中用于优化非聚集索引插入操作的一种机制。当向一个包含非聚集索引的表中插入数据时，如果相关的索引页不在缓冲池中，InnoDB不会立即将索引键插入到索引页中，而是将其存储在插入缓存中。当相关的索引页被加载到缓冲池时，插入缓存中的索引键会被合并并插入到索引页中。这样可以减少磁盘I/O操作，并提高插入操作的性能。

需要注意的是，插入缓存只适用于非唯一索引的插入操作，并且在某些情况下，如缓冲池足够大或表很小，插入缓存可能不会被使用。

MVCC+undolog实现的无锁快照读

mysql中的快照读是通过MVCC+undolog实现的。

快照读，顾名思义，就是读取快照数据，也就是说当某个数据正在被修改的时候，也可以进行读取该数据，保证读写不冲突。

mysql中的快照读 , 每次都是读取undolog中的数据。

刚刚提到undolog，当我们对记录做了变更操作时，就会产生undo记录，undo记录中存储的是老版数据，当一个旧的事务需要读取数据时，为了能够读取到老版本的数据，需要顺着undo列找到满足其可见性的记录，这个找满足可见行的记录依赖。

MVCC：

MVCC也称多版本并发控制，说的通俗易懂一点就是记录数据的不同版本。
实际上，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录。
所以快照读都是去读取undolog中链首的最新的旧记录。

但是不同隔离级别下的快照读又有所区别。

不同隔离级别下的快照读

这里举例说明：

事务开始前 name = 张三 id = 1
事务A：select name from t where id = 1 (多次)
事务B：update name = 李四 from t where id = 1

在Read Committed隔离级别下，快照读和当前读读取的是一样的。因为每次select都会去读取最新的快照数据。

过程如下：

事务A开始
事务B开始
事务A select ，查出name = 张三
事务B update，修改name = 李四
事务B结束
事务A 再次select ，查出name = 李四（因为此时undolog中的最新数据为李四）
事务A结束

在默认隔离级别Repeatable Read下，在一个事务内，读取的都是第一次select的数据，所以可能会出现读取的数据不是最新数据的情况
过程如下：

事务A开始
事务B开始
事务A select ，查出name = 张三
事务B update，修改name = 李四
事务B结束
事务A 再次select ，查出name = 张三（因为只读第一次的快照数据）
事务A结束

事务的不同隔离级别加锁方式是不同的

1、可重复读（RR）：

在RR隔离级别下，默认会使用Next-Key Locks，这是Record Lock加上Gap Lock的组合。它的作用是防止幻读现象，即阻止其他事务插入新的记录到当前事务正在读取的范围之内。

2、 读已提交（RC）：

与RR不同，在RC隔离级别下，默认情况下不会使用Gap Locks，这意味着它不会阻止其他事务向当前事务正在操作的数据范围内插入新记录。因此，在RC隔离级别下可能会出现幻读现象。

下面对使用聚族索引和非聚族索引下的修改语句的加锁规则进行分析，以了解mysql行锁的加锁规则

四：锁膨胀/锁优化分析之一：索引等值匹配加锁规则实操

锁膨胀/锁优化分析分为两种情况：

索引等值匹配：

有唯一性的索引，都会降级为Record Lock。当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

索引无值匹配：

对于聚集索引、唯一索引、普通索引，都只上了一把Gap Lock间隙锁，锁的是若条件值成立的 next key索引记录。

索引等值匹配分析

索引等值匹配分析的是: 聚族索引、唯一索引、普通索引字段 “等于” 条件的修改语句加锁情况

索引等值匹配分为三种情况：

聚族索引：对唯一匹配的索引记录上了 Record Lock，这里是Next-key Lock降级优化为 Record Lock;
唯一索引：对唯一匹配的索引记录上了 Record Lock，对应的聚集索引记录也上了Record Lock，都是Next-key Lock降级优化为 Record Lock;
普通索引：对所有匹配的索引记录都上了 Next-key Lock，对应的聚族索引记录都上了Record Lock，另外，对匹配索引记录的next key记录上了Gap Lock。（相当于把自身和前后的间隙都加锁了），原因是普通索引可能重复，需要向右扫描

4.1：锁膨胀分析之：数据准备

准备一个用户表，三个索引。聚族索引是 id 是自增主键。

两个非聚族索引：

age（年龄）是普通索引，
work_number（工号）是唯一索引

并插入4条初始数据：


CREATE TABLE `t_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(10) unsigned NOT NULL,
  `work_number` int(10) unsigned NOT NULL,
  `remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_work_number` (`work_number`) USING BTREE,
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO `t_user`
(`id`, `name`, `age`, `work_number`, `remark`) 
VALUES 
(10, '张三', 10,10, NULL),
(20, '李四', 20,20, NULL),
(30, '王五', 30,30, NULL),
(40, '赵六', 40,40, NULL);

新建Session1，做以下基本设置。

再次说明，本文分析加锁规则的事务隔离级别为，默认的可重复读（RR）事务隔离级别。

所以，先确认是事务隔离级别：使用可重复读（RR）事务隔离级别，这个也是mysql默认的。大厂一般建议改为 rc提升性能。


mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

如果不是，需要在各Session中设置一下：


set tx_isolation='repeatable-read';

再开启 Session2 对话框，也检查一下事务隔离级别。

接下来，开启锁的监视器 .

在 Session1、 Session2 两个对话框中，开启锁的监视器：

SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

查询是否开启：


mysql> show variables like '%innodb_status_output%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output       | ON    |
| innodb_status_output_locks | ON    |
+----------------------------+-------+

4.2 聚族索引加锁规则

先从聚集索引开始说起。

那么这里也分索引等值匹配和索引无值匹配两种情况，对应上的锁也是不同的

聚族索引的索引等值匹配实操分析

在Session2执行SQL如下(按id=10)：


begin;
update t_user set remark = '技术自由圈，有匹配' where id = 10;

注意不要commit或rollback，以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息


show engine innodb status\G;

SHOW ENGINE INNODB STATUS\G 是 MySQL 中用于查看 InnoDB 引擎实时状态的核心诊断命令。其作用包括：

‌诊断死锁‌：显示最近死锁的详细信息（涉及事务、冲突语句及资源）；

‌分析事务与锁‌：列出活动事务、锁等待链及行锁占用情况；

‌监控性能‌：统计缓冲池命中率、I/O 负载、Redo 日志刷新进度及内存使用；

‌排查资源争用‌：暴露信号量等待线程及内部线程状态。
通过 \G 垂直格式化输出，便于快速定位性能瓶颈或锁冲突问题，辅助数据库调优与故障排查。

主要看TRANSACTIONS这段，如下图：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

如何区分加锁算法

RECORD LOCKS后面带locks rec but not gap：这说明是无间隙的Record Lock，也就是行锁

RECORD LOCKS后面带locks gap before rec：这说明是间隙锁Gap Lock

RECORD LOCKS后面不带1和2的，就说明是默认的Next-key Lock

我们来分析一下，上图中包含的信息：

1 row lock(s)就代表上了1个行锁（不要理解成只锁了1行）；
具体的行锁信息从RECORD LOCKS开始：每个RECORD LOCKS都会标明上锁的索引，就是index后面的，当前是PRIMARY，即代表上锁的索引是聚集索引；可能有多条RECORD LOCKS(当前只有一条)；
RECORD LOCKS下面紧跟着是它所有的Record lock记录：每条Record lock下面是具体的索引物理记录，第0个就是索引记录的key：当前hex 0000000a是指十六制的10，所以可以得知这个行锁锁的是id=10的聚集索引记录；我们以第0个来识别是哪个索引key就可以了，下面的1~6是索引记录上携带的数据，聚集索引保存了所有字段信息，所以比较多，其它索引只有2行：索引值和聚集索引的值；另外，Record Lock也可能有多条，这里只上了1个行锁，所以只有一条Record lock, heap no。。。

小结：等值查询匹配到聚集索引时，行级锁会上一把无间隙的Record Lock。

这里是因为聚集索引id具有唯一性，所以Next-key Lock降级优化为Record Lock。

聚族索引的索引无值匹配实操分析

假设， id=11 不存在，就是索引无值匹配场景。

先在Session2 rollback上一个SQL，再执行SQL如下：


begin;
update t_user set remark = '技术自由圈，没有id=11记录' where id = 11;

注意不要commit或rollback，以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息



show engine innodb status\G;

我们主要看TRANSACTIONS这段，如下图：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

结论：等值查询未匹配到聚集索引时，行级锁会上一把间隙锁 gap lock before record

这里是间隙锁，不是临界锁。为什么是对 id=20 加的锁，而不是对 id=11 加的锁呢？

我们来分析一下：

行锁都是对索引记录加锁（除了伪值上界限supremun），因为id=11的索引不存在，所以无法对id=11加锁。
索引都是排好序的，按顺序从左向右扫描，直到找到 id=20 时，才可以确定 id=11 不存在，也就是说id=20 是 id =11 的next key，所以是对id=20的索引加锁，这里不是Next-key Lock，而是间隙锁，也是合理的，毕竟只锁间隙就可以了，范围是(10,20)，不包括20。

按这么说，可能有同学又有疑问：

如果id大于最大索引值，锁哪个索引记录？

直接看结果，锁的伪值：上界限supremum，范围是(40, supremum)，不包括40.


update t_user set remark = '比最大id还要大！' where id = 41;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

4.3 唯一索引加锁规则

索引等值匹配

先在Session2 rollback上一个SQL，再执行SQL如下(按abc_uk=10)：


begin;
update t_user set remark = '技术自由圈' where work_number = 10;

注意不要commit或rollback，以便于我们分析行锁

然后我们在"Session1"里查看锁的详细信息


show engine innodb status\G;

我们主要看TRANSACTIONS这段，如下图：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

和聚集索引非常类似，但这里是上了2个行锁，所以有两条Record lock, heapno。。。

小结：等值查询匹配到唯一索引时，行级锁上了2把锁：

(1) 锁了一条唯一索引记录（work_number=10）
(2) 锁了一条聚集索引记录（id=10）

因为唯一索引具有唯一性，所以都是无间隙的Record Lock，这里也是Next-key Lock降级优化为Record Lock。

索引无值匹配

先在Session2 rollback上一个SQL，再执行SQL如下(work_number=35)：


begin;
update t_user set remark = '没有work_number=35的记录~~' where work_number = 35;

注意不要commit或rollback，以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息



show engine innodb status\G;

我们主要看TRANSACTIONS这段，如下图：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

小结：等值查询未匹配到唯一索引时，行级锁会上一把间隙锁，与聚集索引规则相同，具体不做赘述。

4.4 普通索引加锁规则

索引等值匹配

先在Session2 rollback上一个SQL，再执行SQL如下(按age=10)：



begin;
update t_user set remark = '技术自由圈' where age = 10;

注意不要commit或rollback，以便于我们分析行锁

然后我们在Session1里查看锁的详细信息



show engine innodb status\G;

我们主要看TRANSACTIONS这段，如下图：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

我们来分析一下：这里就有意思了，上了3个行锁，还是3种不同的行锁。

3种算法都齐了，先说一下，怎么区分Next-key Lock 、Gap Lock、Next-key Lock：

(1) RECORD LOCKS后面带locks rec but not gap：这说明是无间隙的Record Lock
(2) RECORD LOCKS后面带locks gap before rec：这说明是间隙锁Gap Lock
(3) RECORD LOCKS后面不带1和2的，就说明是默认的Next-key Lock

接下来分析上面的等值查询匹配到普通索引时，行级锁上了3把锁：

(1) age=10 的普通索引记录上了Next-key Lock，这里的范围是：(下界值, 10]
(2) id=10 的聚集索引记录上了Record Lock（单条）
(3) age=20的普通索引记录上了Gap-key Lock，这里的范围是：(10, 20)

可以这样说：一个普通索引的等值查询update时，相当于把这条索引记录前后的空隙都锁上了~

这和聚集索引、唯一索引有着很大的不同，这是为什么？

思考一下！！！

我们新开一个Session3先来验证一下吧：


begin;
// 验证第一把锁
insert into t_user(`id`,`name`,`age`,`work_number`,`remark`) values(11,'肖恩',1,1,NULL);
insert into t_user(`id`,`name`,`age`,`work_number`,`remark`) values(21,'肖恩',9,9,NULL);
update t_user set remark = '技术自由圈' where age = 10;

//  验证第二把锁
update t_user set remark = '技术自由圈' where id = 10;

// 验证第三把锁
insert into t_user(`id`,`name`,`age`,`work_number`,`remark`) values(1,'肖恩',11,1,NULL);
insert into t_user(`id`,`name`,`age`,`work_number`,`remark`) values(21,'肖恩',19,9,NULL);

// 验证20无锁
update t_user set remark = '技术自由圈' where age = 20;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

特殊说明：正常的锁超时异常是：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ^C – query aborted

这是我不想等锁超时按Ctrl+C中止了

分析为什么按age=10 更新时，却上了3把锁：

(1) 匹配上的索引记录需要上锁，所以 age=10的索引上了锁，这里没有降级，就是使用默认的Next-key Lock;
(2) 给匹配索引记录的聚集索引上锁，这个与唯一索引是相同的规则，对应id=10的聚集索引记录上了Record Lock;
(3) 为什么还锁age=20的索引记录，是这样的：普通索引不具有唯一性，当在索引树从左向右扫描时，即使匹配到了记录，也依然不能停止，因为可能有多条匹配的记录！！！

所以依然需要继续向右扫描，直到 age = 20出现为止，这样，age = 20 作为 next key，也需要上锁，这里上间隙锁也是可以理解的，毕竟只锁间隙就可以了。

那如果age=10有多行记录如下：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

那么执行


begin;
update t_user set remark = '技术自由圈' where age = 10;

加锁分析如下：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

一共上了5把锁，多的2把就是我们新增的那条id =15, age = 10记录，对应的普通索引和聚集索引上的锁。

小结：等值查询匹配到普通索引时，行级锁会上2m+1把锁，m是匹配的记录数：上面例子匹配了2条记录，所以上了2*2+1 = 5把锁，分别是

(1) age=10, id =10 的普通索引记录上了Next-key Lock，这里的范围是：(下界值, 10]
(2) age=10, id =15 的普通索引记录上了Next-key Lock，这里的范围是：(下界值, 10]
(3) id=10的聚集索引记录上了Record Lock（单条）
(4) id=15的聚集索引记录上了Record Lock（单条）
(5) abc=20的普通索引记录上了Gap-key Lock，这里的范围是：(10, 20)

通俗的理解：记录和前后间隙

匹配记录的（主键）行锁、
聚族索引的临键锁、
右扫描第一个记录（next key）的间隙锁

索引无值匹配

先在Session2 rollback上一个SQL，再执行SQL如下(按age=1)：


begin;
update t_user set remark = '技术自由圈' where age = 1;

注意不要commit或rollback，以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息


show engine innodb status\G;

我们主要看TRANSACTIONS这段，如下图：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

小结：

等值查询未匹配到普通索引时，行级锁会上一把间隙锁，与聚集索引和唯一索引的规则相同，具体不做赘述。

索引等值匹配 /索引无值匹配加锁规则总结

索引等值匹配：有唯一性的索引，都会降级为Record Lock。

聚族索引：对唯一匹配的索引记录上了 Record Lock，这里是Next-key Lock降级优化为 Record Lock;
唯一索引：对唯一匹配的索引记录上了 Record Lock，对应的聚集索引记录也上了Record Lock，都是Next-key Lock降级优化为 Record Lock;
普通索引：对所有匹配的索引记录都上了 Next-key Lock，查询到的聚族索引记录都上了Record Lock，另外，对匹配索引记录的next key记录上了Gap Lock。（相当于把自身和前后的间隙都加锁了）原因是普通索引可能重复，需要向右扫描

索引无值匹配：

对于聚集索引、唯一索引、普通索引，都只上了一把Gap Lock间隙锁，锁的是若条件值成立的 next key索引记录。

五：范围查询加锁规则实操

分析在 MySQL InnoDB 引擎中，范围查询（如 <、<=、>、>=）所涉及的加锁规则。

在 InnoDB 中，范围查询的加锁行为取决于是否命中索引以及索引的类型。

对于聚族索引，只有 >= 的等值部分会触发锁降级；

而对于二级索引，所有匹配记录及对应聚集索引记录都会被加锁，并且默认使用 Next-Key Lock 来防止幻读。

若未使用聚族索引，则可能导致“行锁变表锁”的严重后果，影响系统并发性能。

加锁规则对比表

查询条件	索引类型	索引记录锁类型	聚集索引锁类型
>= (等值部分)	聚集索引	Record Lock	Record Lock
其他范围操作	聚集索引	Next-key Lock	Record Lock
范围操作	唯一索引	Next-key Lock	Record Lock
范围操作	普通索引	Next-key Lock	Record Lock
范围操作	非聚族索引查询时未使用索引查询	全表 Next-key Lock	全表 Record Lock

5.1 聚族索引加锁规则

小于

新打开一个mysql客户端，我们叫Session2，执行SQL如下（按id < 20）：


begin;
update t_user set remark = '技术自由圈' where id < 20;

注意不要commit或rollback，以便于我们分析行锁

这里匹配id < 20 的记录有两条：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

然后我们在Session1里查看锁的详细信息


show engine innodb status\G;

我们还是主要看TRANSACTIONS这段，如下图：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

这里上了 3 把锁，因为是按id，所以全锁在聚集索引上，也就是小于的值的next key 前面的所有范围：

(1) id = 20 （向右扫描到的第一个不匹配记录）: Next-key Lock；
(2) id = 10（匹配记录） : Next-key Lock；
(3) id = 15（匹配记录） : Next-key Lock；

因为 20 刚好是临界值。如果我们换成 id < 19呢？


update t_user set remark = '技术自由圈' where id < 19;

再来看一下：


show engine innodb status\G;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

聚族索引，<场景:1.向有扫描第一个不匹配的记录的Next-key Lock2.所有匹配记录上Next-key Lock

结果依旧！！！（你可以对比一下：上一个事务ID是：793365）：

还有一个临界值 id < 16 ：


update t_user set remark = '技术自由圈' where id < 19;

结果也是一样的，就不展示了。

所以对于 < 在聚集索引上来说，我们得出的结果：

聚集索引上, 所有匹配的索引记录上Next-key Lock;
向右扫描聚集索引, 直到找到不匹配的索引记录上Next-key Lock.

小于等于

这里依旧演示两个临界值： id <=19 和 id <= 20


begin;
update t_user set remark = '技术自由圈' where id <= 19;

这里匹配记录有 id =10 和 id =15 两条记录。

我们来看一下：


show engine innodb status\G;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

聚族索引，<=场景:

向右扫描第一个不匹配的记录Next-key Lock
所有匹配记录上Next-key Lock
通俗理解:就是加锁小于等于的值的next key前面的所有范围

不出所料，因为和 id < 19 的匹配记录是相同的，所以锁的结果也是相同的！

我们再来看一下临界值 id <=20：


begin;
update t_user set remark = '技术自由圈' where id <= 20;

我们看一下修改的记录，共3条：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

这里对于 3条匹配的索引记录，上Next-key Lock已尽在我们掌握中，但id = 30 是否会上锁？

如果上的话，是上 Next-key Lock 还是Gap Lock？


show engine innodb status\G;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

聚族索引，<=场景

向右扫描第一个不匹配记录的Next-key Lock
所有匹配记录的Next-key Lock
<=的值的next key的前面的范围加锁问题，为什么没有截至到20，而是向右到30，可能是mysql实现上的一致性，简便性

可以看出上了4把锁：3条已匹配记录上了Next-key Lock，向右查找到不匹配的30也上了Next-key Lock。

其实这里不知道你会不会有这个疑问：对于聚族索引来说，值是唯一的，既然已经匹配到最大的20了，中止是不是更好？为什么还要继续向右？

如果是<=21，因为21不存在，所以需要继续向右扫描直到查找到30，上一把Next-key Lock倒可以理解！

所以这里可以认为是一个潜在的bug，但MySQL官方仍维持当前实现方式

对于 <= 在聚集索引上来说，我们得出的结果是（实际和 < 一样）：

聚集索引上, 所有匹配的索引记录上Next-key Lock;
向右扫描聚集索引, 直到找到不匹配的索引记录上Next-key Lock.

大于

我们再来验证 id > 10：


begin;
update t_user set remark = '技术自由圈' where id > 10;

这时匹配的记录有 15、20、30、40，所以会上4把Next-key Lock，对于40后的间隙，是对supremum上了Next-key Lock，这个很好理解，没什么特殊的，请看下图：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

对于 > 在聚集索引上来说，我们得出的结果是（实际和 <, <= 类似）：

聚集索引上, 所有匹配的索引记录上Next-key Lock;
对supremum (上界限伪值) 上Next-key Lock：锁的是最大值后的间隙;

大于等于

我们最后来看一下 id >= 10，这个有点特殊：


begin;
update t_user set remark = '技术自由圈' where id >= 10;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

对于 >= 我们得到的结果是：

聚集索引上, 所有 > 的索引记录上Next-key Lock;
聚集索引上, 等值(=) 会上Record Lock，当然：如果没有等值(=) 就不会上锁，我已验证 id>=11，比较好理解，不做赘述！
对supremum (上界限伪值) 上Next-key Lock：锁的是最大值后的间隙;

范围组合


begin;
update t_user set remark = '技术自由圈' where id > 10 and id < 20;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

由图可知，对2条索引记录上锁：匹配：15，next-key：20

聚集索引上, 所有匹配的索引记录上Next-key Lock;
向右扫描聚集索引, 直到找到不匹配的索引记录上Next-key Lock.

聚集索引范围查询加锁规则小结

对于聚集索引下的范围查询 <、<=、>、>=，无论是否组合，都会遵循如下规则：

所有匹配的索引记录：只有>= 的等值(=)匹配上Record Lock，其它上Next-key Lock;
对于 < 和 <=：向右扫描聚集索引，直到找到不匹配的索引记录上Next-key Lock.
对于 > 和 >=，会对supremum (上界限伪值) 上Next-key Lock：锁的是聚集索引最大值后面的间隙;
简单立即就是对匹配的范围加锁

5.2 非聚族索引加锁规则

非聚族索引规则分析

我们在Session2 执行SQL如下（按work_number < 20）：


begin;
update t_user set remark = '技术自由圈' where work_number < 20;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

这里共匹配2条记录：work_number = 10 、15。

共上了 6 把锁，3把锁在唯一索引上：

( 1 ) work_number = 20 （向右扫描到的第一个不匹配记录）: Next-key Lock；

(2) work_number = 10（匹配记录） : Next-key Lock；

(3) work_number = 15（匹配记录） : Next-key Lock；

3把锁在聚集索引上：

(1) id = 20 （向右扫描到的第一个不匹配记录）: Record Lock；

(2) id = 10（匹配记录） : Record Lock；

(3) id = 15（匹配记录） : Record Lock；

到这，我猜你肯定认为和聚集索引一样有结果了，请看好了，好戏即将上演~

我改下sql，sql语句本身没变，只是 20换成30：


begin;
update t_user set remark = '技术自由圈' where work_number < 30;

这里共匹配3条记录，分别是work_number = 10 、15、20。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

我们看一下锁监视器：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

非聚族索引，没有索引(没有回表因为成本太高)：

那么就会把所有聚集索引记录和间隙都锁上，就是所谓的锁表，或叫行锁升表锁

这里对聚集索引上了6把锁！！！

表里所有的5条聚集索引记录都上了Next-key Lock，还把supremum上了Next-key Lock。

相当于锁住了所有行和间隙，看一下explain的结果：


explain update t_user set remark = '技术自由圈' where work_number < 30;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

possible_keys： uk_work_number，意思说可能走的索引是uk_work_number
key：PRIMARY，意思说实际走的索引是聚族索引
type：index，意思说扫描了整个索引树

所以：这条SQL 并没有使用索引，而使用的是全表扫描。

这里其实是索引相关的知识，也就是索引失效了，实际是通过索引成本计算，得出全表扫描的cost(4.1) 小于走非聚族索引再回表的cost(4.61)：

用Optimizer工具在确认下：

开启Optimizer Trace（会话级生效）



-- 开启Optimizer Trace（会话级生效）
SET SESSION optimizer_trace = 'enabled=on';
-- 可选：设置输出格式为多行（默认off，更易阅读）
SET SESSION end_markers_in_json = on; 

--查看
SHOW VARIABLES LIKE 'optimizer_trace';

SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

-- 4. 关闭追踪（可选）
SET SESSION optimizer_trace = 'enabled=off';

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

说白了，就是表里一共才5条记录，这个范围就匹配了3条记录，我用聚族索引先查id，再用id回表去修改，还不如直接遍历全表来的快！！！

实际项目里，表里的数据一般不会这么少，所以这个示例的修改占比(60%)还是很高的，所以才造成了全表扫描(全表成本低于使用索引)。

非聚族索引小结

对于非聚族索引下的范围查询 <、<=、>、>=，无论是否组合，都会遵循如下规则：

场景一：如果走了普通索引：

在该索引上，所有匹配的索引记录上Next-key Lock，对应的聚集索引上Record Lock;
对于 < 和 <=，会在该索引上向右扫描，直到找到不匹配的索引记录上Next-key Lock，对应的聚集索引上Record Lock;
对于 > 和 >=，会对supremum (上界限伪值) 上Next-key Lock：锁的是该索引最大值后面的间隙;

场景二：如果没走普通索引，那么就会把所有聚集索引记录和间隙都锁上，就是所谓的锁表，或叫行锁升表锁.

六：行锁升表锁分析

行锁升表锁分析有两种情况

无索引
索引失效

6.1 什么是行锁升表锁？

MySQL会把所有聚集索引记录和间隙都锁上，称之为锁表，或叫行锁升表锁

但是行锁升表锁，锁的类型是不变，只是把所有的行和间隙加锁，看看起来像表锁，跟真正的表锁还是有区别的，这里要澄清下，如下图：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

6.2 为什么行锁要升表锁？

InnoDB引擎的 3种行锁算法(Record Lock、Gap Lock、Next-key Lock)，都是锁定的索引，当触发X锁(写锁)的where 条件无索引或索引失效时, 查找的方式就会变成全表扫描，也就是扫描所有的聚集索引记录

那为什么要（行锁）Record Lock，和间隙锁（Gap Lock、Next-key Lock），分别是用来解决什么问题呢？

加行锁（Record Lock）的原因：在RR，RC隔离级别下，加锁记录不允许被修改，也就是解决脏读，不可重复读问题
加间隙锁（ Gap Lock、Next-key Lock ）的原因：在RR隔离级别下，加间隙锁，是解决当前读下的幻读问题。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

什么是脏读、不可重复读、幻读？这里通过3张图简单回顾

第一：脏读：

当前事务(A)中可以读到其他事务(B)未提交的数据

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

第二：不可重复读：

在事务A中先后两次读取同一个数据，两次读取的结果不一样，也就是在事务中读到了另一个事务提交的数据。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

第三：幻读：

在事务A中按照某个范围条件先后两次查询数据库，因为没有间隙锁，两次查询结果的条数不同，这种现象称为幻读

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

6.3 行锁升级表锁的场景

无索引

例如, 下面这个sql的 remark列不是索引列 , 如果按remark更新就是无索引更新.


begin;
update t_user set remark = '技术自由圈' where remark = '技术自由圈';

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

无索引:走全表扫描

在聚族索引上，所有记录加临键锁
supremum上界限伪值，加临键锁
上面两个相当于表锁

索引失效

索引会不生效呢？主要有以下十大经典场景

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

索引失效详细分析参考：

索引失效的情况有很多, 我们不做详细分析, 参考上面两个文章，

上文也分析了范围查询时，虽然有索引，但是优化器根据成本评估，因为回表等原因，优化器选择的全表扫描。

6.4 锁膨胀，如何分析排查?

其实这个和死锁排查，类似的。具体参考下面的文章

MySQL死锁是什么，如何解决？（史上最全）

可以通过查看 InnoDB_row_lock相关变量，和INFORMATION_SCHEMA系统库，来排查

查看InnoDB_row_lock%相关变量


mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

字段	说明
Innodb_row_lock_current_waits	当前正在等待锁定的数量
Innodb_row_lock_time	等待总时长: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg	等待平均时长: 每次等待所花平均时间
Innodb_row_lock_time_max	从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits	等待总次数: 系统启动后到现在总共等待的次数

从上述值，我们可以看出我们行锁的整体情况，有助于我们分析。

启动三个session，

session1执行，因为没有索引，升级为表锁，不要commit，rollback


begin;
 update t_user set remark = '技术自由圈' where remark = '技术自由圈';

session2执行，拿不到锁，等待（默认50S）


insert into t_user(`id`,`name`,`age`,`work_number`,`remark`) values(11,'肖恩',1,1,NULL);

session3查看，可以看到一个等锁进程


mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

查看 INFORMATION_SCHEMA系统库

我们可以通过 INFORMATION_SCHEMA系统库提供的：查看事务、锁、锁等待的数据表来分析.


-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 查看连接情况
select * from INFORMATION_SCHEMA.PROCESSLIST;

通过 INNODB_LOCK_WAITS 可以找出阻塞的事务id和锁id


-- 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

字段	说明
requesting_trx_id	请求的事务id
requested_lock_id	请求的锁id
blocking_trx_id	阻塞的事务id
blocking_lock_id	阻塞的锁id

这里模拟一个锁等待，然后查询，可以清晰的看到谁阻塞了谁



mysql> select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 289040            | 289040:1123:3:39  | 289039          | 289039:1123:3:39 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

温馨提示：只有发生锁等待才有数据

通过 INNODB_LOCKS 可以查看上锁的详细信息


-- 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;

这和我们通过show engine innodb status\G; 看到的结果类似, 略…, 也是只有发生阻塞才会有数据.

通过 INNODB_TRX 可以查看事务的状态、阻塞开始时间、阻塞的sql、线程id等等



-- 查看事务
 select * from INFORMATION_SCHEMA.INNODB_TRX;

这个表很关键, 对于我们排查来说必不可少, 一些关键字段说明如下：

字段	说明
trx_id	事务id
trx_state	事务状态，LOCK WAIT代表发生了锁等待
trx_started	事务开始时间
trx_requested_lock_id	请求锁id, 事务当前正在等待锁的标识，可以join关联INNODB_LOCKS.lock_id
trx_wait_started	事务开始锁等待的时间
trx_weight	事务的权重
trx_mysql_thread_id	事务线程 ID，可以join关联PROCESSLIST.ID
trx_query	事务正在执行的 SQL 语句
trx_operation_state	事务当前操作状态
trx_isolation_level	当前事务的隔离级别

当发生阻塞时，我们来看一下数据：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

一目了然，哪个SQL从什么时间开始阻塞，线程id是多少，看的一清二楚.

通过 PROCESSLIST 可以查看连接情况



-- 查看连接情况
select * from INFORMATION_SCHEMA.PROCESSLIST;

通过这个表，我们可以定位到事务所在的主机.

字段	说明
ID	线程ID, 可以JOIN INNODB_TRX.trx_requested_lock_id
USER	连接用户
HOST	连接主机 ip:port
DB	连接的数据库

如何kill某个事务?

通过对上面的表进行查询, 当我们发现某个事务阻塞了很多事务, 并且执行时间很长时, 我们可以手动中止它, 只需要找到INNODB_TRX.trx_mysql_thread_id，然后调用kill命令：



kill {
   INNODB_TRX.trx_mysql_thread_id}

Mysql 的死锁分析

MySQL死锁是什么，如何解决？（史上最全）

6.5 表锁的总结：以下情况会直接用表锁

1、对应的存储引擎没有行级锁（例如：MyIASM）

2、使用LOCK TABLES语句手动锁定表时，MySQL将锁定整个表以确保其他会话不能访问它。

3、当执行 DDL （使用ALTER TABLE、TRUNCATE TABLE等）语句去修改表结构时，会使用表级锁。

4、在查询或更新操作中，如果涉及到大部分数据（占全表的80%或更多），通常会使用表级锁，而不是行级锁。使用表级锁的原因包括以下几点：

1）效率考虑：当涉及大量数据时，使用行级锁可能导致大量的锁竞争和锁粒度的细化，这会影响性能，因为每个被锁定的行都需要维护锁状态。表级锁的粒度更大，因此更高效。

2）简化锁管理：管理大量行级锁可能会导致锁管理的复杂性增加，因为需要跟踪和管理大量锁对象。表级锁更容易管理，因为只有一个锁对象。

3）避免死锁：行级锁可能导致死锁问题的发生，因为大量的锁定请求可能形成复杂的锁依赖关系。表级锁可以降低死锁风险。

4）操作一致性：当需要执行大规模数据操作（例如，更新表的大部分数据）时，使用表级锁可以确保整个操作是一致的，因为整个表都被锁定，不会发生部分数据被更新而其他部分未更新的情况。

6.6 锁膨胀的总结：以下情况会把行锁升级为表锁:

场景1：无索引

无锁引更新字段， mysql 直接锁表。

场景2：索引失效场景

1、类型转换

检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，可以清楚地看到了这一点。

（数据库类型为varchar时查询用数字类型时用不了索引。但数据库类型为int、datetime时查询类型为字符串，也可以走索引）

2、即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，也会自动升级为表锁

3、其他的索引失效场景

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

6.7 如何避免行锁升表锁?

隔离级别‌：推荐使用读已提交(RC)事务隔离级别这条非常重要！对于读已提交(RC)事务隔离级别，由于没有间隙锁(Gap Lock)，所以它的加锁规则相当简单，都是针对匹配索引记录加Record Lock，因为不用解决不可重复读和幻读问题，所以也就不存在锁表了。
禁止where条件使用无索引列进行更新/删除这是我们最应该做到的！除了会锁表，性能也是真的不好！
尽可能使用聚集索引进行更新/删除这是我们能做到的最优做法！
SQL规范‌：避免在索引列上使用函数、运算或类型转换，防止索引失效。使用EXPLAIN分析执行计划，确保走索引扫描。
‌事务控制‌：保持事务短小精悍，及时提交释放锁资源。避免大事务长时间持有锁，必要时拆分批量操作为小批次处理。涉及事务加锁的sql语句尽可能放在事务最后执行！
‌系统监控‌：定期检查锁等待和全表扫描情况，通过 performance_schema 监控锁争用。
‌架构设计‌：热点数据考虑分库分表，降低单表锁冲突概率。

遇到问题，找老架构师取经

借助此文，尼恩给解密了一个高薪的秘诀，大家可以放手一试。保证屡试不爽，涨薪 100%-200%。

后面，尼恩java面试宝典回录成视频，给大家打造一套进大厂的塔尖视频。

通过这个问题的深度回答，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

在面试之前，建议大家系统化的刷一波 5000页《尼恩Java面试宝典PDF》，里边有大量的大厂真题、面试难题、架构难题。

很多小伙伴刷完后，吊打面试官，大厂横着走。

在刷题过程中，如果有啥问题，大家可以来找 40岁老架构师尼恩交流。

另外，如果没有面试机会，可以找尼恩来改简历、做帮扶。

遇到职业难题，找老架构取经，可以省去太多的折腾，省去太多的弯路。

尼恩指导了大量的小伙伴上岸，前段时间，刚指导 32岁高中生，冲大厂成功。特批成为架构师，年薪 50W，逆天改命！！！。

狠狠卷，实现 “offer自由” 很容易的，前段时间一个武汉的跟着尼恩卷了2年的小伙伴，在极度严寒/痛苦被裁的环境下， offer拿到手软，实现真正的 “offer自由” 。

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

滴滴面试：明明 mysql 加的是 行锁，怎么就变 表锁 了？

本文 的 原文 地址

一：尼恩说在前面：

二： 锁膨胀 (行锁 变 表锁) 的现象

哪些场景会造成行锁升表锁？

三：Mysql 锁基础原理

3.1 从操作的类型进行的mysql锁的分类

读锁（S锁、共享锁）

写锁（X锁、排它锁）

意向锁

3.2 从操作的粒度进行的mysql锁的分类

表级锁：

行级锁：

3.3 InnoDB存储引擎三种行锁模式

1 记录锁(Record Locks)

2 间隙锁(Gap Locks)

3 临键锁(Next-Key Locks)

3.4 快照读 (snapshot read)与当前读 (current read)

快照读 的底层原理：

当前读 的底层原理：

1. 索引页（Index Pages）

2. 数据页（Data Pages）

3. Undo页（Undo Pages）

4. 插入缓存（Insert Buffer）

MVCC+undolog实现的无锁 快照读

不同隔离级别下的快照读

四 ： 锁膨胀/锁优化分析之一： 索引等值匹配 加锁规则 实操

锁膨胀/锁优化分析 分为两种情况：

索引等值匹配分析

4.1：锁膨胀分析之： 数据准备

4.2 聚族索引 加锁规则

聚族索引 的 索引等值匹配 实操分析

聚族索引 的 索引无值匹配 实操分析

4.3 唯一索引 加锁规则

索引等值匹配

索引无值匹配

4.4 普通索引 加锁规则

索引等值匹配

索引无值匹配

索引等值匹配 /索引无值匹配 加锁规则 总结

五：范围查询加锁规则 实操

5.1 聚族索引 加锁规则

小于

小于等于

大于

大于等于

范围组合

聚集索引 范围查询 加锁规则 小结

5.2 非聚族索引 加锁规则

非聚族索引规则分析

非聚族索引 小结

六：行锁升表锁分析

6.1 什么是行锁升表锁？

6.2 为什么行锁要升表锁？

6.3 行锁升级表锁的场景

无索引

索引失效

6.4 锁膨胀 ， 如何分析排查?

查看InnoDB_row_lock%相关变量

查看 INFORMATION_SCHEMA系统库

Mysql 的死锁分析

6.5 表锁的总结： 以下情况会 直接 用 表锁

6.6 锁膨胀的总结：以下情况会 把 行锁升级为 表锁:

场景1：无索引

滴滴面试：明明 mysql 加的是行锁，怎么就变表锁了？

本文的原文地址

二：锁膨胀 (行锁变表锁) 的现象

快照读的底层原理：

当前读的底层原理：

MVCC+undolog实现的无锁快照读

四：锁膨胀/锁优化分析之一：索引等值匹配加锁规则实操

锁膨胀/锁优化分析分为两种情况：

4.1：锁膨胀分析之：数据准备

4.2 聚族索引加锁规则

聚族索引的索引等值匹配实操分析

聚族索引的索引无值匹配实操分析

4.3 唯一索引加锁规则

4.4 普通索引加锁规则

索引等值匹配 /索引无值匹配加锁规则总结

五：范围查询加锁规则实操

5.1 聚族索引加锁规则

聚集索引范围查询加锁规则小结

5.2 非聚族索引加锁规则

非聚族索引小结

6.4 锁膨胀，如何分析排查?

6.5 表锁的总结：以下情况会直接用表锁

6.6 锁膨胀的总结：以下情况会把行锁升级为表锁:

场景2：索引失效场景

6.7 如何避免行锁升表锁?