MySQL:Innodb 一个死锁案例

一、准备数据和问题

RR隔离级别

CREATE TABLE `ty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
insert into ty(a,b) values(2,3),(5,4),(6,7);

问：

image.png

这种情况会产生死锁，如果将
insert into ty(a,b) values(2,10);
改为：
insert into ty(a,b) values(5,10);

则不会产生死锁为什么？

二、初始化数据画图

本死锁的堵塞主要集中在二级索引中，我们将二级索KEY  idxa  ( a )和主键的数据按照Innodb引擎存储的方式大概排列一下则如图：

image.png

三、T2 步骤1

T2 步骤1：delete from ty where a=5;

-----TRX NO:334719 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334719 lock_mode X(LOCK_X) locks gap and rec(LOCK_ORDINARY[next_key_lock])
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact fORMat; info bits 32
 0: len 4; hex 80000005; asc     ;; 1: len 4; hex 80000009; asc     ;;
-----TRX NO:334719 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 653 page no 3 n bits 72 index PRIMARY of table `test`.`ty` trx id 334719 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000009; asc     ;; 1: len 6; hex 000000051b7f; asc       ;; 2: len 7; hex 760000082b13fd; asc v   +  ;; 3: len 4; hex 80000005; asc     ;; 4: len 4; hex 80000004; asc     ;;
-----TRX NO:334719 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334719 lock_mode X(LOCK_X) locks gap before rec(LOCK_GAP)
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;; 1: len 4; hex 8000000a; asc     ;;

根据这个记录我们可以画图如下，红色部分为锁定的部分箭头为gap lock：

image.png

四、T1 步骤2

T2 步骤1：delete from ty where a=5; 堵塞

-----TRX NO:334724 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334724 lock_mode X(LOCK_X) locks gap and rec(LOCK_ORDINARY[next_key_lock]) waiting(LOCK_WAIT)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;; 1: len 4; hex 80000009; asc     ;;

根据这个记录我们可以画图如下，黄色部分为事务T1准备上锁但是被堵塞的部分，包含黄色部分和红色部分的记录说明它既被T2锁定了并且T1拿不到这条记录的锁，它实际上就是一个next key lock的堵塞：

image.png

五、T2步骤3

这一步如果是：

insert into ty(a,b) values(2,10);
则发生死锁，实际上这一条记录记录在二级索引的值为(2,11)，11是主键的值，则画图如下：

image.png

这种情况下则T2也被堵塞，因为这个区域T1也处于堵塞下，则发生死锁。死锁记录如下：

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334712 lock_mode X(LOCK_X) locks gap before rec(LOCK_GAP) insert intention(LOCK_INSERT_INTENTION) waiting(LOCK_WAIT)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;; 1: len 4; hex 80000009; asc     ;;

及插入印象锁堵塞

这一步如果是：

insert into ty(a,b) values(5,10);
不会发生死锁，，实际上这一条记录记录在二级索引的值为(5,11)，11是主键的值，则画图如下：

image.png

如果是这种情况，不会发生死锁，我们可以看到对于二级索引而言这个区域没有其他事物堵塞，只是T2最开始获取过，本事务再次获取不会有问题。

六、总结

本案例实际上就是看最后触发死锁的插入操作插入的记录到底落在二级索引的哪个区域。

作者微信：