mysql_四InnoDB锁

一条SQL是如何加锁的，一些加锁规则、如何分析和解决死锁问题

为什么需要加锁呢？
InnoDB的七种锁介绍
一条SQL是如何加锁的
RR隔离级别下的加锁规则
如何查看事务加锁情况
死锁案例分析

1. 为什么需要加锁？

数据库为什么需要加锁呢？

在日常生活中，如果你心情不好。想要一个人静静，不想被比别人打扰，你就可以把自己关进房间里，并且反锁。

同理，对于MySQL数据库来说的话，一般的对象都是一个事务一个事务来说的。所以，如果一个事务内，正在写某个SQL，我们肯定不想它被别的事务影响到嘛？因此，数据库设计大叔，就给被操作的SQL加上锁。

专业一点的说法: 如果有多个并发请求存取数据，在数据就可能会产生多个事务同时操作同一行数据。如果并发操作不加控制，不加锁的话，就可能写入了不正确的数据，或者导致读取了不正确的数据，破坏了数据的一致性。因此需要考虑加锁。

1.1 事务并发存在的问题

脏读:一个事务A读取到事务B未提交的数据，就是脏读。
不可重复读：事务A被事务B干扰到了！在事务A范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。
幻读：事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

select @@transaction_isolation; -- 查看数据库隔离级别
SET GLOBAL innodb_status_output=ON; -- 开启输出
SET GLOBAL innodb_status_output_locks=ON; -- 开启锁信息输出
SHOW ENGINE INNODB STATUS； --获取 InnoDB 存储引擎的详细状态信息

2. InnoDB的七种锁介绍

2.1 共享/排他锁

InnoDB呢实现了两种标准的行级锁：共享锁（简称S锁）、排他锁（简称X锁）。

共享锁：简称为S锁，在事务要读取一条记录时，需要先获取该记录的S锁。
排他锁：简称X锁，在事务需要改动一条记录时，需要先获取该记录的X锁。

如果事务T1持有行R的S锁，那么另一个事务T2请求访问这条记录时，会做如下处理：

T2 请求S锁立即被允许，结果 T1和T2都持有R行的S锁
T2 请求X锁不能被立即允许,此操作会阻塞

如果T1持有行R的X锁，那么T2请求R的X、S锁都不能被立即允许，T2 必须等待T1释放X锁才可以，因为X锁与任何的锁都不兼容。

X锁和S锁是对于行记录来说的话，因此可以称它们为行级锁或者行锁。我们认为行锁的粒度就比较细，其实一个事务也可以在表级别下加锁，对应的，我们称之为表锁。给表加的锁，也是可以分为X锁和S锁的哈。

如果一个事务给表已经加了S锁，则：

别的事务可以继续获得该表的S锁，也可以获得该表中某些记录的S锁。
别的事务不可以继续获得该表的X锁，也不可以获得该表中某些记录的X锁。

如果一个事务给表加了X锁，那么

别的事务不可以获得该表的S锁，也不可以获得该表某些记录的S锁。
别的事务不可以获得该表的X锁，也不可以继续获得该表某些记录的X锁。

2.2 意向锁

什么是意向锁呢？意向锁是一种不与行级锁冲突的表级锁。未来的某个时刻，事务可能要加共享或者排它锁时，先提前声明一个意向。注意一下，意向锁，是一个表级别的锁哈。

为什么需要意向锁呢？ 或者换个通俗的说法，为什么要加共享锁或排他锁时的时候，需要提前声明个意向锁呢呢？

因为InnoDB是支持表锁和行锁共存的，如果一个事务A获取到某一行的排他锁，并未提交，这时候事务B请求获取同一个表的表共享锁。因为共享锁和排他锁是互斥的，因此事务B想对这个表加共享锁时，需要保证没有其他事务持有这个表的表排他锁，同时还要保证没有其他事务持有表中任意一行的排他锁。

然后问题来了，你要保证没有其他事务持有表中任意一行的排他锁的话，去遍历每一行？这样显然是一个效率很差的做法。为了解决这个问题，InnoDB的设计大叔提出了意向锁。

