<返回更多

两万字详解InnoDB的锁

2022-05-06    三辦橘子
加入收藏

本文将跟大家聊聊InnoDb的锁，以及如何分析和解决死锁问题，希望对大家有帮助哈。

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

1. 为什么需要加锁？

为什么需要加锁呢？

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

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

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

1.1 事务并发存在的问题

1.2 一个加锁和不加锁对比的例子

我们知道MySQL数据库有四大隔离级别读已提交（RC）、可重复读（RR）、串行化、读未提交。如果是读未提交隔离级别，并发情况下，它是不加锁的，因此就会存在脏读、不可重复读、幻读的问题。

为了更通俗易懂一点，还是给大家举个例子吧，虽然东西挺简单的。假设现在有表结构：

CREATE TABLE `account` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_name_idx` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into account(id,name,balance)values (1,'Jay',100);
insert into account(id,name,balance)values (2,'Eason',100);
insert into account(id,name,balance)values (3,'Lin',100);

在READ-UNCOMMITTED（读未提交） 隔离级别下，假设现在有两个事务A、B：

两万字详解InnoDB的锁

 

手动验证了一把，流程如下：

两万字详解InnoDB的锁

 

由上图可以发现，事务A、B交替执行，事务A被事务B干扰到了，因为事务A读取到事务B未提交的数据,这就是脏读。这是因为在读未提交的隔离级别写操作，并没有对SQL加锁，因此产生了脏读这个问题。

我们再来看下，在串行化隔离级别下，同样的SQL执行流程，是怎样的？

两万字详解InnoDB的锁

 

为啥会阻塞等待超时呢？这是因为串行化隔离级别下，对写的SQL加锁啦。我们可以看下加了什么锁，命令如下：

SET GLOBAL innodb_status_output=ON; -- 开启输出
SET GLOBAL innodb_status_output_locks=ON; -- 开启锁信息输出
SHOW ENGINE INNODB STATUS

锁相关的输出内容如下：

两万字详解InnoDB的锁

 

我们可以看到了lock_mode X locks rec but not gap，它到底是一种什么锁呢？我们一起来学习下InnoDB的七种锁。

2. InnoDB的七种锁介绍

两万字详解InnoDB的锁

 

2.1 共享/排他锁

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

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

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

S锁和X锁的兼容关系如下：

两万字详解InnoDB的锁

 

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

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

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

2.2 意向锁

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

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

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

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

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

意向锁分为两类：

比如：

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

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

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

两万字详解InnoDB的锁

 

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之前的区间插入新的索引记录。

If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

2.6 插入意向锁

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

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

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

锁模式兼容矩阵（横向是已持有锁，纵向是正在请求的锁）：

两万字详解InnoDB的锁

 

2.7 自增锁

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

官方文档是这么描述的：

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

假设有表：

mysql> create table t0 (id int NOT NULL AUTO_INCREMENT,name varchar(16),primary key ( id));

mysql> show variables like '%innodb_autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set, 1 warning (0.01 sec)

设置事务A和B交替执行流程如下：

两万字详解InnoDB的锁

 

通过上图我们可以看到，当我们在事务A中进行自增列的插入操作时，另外会话事务B也进行插入操作，这种情况下会发生2个奇怪的现象：

自增锁是一个表级别锁，那为什么会话A事务还没结束，事务B可以执行插入成功呢？不是应该锁表嘛？这是因为在参数innodb_autoinc_lock_mode上，这个参数设置为1的时候，相当于将这种auto_inc lock弱化为了一个更轻量级的互斥自增长机制去实现，官方称之为mutex。

innodb_autoinc_lock_mode还可以设置为0或者2，

INSERT-LIKE:指所有的插入语句，包括： INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等。 Simple inserts:指在插入前就能确定插入行数的语句，包括：INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。 Bulk inserts: 指在插入钱不能确定行数的语句，包括：INSERT … SELECT/REPLACE … SELECT/LOAD DATA。

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

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

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锁即可。

两万字详解InnoDB的锁

 

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

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

接着开启事务会话B

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

验证流程图如下：

两万字详解InnoDB的锁

 

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

两万字详解InnoDB的锁

 

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

两万字详解InnoDB的锁

 

结论就是，在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]的记录。

两万字详解InnoDB的锁

 

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

如果并发的一个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);

加锁示意图如下：

两万字详解InnoDB的锁

 

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

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

接着开启事务会话B

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

实践流程如下：

两万字详解InnoDB的锁

 

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

两万字详解InnoDB的锁

 

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);

加锁示意图图下：

两万字详解InnoDB的锁

 

验证流程如下，先开启事务会话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;

验证结果如下：

两万字详解InnoDB的锁

 

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

给定SQL：delete from t1 where id = 6;，如果id是主键的话，在RR隔离级别下，跟RC隔离级别，加锁是一样的，也都是在id = 10这条记录上加上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，加锁示意图如下：

两万字详解InnoDB的锁

 

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

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

接着开启事务会话B

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

验证流程图如下：

两万字详解InnoDB的锁

 

为什么会阻塞呢？因此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列没有索引，加锁示意图如下：

两万字详解InnoDB的锁

 

如果查询条件列没有索引，主键索引的所有记录，都将加上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锁阻塞的。验证如下：

两万字详解InnoDB的锁

 

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串行化的隔离级别，即会加锁。

如文章开始第一小节的那个例子，就是类似的：

两万字详解InnoDB的锁

 

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

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

对InnoDb的锁来说，面试的时候问的比较多，就是Record lock、Gap lock、Next-key lock。接下来我们来学习，RR隔离级别，到底一个锁是怎么加上去的。丁奇的MySQL45讲有讲到，RR隔离级别，是如何加锁的。大家有兴趣可以去订购看下哈。非常不错的课程

首先MySQL的版本，是5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。加锁规则一共包括：两个原则、两个优化和一个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个案例去分析哈：

  1. 等值查询间隙锁
  2. 非唯一索引等值锁
  3. 主键索引范围锁
  4. 非唯一索引范围锁
  5. 唯一索引范围锁 bug
  6. 普通索引上存在"等值"的例子
  7. limit 语句减少加锁范围

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

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

两万字详解InnoDB的锁

 

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

两万字详解InnoDB的锁

 

为什么事务B会阻塞呢？

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

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

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

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

两万字详解InnoDB的锁

 

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

两万字详解InnoDB的锁

 

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

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

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

两万字详解InnoDB的锁

 

执行结果如下：

两万字详解InnoDB的锁

 

发现事务会话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的行：

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

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

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

两万字详解InnoDB的锁

 

执行结果如下：

两万字详解InnoDB的锁

 

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

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

  1. 根据加锁原则1：加锁单位是next-key lock，因此会加上next-key lock(5,10]。

又因为c不是唯一索引，所以它不会退化为行锁。因此加的锁还是next-key lock(5,10]。 2. 范围查找就往后继续找，找到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，如下：

两万字详解InnoDB的锁

 

执行结果如下：

两万字详解InnoDB的锁

 

发现事务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索引树如下：

两万字详解InnoDB的锁

 

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

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

两万字详解InnoDB的锁

 

执行结果如下：

两万字详解InnoDB的锁

 

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

两万字详解InnoDB的锁

 

因为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执行如下：

两万字详解InnoDB的锁

 

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

两万字详解InnoDB的锁

 

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

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

两万字详解InnoDB的锁

 

索引平时我们写SQL的时候，比如查询select或者delete语句时，尽量加一下limit哈。

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

我门怎么查看SQL加了什么锁呢？或者换个说法，如何查看事务的加锁情况呢？有这两种方法：

5.1 使用infomation_schema数据库中的表获取锁信息

infomation_schema数据库中，有几个表跟锁紧密关联的。

5.1.1 INNODB_TRX

我们在一个会话中执行加锁的语句，在两个会话窗口，即可查看INNODB_TRX的信息啦，如下：

两万字详解InnoDB的锁

 

表中可以看到一个事务id为1644837正在运行汇中，它的隔离级别为REPEATABLE READ。我们一般关注这几个参数：

5.1.2 INNODB_LOCKS

一般系统中，发生某个事务因为获取不到锁而被阻塞时，该表才会有记录。

事务A、B执行如下：

两万字详解InnoDB的锁

 

使用select * from
information_schema.INNODB_LOCKS;查看

两万字详解InnoDB的锁

 

可以看到两个事务Id 1644842和1644843都持有什么锁，就是看那个lock_mode和lock_type哈。但是并看不出是哪个锁在等待那个锁导致的阻塞，这时候就可以看INNODB_LOCK_WAITS表啦。

5.1.3 INNODB_LOCK_WAITS

INNODB_LOCK_WAITS 表明每个事务是因为获取不到哪个事务持有的锁而阻塞。

两万字详解InnoDB的锁

 

两万字详解InnoDB的锁

 

即requesting_trx_id表示事务B的事务Id，blocking_trx_id表示事务A的事务Id。

5.2 show engine innodb status

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：

两万字详解InnoDB的锁

 

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     ;;

这结构锁的关键词记住哈：

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

表示它在等这个锁

表示一个锁结构，这个锁结构的Space ID是267，page number是4，n_bits属性为80，对应的索引是c，这个锁结构中存放的锁类型是X型的插入意向Gap锁。

对应加锁记录的详细信息，8000000a代表的值就是10，

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

6. 手把手死锁案例分析

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

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

6.1 一个死锁的简单例子

表结构和数据如下：

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事务，执行流程如下：

两万字详解InnoDB的锁

 

6.2 分析死锁日志

  1. 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     ;;
  1. 先找到关键词TRANSACTION，可以发现两部分的事务日志，如下：
两万字详解InnoDB的锁

 

  1. 查看正在执行，产生死锁的对应的SQL，如下：
两万字详解InnoDB的锁

 

  1. 查看分开两部分的TRANSACTION，分别持有什么锁，和等待什么锁。
两万字详解InnoDB的锁

 

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


作者：捡田螺的小男孩
链接：
https://juejin.cn/post/7094049650428084232

声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多资讯 >>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号