MySQL进阶篇SQL优化（InnoDB锁问题排查与解决）

1.概述

前面章节之所以介绍那么多锁的知识点和示例，其实最终目的就是为了排查与解决死锁的问题，下面我们把之前学过锁知识重温与补充一遍，然后再通过例子演示下如果排查与解决死锁。

2.前期准备

●数据库事务隔离级别

SHOW VARIABLES LIKE 'transaction_isolation%';

 

 MYSQL事务隔离级别默认可重复读（如果还不了解事务隔离级别的鞋童们，可以移步到我写这篇文章去了解下）。
●将事务自动提交关闭

SET AUTOCOMMIT=0;

事务自动提交配置：0.事务非自动提交，1.事务自动提交
●创建一个模拟演示用的会员表

CREATE TABLE goods.members (`ID` int NOT NULL AUTO_INCREMENT COMMENT '会员自增ID',`MemberName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '会员名称',`Tel` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号码',PRIMARY KEY (`ID`));

 在MemberName会员名称字段上建立一个非聚集索引

ALTER TABLE goods.members ADD INDEX IX_MemberName(MemberName);
SHOW INDEX FROM goods.members;

 

往会员表插入四条数据，方便间隙锁跟记录锁例子演示

INSERT INTO goods.members (MemberName,Tel) VALUES ('A','110'),('B','120'),('C','130'),('D','140');

 

SELECT * FROM goods.members;

 

 

好了，前期条件已经准备完毕，在演示之前，下面让我们来重温与补充下锁知识。

3.锁知识重温与补充

3.1锁的介绍

 

 

下面就根据上述图再次重温与补充下之前学习过锁的知识点。

3.2乐观锁与悲观锁

悲观锁与乐观锁是两种常见的资源并发锁设计思路，也是并发编程中一个非常基础的概念。
●悲观锁（Pessimistic Lock）
悲观锁的特点是先获取锁，再进行业务操作，即“悲观”的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。通常所说的“一锁二查三更新”即指的是使用悲观锁。通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select...for update操作来实现悲观锁。当数据库执行select for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
●乐观锁（Optimistic Lock）
乐观锁的特点先进行业务操作，不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号，或者时间戳。例如UPDATE SET data = new_data, version = new_version WHERE version = old_version;

3.3共享锁与排他锁

InnoDB存储引擎有主要两种类型的行锁：
●共享锁（S锁）：允许持锁事务读取数据行。
●排他锁（X锁）：允许持锁事务更新或者删除数据行。
假设事务T1持有R记录行S锁，事务T2请求获取R记录行时，会做如下处理：
◎T2请求S锁会被允许，结果T1，T2都会持有R记录S锁。
◎T2请求X锁不会允许，需要等待T1释放S锁。
同理，假设事务T1持有R记录行X锁，事务T2请求持有R记录行S、X锁时，会做如下处理：
◎T2必须等待T1释放X锁才可以操作R记录行，因为S锁与X锁不兼容。

3.4意向锁

●意向共享锁（IS锁）：允许事务获取表数据行的共享锁。
●意向排他锁（IX锁）：允许事务获取表数据行的排他锁。
假设事务T1在某表上加了S锁，事务T2想要更改该表R记录行时，要先添加IX锁：
◎由于S锁与IX锁不兼容，所以需要等待T1释放S锁才能更改该表R记录行。
同理，假设事务T1在某表上加了IS锁，事务T2想要更改该表R记录行时，添加了IX锁：
◎由于IS锁与IX锁兼容，所以事务T2可以更改该表R记录行，这样也实现了锁多粒度。
InnoDB存储引擎锁兼容性如下：

 

3.5记录锁（Record Locks）

●它是建立在索引记录上的行锁，会锁住一行记录：SELECT * FROM goods.members WHERE ID=1 FOR UPDATE;
●当一条SQL没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。
●即使查询的表上没有任何索引，InnoDB也会在后台创建一个隐藏的聚集主键索引并实施记录锁。
●会阻塞其他事务的插入、更新和删除。

RECORD LOCKS space id 51 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 270900 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

3.6间隙锁（Gap Locks）

●仅仅锁住一个索引区间（开区间）。其实就是索引项范围内的间隙上锁（在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身），避免幻读。还有间隙锁只会阻止其他事务插入到间隙当中，他们并不阻止其他事务在同一个间隙上获得间隙锁，所以gap x lock和gap s lock有相同的作用。如members表中ID主键间隙范围：(-∞,1),(1,2),(2,3),(3,4), (4,+∞)。示例如下：
事务T1：

SELECT * FROM goods.members WHERE ID>1 AND ID<4 FOR UPDATE;

 

事务T2：

UPDATE goods.members SET Tel='110' WHERE ID IN (1,4);

 

UPDATE goods.members SET Tel='110' WHERE ID IN (2,3);

 

很明显T1在主键ID (2,3)区间加了间隙锁，当T1未释放锁情况下，T2想要更新ID>1 AND ID<4区间范围值时，就会发生阻塞。

3.7临键锁（Next-Key Locks）

●临键锁（Next-Key Locks）其实也是一种特殊间隙锁，是记录锁（Record Locks）和间隙锁（Gap Locks）的组合。Next-Key锁是在下一个索引记录本身和索引之前的间隙加上S锁或是X锁（如果是读就加上S锁，如果是写就加X锁）。

3.8插入意向锁（Insert Intention Locks）

Gap Lock中存在一种插入意向锁（Insert Intention Lock），在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

3.9行锁的兼容矩阵

 

4.死锁

所谓死锁，其实是指多个进程在运行过程中因争夺资源而造成的一种僵持局面，当进程处于这种僵持状态时，若无外力作用，它们都将无法再向前推进。如下图所示：

因此我们举个例子来描述，如果此时有一个事务A，先持有锁A，再去获得锁B的情况下，同时又有一个事务B，先持有锁B再去获得锁A的时候就会发生死锁。

 

4.1死锁产生的4个必要条件

●互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。
●请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
●不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
●环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，•••，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

4.2死锁示例

演示还是使用goods.members会员表，MemberName会员名称字段为非聚集索引列，清空之前示例数据：

TRUNCATE TABLE goods.members;

 预先插入两条会员数据：

INSERT INTO goods.members (MemberName,Tel) VALUES ('A','110'),('C','130');

 

 事务T1：

UPDATE goods.members SET Tel='130' WHERE MemberName='C';

 

●记录锁：因为MemberName字段是索引，所以该Update语句肯定会加上MemberName='C'的记录锁。
●间隙锁：Update语句会在非唯一索引的MemberName='C'加上左区间的间隙锁(A,C)和右区间的间隙锁(C, +∞)（因为目前goods.members会员表中只有MemberName='C'的一条记录，所以没有中间的间隙锁）。
●Next-Key锁：记录锁(Record Locks)+间隙锁(Gap Locks)，说明Update语句同时持有(A,C]Next-Key锁。
事务T2：

UPDATE goods.members SET Tel='110' WHERE MemberName='A';

 

记录锁：因为MemberName字段是索引，所以该Update语句肯定会加上MemberName='A'的记录锁。
●间隙锁：Update语句会在非唯一索引的MemberName='A'加上左区间的间隙锁(-∞,A)（因为目前goods.members会员表中只有MemberName='A'的一条记录，所以没有中间的间隙锁）和右区间的间隙锁(A,C)。
●Next-Key锁：记录锁(Record Locks)+间隙锁(Gap Locks)，说明Update语句同时持有(-∞,A]Next-Key锁。
事务T1： 

INSERT INTO goods.members (MemberName,Tel) VALUE ('B','120');

 

 首先是阻塞等待，等T2执行完毕才显示结果！
●间隙锁：因为插入是MemberName=’B’会员信息（B在A和C之间），所以需要请求加(A,C)的间隙锁。
●插入意向锁（Insert Intention）：插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即事务T1需要插入意向锁(A,C)。
事务T2：

INSERT INTO goods.members (MemberName,Tel) VALUE ('D','140');

 

 间隙锁：因为插入是MemberName=’D’会员信息（D在C之后），所以需要请求加(C,+∞)的间隙锁。
●插入意向锁（Insert Intention）：插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即事务T2需要插入意向锁(C,+∞)。
事务T1：

等T2执行完毕后，事务T1插入MemberName=’B’的语句就会由阻塞变为死锁！

4.3死锁分析

上面死锁示例我再画了一个表格方便大家更加清晰了解死锁发生过程：

顺序编号	事务T1	事务T2
①	BEGIN;
②	UPDATE goods.members SET Tel='130' WHERE MemberName='C'; 持有锁：(A,C]Next-Key锁和(C, +∞)间隙锁。
③		BEGIN;
④		UPDATE goods.members SET Tel='110' WHERE MemberName='A'; 持有锁：(-∞,A]Next-Key锁和(A,C)间隙锁。
⑤	INSERT INTO goods.members (MemberName,Tel) VALUE ('B','120'); 持有锁：(C, +∞)间隙锁。 等待锁：(A,C)插入意向锁。
⑥		INSERT INTO goods.members (MemberName,Tel) VALUE ('D','140'); 持有锁：(A, C)间隙锁。 等待锁：(C, +∞)插入意向锁。
⑦	Deadlock found when trying to get lock; try restarting transaction

然后我们再通过以下语句来查看死锁日志具体分析一下：

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

日志如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-08-04 11:39:12 0x7fee8b558700
*** (1) TRANSACTION:
TRANSACTION 271069, ACTIVE 590 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 1123904, OS thread handle 140662933055232, query id 4785256 localhost root update
INSERT INTO goods.members (MemberName,Tel) VALUE ('B','120')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 57 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 271069 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 43; asc C;;
 1: len 4; hex 80000002; asc     ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 271069 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 1; hex 43; asc C;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 271070, ACTIVE 432 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 1123909, OS thread handle 140662461384448, query id 4785257 localhost root update
INSERT INTO goods.members (MemberName,Tel) VALUE ('D','140')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 57 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 271070 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 43; asc C;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 5 n bits 72 index IX_MemberName of table `goods`.`members` trx id 271070 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

 

4.3.1事务T1日志

●找到最新死锁日志记录，并找到事务T1(271069)：

 

●查看事务T1日志执行SQL语句：

 

INSERT INTO goods.members (MemberName,Tel) VALUE ('B','120');

●查看事务T1日志里持有锁(HOLDS THE LOCK)：索引(index IX_MemberName)，物理记录(PHYSICAL RECORD)，间隙区间（未知，+∞）、（未知，C）。

 

●查看事务T1日志正在等待锁释放(WAITING FOR THIS LOCK TO BE GRANTED)：插入意向锁(lock_mode X locks gap before rec insert intention waiting)，索引上(index IX_MemberName)，物理记录(PHYSICAL RECORD)，间隙区间（未知，C）。

 

4.3.2事务T2日志

●然后找到事务T2(271070)：

●查看事务T2日志执行SQL语句：

INSERT INTO goods.members (MemberName,Tel) VALUE ('D','140');

 

查看事务T2日志里持有锁(HOLDS THE LOCK)：索引(index IX_MemberName)，间隙锁(lock_mode X locks gap before rec)，物理记录(PHYSICAL RECORD)，间隙区间（未知，C）。

 

●查看事务T2日志正在等待锁释放(WAITING FOR THIS LOCK TO BE GRANTED)：插入意向锁(lock_mode X locks gap before rec insert intention waiting)，索引上(index IX_MemberName)，物理记录(PHYSICAL RECORD)，间隙区间（未知，+∞）。

 

4.3.3查看日志总结

●事务T1正在等待的插入意向排他锁，刚好正在事务T2的怀里。
●事务T2持有间隙锁，正在等待插入意向排它锁。

4.4总结

●事务T1执行完Update MemberName='C'语句，持有(A,C]Next-Key锁和(C, +∞)间隙锁。
●事务T2执行完Update MemberName='A'语句，持有(-∞,A]Next-Key锁和(A,C)间隙锁。
●事务T1执行Insert MemberName='B'的语句时，因为需要(A,C)插入意向锁，但是(A,C)在事务T2里面未释放，所以T1继续等待。
●事务T2执行Insert MemberName='D'的语句时，因为需要(C, +∞) 插入意向锁，但是(C, +∞) 在事务T1里面未释放，所以T2继续等待。
●事务T1持有(C, +∞)间隙锁，在等待(A,C)的插入意向锁，事务T2持有(A,C)间隙锁，在等待(C, +∞)的插入意向锁，所以形成了死锁的闭环（间隙锁与插入意向锁会冲突的，可以看回行锁的兼容矩阵）。
●事务T1，T2形成了死锁闭环后，因为InnoDB的底层机制，它会让其中一个事务让出资源，让另外的事务执行成功，这就是为什么你最后看到了事务T2插入成功，而事务T1的插入最后由阻塞显示为Deadlock found when trying to get lock; try restarting transaction。
注：查询锁信息（MySQL8.0版本）：SELECT * FROM `performance_schema`.data_locks;