意向锁是如何解决这个问题的呢？ 我们来看下

意向锁分为两类：

意向共享锁：简称IS锁，当事务准备在某些记录上加S锁时，需要现在表级别加一个IS锁。
意向排他锁：简称IX锁，当事务准备在某条记录上加上X锁时，需要现在表级别加一个IX锁。

比如：

select ... lock in share mode，要给表设置IS锁;
select ... for update，要给表设置IX锁;

意向锁又是如何解决这个效率低的问题呢：

如果一个事务A获取到某一行的排他锁，并未提交,这时候表上就有意向排他锁和这一行的排他锁。这时候事务B想要获取这个表的共享锁，此时因为检测到事务A持有了表的意向排他锁，因此事务A必然持有某些行的排他锁，也就是说事务B对表的加锁请求需要阻塞等待，不再需要去检测表的每一行数据是否存在排他锁啦。这样效率就高很多啦。

意向锁仅仅表明意向的锁，意向锁之间并不会互斥，是可以并行的，整体兼容性如下图所示：

2.3 记录锁（Record Lock）

记录锁是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果c1字段是主键或者是唯一索引的话，这个SQL会加一个记录锁（Record Lock）

记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB也会隐式的创建一个索引，并使用这个索引实施记录锁。它会阻塞其他事务对这行记录的插入、更新、删除。

一般我们看死锁日志时，都是找关键词，比如lock_mode X locks rec but not gap），就表示一个X型的记录锁。记录锁的关键词就是rec but not gap。以下就是一个记录锁的日志：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.4 间隙锁（Gap Lock）

为了解决幻读问题，InnoDB引入了间隙锁(Gap Lock)。间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。它锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

比如lock_mode X locks gap before rec表示X型gap锁。以下就是一个间隙锁的日志：

RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

2.5 临键锁(Next-Key Lock)

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。说得更具体一点就是:临键锁会封锁索引记录本身，以及索引记录之前的区间，即它的锁区间是前开后闭，比如(5,10]。

如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

2.6 插入意向锁

插入意向锁,是插入一行记录操作之前设置的一种间隙锁。这个锁释放了一种插入方式的信号。它解决的问题是：多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，就不会阻塞彼此。

假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了4、7之间的间隙，但是不阻塞对方因为插入行不冲突。以下就是一个插入意向锁的日志：

2.7 自增锁

自增锁是一种特殊的表级别锁。它是专门针对AUTO_INCREMENT类型的列，对于这种列，如果表中新增数据时就会去持有自增锁。简言之，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

3. 一条SQL是如何加锁的呢？

介绍完InnoDB的七种锁后，我们来看下一条SQL是如何加锁的哈，现在可以分9种情况进行：

组合一：查询条件是主键，RC隔离级别
组合二：查询条件是唯一索引，RC隔离级别
组合三：查询条件是普通索引，RC隔离级别
组合四：查询条件上没有索引，RC隔离级别
组合五：查询条件是主键，RR隔离级别
组合六：查询条件是唯一索引，RR隔离级别
组合七：查询条件是普通索引，RR隔离级别
组合八：查询条件上没有索引，RR隔离级别
组合九：Serializable隔离级别

3.1 查询条件是主键 + RC隔离级别

在RC（读已提交） 的隔离级别下，对查询条件列是主键id的话，会加什么锁呢？

我们搞个简单的表，初始化几条数据：

create table t1 (id int,name varchar(16),primary key ( id));
insert into t1 values(1,'a'),(3,'c'),(6,'b'),(9,'a'),(10,'d');

假设给定SQL：delete from t1 where id = 6;，id是主键。在RC隔离级别下，只需要将主键上id = 6的记录，加上X锁即可。

我们来验证一下吧，先开启事务会话A，先执行以下操作：

begin;
//删除id=6的这条记录
delete from t1 where id = 6;

接着开启事务会话B

begin;
update t1 set name='b1' where id =6;
//发现这个阻塞等待，最后超时释放锁了

事务会话B对id=6的记录执行更新时，发现阻塞了，打开看下加了什么锁。发现是因为id=6这一行加了一个X型的记录锁

