MySQL并发死锁案例分析一

vlambda
2020-08-12

MySQL并发死锁案例分析一

1、前言

2、数据库准备

3、案例分析

一、前言

死锁问题是并发系统中绕不开的一个话题，同样在数据库MySQL存储引擎Innodb中并发情况下死锁问题也屡见不鲜。对于死锁定义、死锁产生的条件、各种锁之间的兼容关系、死锁检测相关内容本篇文章不做深入介绍，理解本文的前提是假设对这些东西已经有了一定的了解，本篇文章只有一个关注点——如何分析死锁。

二、数据库准备

数据库结构：

CREATE TABLE `student` ( `ID` varchar(100) NOT NULL, `NAME` varchar(100) DEFAULT NULL, `AGE` int(11) DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ID为主键，age是一般性索引，name无索引。

表数据：

insert into test.student (ID, NAME, AGE) values(1,'张','11');insert into test.student (ID, NAME, AGE) values(2,'李','22');insert into test.student (ID, NAME, AGE) values(3,'刘','33');

数据库隔离级别：

mysql> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set, 1 warning (0.00 sec)

查看加锁相关信息MySQL语句：

select * from information_schema.innodb_locks;select * from information_schema.innodb_lock_waits;

查看事务相关信息MySQL语句：

select * from information_schema.innodb_trx;

查看innodb状态MySQL语句(最近的死锁日志信息)：

show engine innodb status;

二、案例分析

以下会列举一些例子进行分析在各种场景下死锁产生的原因。各个场景大致是从锁模式(S、X、Rec、Gap、Next-Key)入手进行举例，示例是自己根据锁模式进行回忆所能想到的。

场景①：

MySQL并发死锁案例分析一

作为第一个示例，这里会进行细致的分析，两个事物每执行一条SQL，可以查看下innodb锁状态及锁等待信息以及当前innodb事务列表信息，最后可以通过show engine innodb status;查看最近的死锁日志信息

首先T1执行begin开始事务执行一条SQL删除age为11的数据，这里进行删除的时候会首先申请IS锁，然后申请S锁进行查询是否存在age为11的记录，发现确实存在这条记录，然后会申请Gap锁、IX锁和和X锁，因为age这个字段是非唯一性索引，所以最终会获取到X、Next-key锁(-∞,11],我们来看下执行第一条delete时INNODB_TRX表中已经有了一条记录表示T1的事务信息。

MySQL并发死锁案例分析一

执行T2的delete语句的时候会发现T2被阻塞住了，这是因为T2同样和T1一样同样要申请IS锁，S锁和X、Gap锁，读锁用于查看是否存在age为11的记录，因为T1的锁和T2的X锁是不兼容的，因此T2的S锁就被T1的X锁阻塞了，之所以说T2在等待获取X锁，是因为这个时候T2的读锁和间隙锁都是和T1的锁之间是兼容的，但是到T2申请X锁的时候才被阻塞住。

我们来看下执行第二条delete时INNODB_TRX表中已经有了二条记录表示T1、T2的事务信息。

MySQL并发死锁案例分析一

然后我们执行查看锁信息SQL语句查看锁信息。

select * from information_schema.innodb_locks;select * from information_schema.innodb_lock_waits;

加锁列表：

MySQL并发死锁案例分析一

锁等待列表：

MySQL并发死锁案例分析一

lock_rec为2这是因为，这里是对二级索引进行加的锁，除了在二级索引上加锁外，还要在一级索引主键上进行加锁。lock_mode为X其实这里加的是X 间隙锁，如果是行锁死锁日志中会有信息rec lock but not gap。

然后接下来执行T1的insert语句，innodb检测到死锁，T2被回滚，执行T1的SQL。

MySQL并发死锁案例分析一

commit事务提交，T1的SQL执行成功，数据被insert进去。

MySQL并发死锁案例分析一

接下来查看死锁日志信息：

------------------------LATEST DETECTED DEADLOCK------------------------2020-08-08 15:36:29 0x70001046b000*** (1) TRANSACTION:TRANSACTION 2771286, ACTIVE 11 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)MySQL thread id 3, OS thread handle 123145575657472, query id 868 localhost root updatingdelete from student where age = 11*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 76 page no 4 n bits 72 index age_index of table `test`.`student` trx id 2771286 lock_mode X waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000000b; asc ;; 1: len 1; hex 31; asc 1;;
*** (2) TRANSACTION:TRANSACTION 2771285, ACTIVE 16 sec insertingmysql tables in use 1, locked 15 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2MySQL thread id 2, OS thread handle 123145575378944, query id 869 localhost root updateinsert into test.student (ID, NAME, AGE) values(4,'张','10')*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 76 page no 4 n bits 72 index age_index of table `test`.`student` trx id 2771285 lock_mode XRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000000b; asc ;; 1: len 1; hex 31; asc 1;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 76 page no 4 n bits 72 index age_index of table `test`.`student` trx id 2771285 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000000b; asc ;; 1: len 1; hex 31; asc 1;;
*** WE ROLL BACK TRANSACTION (1)

这里的TRANSACTION1其实是实例中的T2，TRANSACTION2实际上是示例中的T1，重点看T1和T2目前持有什么锁，需要加什么锁，首先看T2：

RECORD LOCKS space id 76 page no 4 n bits 72 index age_index of table `test`.`student` trx id 2771286 lock_mode X waiting

T2正在申请lock_mode X ，处于waiting状态。

然后看T1：

RECORD LOCKS space id 76 page no 4 n bits 72 index age_index of table `test`.`student` trx id 2771285 lock_mode XRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000000b; asc;; 1: len 1; hex 31; asc 1;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 76 page no 4 n bits 72 index age_index of table `test`.`student` trx id 2771285 lock_mode X locks gap before rec insert intention waiting

T1目前已经持有了X间隙互斥锁，正在等待lock_mode X locks gap before rec insert intention间隙插入意向锁，也就是IIGap锁。

T2等待T1的X锁释放，T1等待T2的S锁释放，所以形成了循环依赖，由于T2的undo日志量较小，所以回滚T2，执行T1的SQL。

场景②、

下面将上个例子进行小小的变形，如下：

MySQL并发死锁案例分析一

执行SQL及最终执行结果如下：

MySQL并发死锁案例分析一

并没有出现死锁现象，最终数据库数据如下：

age为11的数据最终被T2删除。

没有发生死锁这就说明T1在最终执行insert语句的时候没有被T2阻塞住，但是T2确实是在执行delete的时候被T1阻塞住了，这个在执行的过程中会看见，这里只是最终结果，具体流程细节可自行尝试，那就说明T2在进行delete语句的时候获取的是间隙锁(-∞,11)，而非Next-key锁，要不然T1在执行insert语句的时候一定会等待T2释放age为11的行锁，当然，如果T1最终插入数据age只要小于11，都会发生死锁。

当然，对于T2或者T1的update道理类似，执行SQL流程如下，死锁原因和之前一样。

以上事例造成死锁的原因大致可以归纳为间隙锁造成的死锁案例，如果明白SQL执行加锁顺序和各种锁之间的兼容性，分析死锁原因应该就很简单了。