MySQL死锁分析

71 阅读 0 评论 47 点赞

我是靠谱客的博主无私灰狼，最近开发中收集的这篇文章主要介绍MySQL死锁分析，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

背景知识

MySQL数据库InnoDB引擎的行级锁在使用时是在索引记录上加锁的。

行级锁从占有模式上分为：

排他锁：独占行数据，如某事务获取了该行记录的排他锁，其他事务在获取该记录的排他锁和共享锁时需等待；
共享锁：共享行数据，如某事务获取了该行记录的共享锁，其他事务可获取该记录的共享锁，如想获取独占锁则需等待。

从锁定的数据范围来看，有：

记录锁（Record Locks）：锁在索引记录上的锁，可以是共享锁或排他锁。
间隙锁（Gap Locks）：两条记录之间的间隙，或者第一条记录前的间隙，或者最后一条数据之后的间隙。注意：在RR事务隔离级别时，如果加锁的索引是唯一索引的话，不需要加间隙锁。
Next-Key Locks：记录锁和间隙锁的集合。如果加锁的索引是唯一索引的话，不需要加间隙锁，也就退化成了记录锁。

以上是一些背景知识，以下实践使用的是MySQL 5.7版本。

初始化数据

接下来描述死锁的一种场景，我们创建表并初始化一部分数据：

-- 创建表
CREATE TABLE `deadlock_test` (
`id` int(11) NOT NULL primary key auto_increment,
`a` int(11) NOT NULL ,
INDEX (`a`)
) ENGINE=InnoDB;

-- 初始化数据
insert into deadlock_test (id, a) values (1,2),(3,4),(8,5),(13,11);

此时表中的数据以id、a的顺序展示如下，注意id是主键：

以a、id的列顺序展示如下，注意a加了非唯一索引：

此时，在a索引记录上可以加的行记录锁，以(a,id)的格式列出来会有如下几个，以(a,id)的格式列是因为a索引的组织和存储形式是这样的顺序，a是索引值，id是二级索引指向的主键索引值，这种格式列出来也容易看出间隙锁可能的情况：

(2,1)
(4,3)
(5,8)
(11,13)

间隙锁的间隙分别会有如下几个范围，也以(a,id)的格式列出来：

最小值和(2,1)之间的间隙；
(2,1)和(4,3)之间的间隙；
(4,3)和(5,8)之间的间隙；
(5,8)和(11,13)之间的间隙；

(11,13)和最大值之间的间隙；

触发死锁

接着执行如下两个事务的SQL，在执行到T5时间时发生死锁：

死锁日志和死锁产生过程分析

发生死锁后，执行截取死锁日志

show engine InnoDB status;

死锁日志如下

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-03-06 21:46:35 0x1ce4
*** (1) TRANSACTION:
TRANSACTION 6026442, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 13044, query id 32078 localhost ::1 root update
insert into deadlock_test (a, id) values (5,7)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 66 page no 4 n bits 72 index a of table `my_test_db`.`deadlock_test` trx id 6026442 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000008; asc ;;

*** (2) TRANSACTION:
TRANSACTION 6026441, ACTIVE 34 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 3, OS thread handle 7396, query id 32089 localhost ::1 root update
insert into deadlock_test (a, id) values (5,7)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 66 page no 4 n bits 72 index a of table `my_test_db`.`deadlock_test` trx id 6026441 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000008; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 66 page no 3 n bits 72 index PRIMARY of table `my_test_db`.`deadlock_test` trx id 6026441 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 0000005bf4ca; asc [ ;;
2: len 7; hex bb000001310110; asc 1 ;;
3: len 4; hex 80000005; asc ;;

*** WE ROLL BACK TRANSACTION (1)

我们结合加锁的过程和死锁日志，分析死锁的过程，下面列出的间隙锁和索引记录锁都是以(a,id)的列顺序格式表示：

事务一在执行T2时间的语句之后，持有了表级的IX锁，行级的Next-Key Locks和间隙锁。Next-Key Lock是行锁和间隙锁的组合，以(a,id)的格式展示，这里包括(5,8)的行记录锁以及(4,3)、(5,8)之间的间隙锁；间隙锁是(5,8)、(11,13)之间的间隙锁。

事务二在执行T4时间的语句时，需要获取表级的IX锁，行级的间隙锁。在获取IX锁时不会有问题，因为两个事务间的IX是可以兼容的；接着获取行记录(5,8)的间隙锁，这里包括(4,3)、(5,8)之间的间隙锁，因为这些锁已经被事务一持有，因此需要等待。另外，事务二在执行插入时，还要获取主键7的行记录共享锁和排它锁。

接下来事务一执行T5时间的语句，在执行该语句时，需要获取主键7的行记录共享锁和排他锁。

综上所属，截至到T5时间，事务一持有(4,3)、(5,8)之间的间隙锁和(5,8)、(11,13)之间的间隙锁，以及(5,8)的记录锁，等待主键7的行记录共享锁和排他锁；事务二等待主键7的共享锁和排他锁，等待(4,3)、(5,8)之间的间隙锁；相互等待，发生死锁。

接着，MySQL服务器检测出死锁，回滚了事务二，在死锁日志中对应的是TRANSACTION 6026442。