如果我们事务B不是对id=6执行更新，而是其他记录的话，是可以顺利执行的，如下：

结论就是，在RC（读已提交） 的隔离级别下，对查询条件是主键id的场景，会加一个排他锁（X锁），或者说加一个X型的记录锁。

3.2 查询条件是唯一索引+RC隔离级别

如果查询条件id，只是一个唯一索引呢？那在RC（读提交隔离级别下），又加了什么锁呢？我们搞个新的表，初始化几条数据：

create table t2 (name varchar(16),id int,primary key (name),unique key(id));
insert into t2 values('a',1),('c',3),('b',6),('d',9);

id是唯一索引，name是主键的场景下，我们给定SQL：delete from t2 where id = 6;。

在RC隔离级别下，该SQL需要加两个X锁，一个对应于id 唯一索引上的id = 6的记录，另一把锁对应于聚簇索引上的[name=’b’,id=6]的记录。

为什么主键索引上的记录也要加锁呢？

如果并发的一个SQL，是通过主键索引来更新：update t2 set id = 666 where name = 'b';此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

3.3 查询条件是普通索引 + RC隔离级别

如果查询条件是普通的二级索引，在RC（读提交隔离级别下），又加了什么锁呢？

若id列是普通索引，那么对应的所有满足SQL查询条件的记录，都会加上锁。同时，这些记录对应主键索引，也会上锁。我们初始化下表结构和数据

create table t3 (name varchar(16),id int,primary key (name),key(id));
insert into t3 values('a',1),('c',3),('b',6),('e',6),('d',9);

加锁示意图如下：

我们来验证一下，先开启事务会话A，先执行以下操作：

begin;
//删除id=6的这条记录
delete from t3 where id = 6;

接着开启事务会话B

begin;
update t3 set id=7 where name ='e';
//发现这个阻塞等待，最后超时释放锁了

事务会话B为什么会阻塞等待超时，是因为事务会话A的delete语句确实有加主键索引的X锁

3.4 查询条件列无索引+RC隔离级别

如果id没有加索引，只是一个常规的列，在RC（读提交隔离级别下），又加了什么锁呢？

若id列上没有索引，MySQL会走聚簇索引进行全表扫描过滤。每条记录都会加上X锁。但是，为了效率考虑，MySQL在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则。

初始化下表结构和数据

create table t4 (name varchar(16),id int,primary key (name));
insert into t4 values('a',1),('c',3),('b',6),('e',6),('d',9);

验证流程如下，先开启事务会话A，先执行以下操作：

begin;
//删除id=6的这条记录
delete from t4 where id = 6;

接着开启事务会话B

begin;
//可以执行，MySQL因为效率问题，解锁了
update t4 set name='f' where id=3;
//阻塞等待
update t4 set name='f' where id=6;

验证结果如下：

3.5 查询条件是主键+RR隔离级别

给定SQL：delete from t1 where id = 6;，如果id是主键的话，在RR隔离级别下，跟RC隔离级别，加锁是一样的，也都是在id = 6这条记录上加上X锁。大家感兴趣可以照着3.1小节例子，自己验证一下哈。

3.6 查询条件是唯一索引+RR隔离级别

给定SQL：delete from t1 where id = 6;，如果id是唯一索引的话，在RR隔离级别下，跟RC隔离级别，加锁也是一样的哈，加了两个X锁，id唯一索引满足条件的记录上一个，对应的主键索引上的记录一个。

3.7 查询条件是普通索引+RR隔离级别

如果查询条件是普通的二级索引，在RR（可重复读的隔离级别下），除了会加X锁，还会加间隙Gap锁。Gap锁的提出，是为了解决幻读问题引入的，它是一种加在两个索引之间的锁。

假设有表结构和初始化数据如下：

CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINE=InnoDB;
insert into t5 values(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

如果一条更新语句update t5 set d=d+1 where c = 10，加锁示意图如下：

我们来验证一下吧，先开启事务会话A，先执行以下操作：

begin;
update t5 set d=d+1 where c = 10;

接着开启事务会话B

begin;
insert into t5 values(12,12,12);
//阻塞等待，最后超时释放锁了

验证流程图如下：

为什么会阻塞呢？因此c=10这个记录更新时，不仅会有两把X锁，还会把区间（10,15）加间隙锁，因此要插入（12,12,12）记录时，会阻塞。

3.8 查询条件列无索引+RR隔离级别

如果查询条件列没有索引呢？又是如何加的锁呢？

假设有表结构和数据如下：

CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
insert into t5 values(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

给定一条更新语句update t5 set d=d+1 where c = 10，因为c列没有索引，加锁示意图如下：

如果查询条件列没有索引，主键索引的所有记录，都将加上X锁，每条记录间也都加上间隙Gap锁。大家可以想象一下，任何加锁并发的SQL，都是不能执行的，全表都是锁死的状态。如果表的数据量大，那效率就更低。

在这种情况下，MySQL做了一些优化，即semi-consistent read，对于不满足条件的记录，MySQL提前释放锁，同时Gap锁也会释放。而semi-consistent read是如何触发的呢：要么在Read Committed隔离级别下；要么在Repeatable Read隔离级别下，设置了innodb_locks_unsafe_for_binlog参数。但是semi-consistent read本身也会带来其他的问题，不建议使用。

我们来验证一下哈，先开启事务会话A，先执行以下操作：

begin;
update t5 set d=d+1 where c = 20;

接着开启事务会话B

begin;
insert into t5 values(16,16,16);
//插入阻塞等待
update t5 set d=d+1 where c = 16;
//更新阻塞等待

我们去更新一条不存在的c=16的记录，也会被X锁阻塞的。验证如下：

3.9 Serializable串行化

在Serializable串行化的隔离级别下，对于写的语句，比如update account set balance= balance-10 where name=‘Jay’;，跟RC和RR隔离级别是一样的。不一样的地方是，在查询语句，如select balance from account where name = ‘Jay’;，在RC和RR是不会加锁的，但是在Serializable串行化的隔离级别，即会加锁。

4. RR隔离级别下，加锁规则到底是怎样的呢？

对于RC隔离级别，加的排他锁（X锁），是比较好理解的，哪里更新就锁哪里嘛。但是RR隔离级别，间隙锁是怎么加的呢？我们一起来学习一下。

对InnoDb的锁来说，面试的时候问的比较多，就是Record lock、Gap lock、Next-key lock。接下来我们来学习，RR隔离级别，到底一个锁是怎么加上去的

首先MySQL的版本，是5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。加锁规则一共包括：两个原则、两个优化和一个bug。

原则1：加锁的基本单位都是next-key lock。next-key lock（临键锁）是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁（Record lock）。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁（Gap lock）。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

假设有表结构和数据如下：

CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINE=InnoDB;
insert into t5 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

分7个案例去分析哈：

等值查询间隙锁
非唯一索引等值锁
主键索引范围锁
非唯一索引范围锁
唯一索引范围锁 bug
普通索引上存在”等值”的例子
limit 语句减少加锁范围

4.1 案例一：等值查询间隙锁

我们同时开启A、B、C三个会话事务，如下：

发现事务B会阻塞等待，而C可以执行成功。如下：

什么事务B会阻塞呢？

这是因为根据加锁原则1：加锁基本单位是next-key lock，因此事务会话 A的加锁范围是（5,10]，这里为什么是区间（5,10]，这是因为更新的记录，所在的表已有数据的区间就是5-10哈，又因为next-key lock是左开右闭的，所以加锁范围是（5,10]。
同时根据优化 2，这是一个等值查询 (id=6)，而id=10不满足查询条件。所以next-key lock退化成间隙Gap锁，因此最终加锁的范围是(5,10)。
然后事务Session B中，你要插入的是9,9在区间(5,10)内，而区间(5,10)都被锁了。因此事务B会阻塞等到。

为什么事务C可以正常执行呢？

这是因为锁住的区间是(5,10)，没有包括10，所以事务C可以正常执行。

4.2 案例二：非唯一索引等值锁

按顺序执行事务会话A、B、C，如下：

发现事务B可以执行成功，而C阻塞等待。如下：

为什么事务会话B没有阻塞，事务会话C却阻塞了？

事务会话A执行时，会给索引树c=5的这一行加上读共享锁。

根据加锁原则1，加锁单位是next-key lock，因此会加上next-key lock(0,5]。
因为c 只是普通索引，所以仅访问c=5这一条记录时不会马上停下来，需要继续向右遍历，查到c=10才结束。根据加锁原则2，访问到的都要加锁，因此要给(5,10]加next-key lock。
由加锁优化2：等值判断，向右遍历，最后一个值10不满足c=5 这个等值条件，因此退化成间隙锁 (5,10)。
根据加锁原则 2 ：只有访问到的对象才会加锁，事务A的这个查询使用了覆盖索引，没有回表，并不需要访问主键索引，因此主键索引上没有加任何锁，事务会话B是对主键id的更新，因此事务会话B的update语句不会阻塞。
但是事务会话C，要插入一个（6,6,6) 的记录时，会被事务会话A的间隙锁(5,10)锁住，因此事务会话C阻塞了。

4.3 案例三：主键索引范围锁

主键范围查询又是怎么加锁的呢？比如给定SQL:

select * from t5 where id>=10 and id<11 for update;

按顺序执行事务会话A、B、C，如下：

执行结果如下：

发现事务会话B中，插入12，即insert into t5 values(12,12,12);时，阻塞了，而插入6，insert into t5 values(6,6,6);却可以顺利执行。同时事务C中，Update t5 set d=d+1 where id =15;也会阻塞，为什么呢？

事务会话A执行时，要找到第一个id=10的行：

根据加锁原则1：加锁单位是next-key lock，因此会加上next-key lock(5,10]。
又因为id是主键，也就是唯一值，因此根据优化1：索引上的等值查询，给唯一索引加锁时，next-key lock退化为行锁（Record lock）。所以只加了id=10这个行锁。
范围查找就往后继续找，找到id=15这一行停下来，因此还需要加next-key lock(10,15]。

事务会话A执行完后，加的锁是id=10这个行锁，以及临键锁next-key lock(10,15]。这就是为什么事务B插入6那个记录可以顺利执行，插入12就不行啦。同理，事务C那个更新id=15的记录，也是会被阻塞的。

4.4 案例四：非唯一索引范围锁

如果是普通索引，范围查询又加什么锁呢？按顺序执行事务会话A、B、C，如下：

执行结果如下：

发现事务会话B和事务会话C的执行SQL都被阻塞了。

这是因为，事务会话A执行时，要找到第一个c=10的行：

根据加锁原则1：加锁单位是next-key lock，因此会加上next-key lock(5,10]。又因为c不是唯一索引，所以它不会退化为行锁。因此加的锁还是next-key lock(5,10]。
范围查找就往后继续找，找到id=15这一行停下来，因此还需要加next-key lock(10,15]。

因此事务B和事务C插入的insert into t5 values(6,6,6);和Update t5 set d=d+1 where c =15; 都会阻塞。

4.5 案例五：唯一索引范围锁 bug

前面四种方案中，加锁的两个原则和两个优化都已经用上啦，那个唯一索引范围bug是如何触发的呢？

按顺序执行事务会话A、B、C，如下：

执行结果如下：

发现事务B的更新语句Update t5 set d=d+1 where id =20; 和事务Cinsert into t5 values(18,18,18);的插入语句均已阻塞了。

这是因为，事务会话A执行时，要找到第一个id=15的行，根据加锁原则1：加锁单位是next-key lock，因此会加上next-key lock(10,15]。因为id是主键，即唯一的，因此循环判断到 id=15 这一行就应该停止了。但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，直到扫描到id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个 next-key lock 也会被锁上。

所以，事务B要更新 id=20 这一行时，会阻塞锁住。同样地事务会话C要插入id=16的一行，也会被锁住。

4.6 案例六：普通索引上存在”等值”的例子

如果查询条件列是普通索引，且存在相等的值，加锁又是怎样的呢？

在原来t5表的数据基础上，插入：

insert into t5 values(28,10,66);

则索引树如下：

索引值有相等的，但是它们对应的主键是有间隙的。比如（c=10，id=10）和（c=10，id=28）之间。

我们来看个例子，按顺序执行事务会话A、B、C，如下：

为什么事务B插入语句会阻塞，事务C的更新语句不会呢？

这是因为事务会话A在遍历的时候，先访问第一个c=10的记录。它根据原则 1，加一个(c=5,id=5) 到 (c=10,id=10)的next-key lock。
然后，事务会话A向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10) 到 (c=15,id=15)的间隙Gap锁。即事务会话A这个select...for update语句在索引 c 上的加锁范围，就是下图灰色阴影部分的：

因为c=13是这个区间内的，所以事务B插入insert into t5 values(13,13,13);会阻塞。因为根据优化2，已经退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙Gap锁,即不包括c=15，所以事务C，Update t5 set d=d+1 where c=15不会阻塞

4.7 案例七：limit 语句减少加锁范围

如果一个SQL有limit，会不会对加锁有什么影响呢？我们用4.6的例子，然后给查询语句加个limit：

Select * from t5 where c=10 limit 2 for update;

事务A、B执行如下：

发现事务B并没有阻塞，而是可以顺利执行

这是为什么呢？跟上个例子，怎么事务会话B的SQL却不会阻塞了，事务会话A的select只是加多了一个limit 2。

这是因为明确加了limit 2的限制后，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 c上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

索引平时我们写SQL的时候，比如查询select或者delete语句时，尽量加一下limit哈，你看着这个例子不就减少了锁范围了嘛，哈哈。

5. 如何查看事务加锁情况

INNODB_LOCKS 和 INNODB_LOCK_WAITS 在MySQL 8.0已被移除，其实就是不鼓励我们用这两个表来获取表信息。而我们还可以用show engine innodb status获取当前系统各个事务的加锁信息。

在看死锁日志的时候，我们一般先把这个变量innodb_status_output_locks打开哈，它是MySQL 5.6.16 引入的

set global  innodb_status_output_locks =on;

在RR隔离级别下，我们交替执行事务A和B：

show engine innodb status查看日志，如下：

TRANSACTIONS
------------
Trx id counter 1644854
Purge done for trx's n:o < 1644847 undo n:o < 0 state: running but idle
History list length 32
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283263895935640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1644853, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 11956, query id 563 localhost ::1 root update
insert into t5 values(6,6,6)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

------------------
TABLE LOCK table `test2`.`t5` trx id 1644853 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

这结构锁的关键词需要记住一下哈：

lock_mode X locks gap before rec表示X型的gap锁
lock_mode X locks rec but not gap表示 X型的记录锁（Record Lock）
lock mode X 一般表示 X型临键锁（next-key 锁）

以上的锁日志，我们一般关注点，是一下这几个地方：

TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED表示它在等这个锁
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting表示一个锁结构，这个锁结构的Space ID是267，page number是4，n_bits属性为80，对应的索引是c，这个锁结构中存放的锁类型是X型的插入意向Gap锁。
0: len 4; hex 8000000a; asc ;;对应加锁记录的详细信息，8000000a代表的值就是10，a的16进制是10。
TABLE LOCK table `test2`.`t5` trx id 1644853 lock mode IX 表示一个插入意向表锁

这个日志例子，其实理解起来，就是事务A持有了索引c的间隙锁（~，10），而事务B想获得这个gap锁，而获取不到，就一直在等待这个插入意向锁。

6. 手把手死锁案例分析

如果发生死锁了，我们应该如何分析呢？一般分为四个步骤：

show engine innodb status，查看最近一次死锁日志。
分析死锁日志，找到关键词TRANSACTION
分析死锁日志，查看正在执行的SQL
看SQL持有什么锁，又在等待什么锁。

CREATE TABLE t6 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINE=InnoDB;
insert into t6 values(5,5,5),(10,10,10);

我们开启A、B事务，执行流程如下：

分析死锁日志

show engine innodb status，查看最近一次死锁日志。如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-03 22:53:22 0x2eb4
*** (1) TRANSACTION:
TRANSACTION 1644867, ACTIVE 31 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5, OS thread handle 7068, query id 607 localhost ::1 root statistics
Select * from t6 where id=10 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644867 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000019193c; asc      <;;
 2: len 7; hex dd00000191011d; asc        ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1644868, ACTIVE 17 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 11956, query id 608 localhost ::1 root statistics
Select * from t6 where id=5 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644868 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000019193c; asc      <;;
 2: len 7; hex dd00000191011d; asc        ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644868 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000019193c; asc      <;;
 2: len 7; hex dd000001910110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

先找到关键词TRANSACTION，可以发现两部分的事务日志，如下：

查看正在执行，产生死锁的对应的SQL，如下：

查看分开两部分的TRANSACTION，分别持有什么锁，和等待什么锁。

所谓的死锁，其实就是，我持有你的需要的锁，你持有我需要的锁，形成相互等待的闭环。所以排查死锁问题时，照着这个思维去思考就好啦。

于2025 年 5 月 18 日2025 年 5 月 18 日由jwq发布

1. 为什么需要加锁？

1.1 事务并发存在的问题

2. InnoDB的七种锁介绍

2.1 共享/排他锁

2.2 意向锁

2.3 记录锁（Record Lock）

2.4 间隙锁（Gap Lock）

2.5 临键锁(Next-Key Lock)

2.6 插入意向锁

2.7 自增锁

3. 一条SQL是如何加锁的呢？

3.1 查询条件是主键 + RC隔离级别

3.2 查询条件是唯一索引+RC隔离级别

3.3 查询条件是普通索引 + RC隔离级别

3.4 查询条件列无索引+RC隔离级别

3.5 查询条件是主键+RR隔离级别

3.6 查询条件是唯一索引+RR隔离级别

3.7 查询条件是普通索引+RR隔离级别

3.8 查询条件列无索引+RR隔离级别

3.9 Serializable串行化

4. RR隔离级别下，加锁规则到底是怎样的呢？

4.1 案例一：等值查询间隙锁

4.2 案例二：非唯一索引等值锁

4.3 案例三：主键索引范围锁

4.4 案例四：非唯一索引范围锁

4.5 案例五：唯一索引范围锁 bug

4.6 案例六：普通索引上存在”等值”的例子

4.7 案例七：limit 语句减少加锁范围

5. 如何查看事务加锁情况

6. 手把手死锁案例分析

分析死锁日志

0 条评论

发表回复取消回复

count(*)为什么会慢？

Mysql查询好慢，除了索引，还有什么原因

Mysql的索引为什么使用B+树？

mysql_四InnoDB锁

于2025 年 5 月 18 日2025 年 5 月 18 日由jwq发布

1. 为什么需要加锁？

1.1 事务并发存在的问题

2. InnoDB的七种锁介绍

2.1 共享/排他锁

2.2 意向锁

2.3 记录锁（Record Lock）

2.4 间隙锁（Gap Lock）

2.5 临键锁(Next-Key Lock)

2.6 插入意向锁

2.7 自增锁

3. 一条SQL是如何加锁的呢？

3.1 查询条件是主键 + RC隔离级别

3.2 查询条件是唯一索引+RC隔离级别

3.3 查询条件是普通索引 + RC隔离级别

3.4 查询条件列无索引+RC隔离级别

3.5 查询条件是主键+RR隔离级别

3.6 查询条件是唯一索引+RR隔离级别

3.7 查询条件是普通索引+RR隔离级别

3.8 查询条件列无索引+RR隔离级别

3.9 Serializable串行化

4. RR隔离级别下，加锁规则到底是怎样的呢？

4.1 案例一：等值查询间隙锁

4.2 案例二：非唯一索引等值锁

4.3 案例三：主键索引范围锁

4.4 案例四：非唯一索引范围锁

4.5 案例五：唯一索引范围锁 bug

4.6 案例六：普通索引上存在”等值”的例子

4.7 案例七：limit 语句减少加锁范围

5. 如何查看事务加锁情况

6. 手把手死锁案例分析

分析死锁日志

0 条评论

发表回复 取消回复

相关文章

count(*)为什么会慢？

Mysql查询好慢，除了索引，还有什么原因

Mysql的索引为什么使用B+树？

发表回复取消回复