Mysql 死锁案例4-delete 相邻记录导致死锁

码上得天下

已于 2024-03-13 19:52:41 修改

阅读量744

点赞数 3

分类专栏： Mysql 文章标签： mysql 数据库

于 2024-03-13 17:10:36 首次发布

本文链接：https://blog.csdn.net/meser88/article/details/136684485

版权

Mysql 专栏收录该内容

107 篇文章 14 订阅

订阅专栏

本文详细描述了一个MySQL死锁案例，涉及间隙锁与意向插入锁在事务中的冲突，以及如何通过死锁日志分析事务间的锁竞争。作者探讨了事务隔离级别对死锁的影响，并提出了如何复现特定情况下的死锁。

摘要由CSDN通过智能技术生成

死锁复现

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `t` */
 
insert  into `t`(`id`,`c`,`d`) values (0,0,0),(5,5,5),(10,10,10),(15,15,15)

	事务1	事务2
T1	BEGIN; DELETE FROM t WHERE id=4;
T2		BEGIN; DELETE FROM t WHERE id=4;
T3	INSERT INTO t VALUES(3, 3,3);(阻塞)
T4		INSERT INTO t VALUES(3, 3,3);（死锁）

死锁分析
注意where条件的c是普通索引

T1事务1加主键索引间隙锁（0,5）写锁成功，
T2事务2加主键索引间隙锁（0,5）写锁成功
T3事务1申请意向插入锁（0,5）与事务2间隙锁（0,5）冲突阻塞
T3事务2申请意向插入锁（0,5）与事务1间隙锁（0,5）冲突阻塞，循环等待死锁

这里其实也是间隙锁和意向插入锁冲突死锁，delete与update加锁逻辑差不多。参考：

Mysql 死锁案例2-间隙锁与意向插入锁冲突

查看锁信息 SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-13 16:47:14 0x1ed8
*** (1) TRANSACTION:
TRANSACTION 488371, ACTIVE 63 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 6, OS thread handle 2368, query id 2676 localhost ::1 root update
INSERT INTO t VALUES(3, 3,3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 488371 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000076d2c; asc     m,;;
 2: len 7; hex 69000001ba0495; asc i      ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000000; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 488372, ACTIVE 51 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 20, OS thread handle 7896, query id 2681 localhost ::1 root update
INSERT INTO t VALUES(3, 3,3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 488372 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000076d2c; asc     m,;;
 2: len 7; hex 69000001ba0495; asc i      ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000000; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 488372 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000076d2c; asc     m,;;
 2: len 7; hex 69000001ba0495; asc i      ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000000; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS

where语句等值匹配的delete语句加锁逻辑分主键索引和唯一索引和普通索引，规则参考：

MySQL行锁加锁规则之等值查询

普通索引删除的加锁案例：

DELETE FROM  t WHERE  c=5;

---TRANSACTION 488378, ACTIVE 6 sec
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 2368, query id 2744 localhost ::1 root
TABLE LOCK table `test`.`t` trx id 488378 lock mode IX
RECORD LOCKS space id 638 page no 4 n bits 80 index c of table `test`.`t` trx id 488378 lock_mode X
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 80000005; asc     ;;

RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 488378 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000773ba; asc     s ;;
 2: len 7; hex 25000001392ce4; asc %   9, ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000000; asc     ;;

RECORD LOCKS space id 638 page no 4 n bits 80 index c of table `test`.`t` trx id 488378 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

加了3把锁，索引c间隙锁(5,10)，主键索引记录锁id=5，索引c临键锁（0,5]

有兴趣的可以看下这篇文章：MySQL delete 相邻记录导致死锁

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-09 22:34:13 7f78eab82700
*** (1) TRANSACTION:
TRANSACTION 462308399, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
delete from ty where a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
insert into ty(a,b) values(2,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

分析死锁日志

首先要理解的是对同一个字段申请加锁是需要排队. 其次表ty中索引idxa为非唯一普通索引，我们根据事务执行的时间顺序来解释，这样比较好理解（MySQL 5.6 事务隔离级别为RR）。

a. 根据死锁日志显示事务2 也即sess1执行的事务，根据 HOLDS THE LOCK(S)显示 sess1 先执行 delete from ty where a=5 ，该事务持有索引a=5 的行锁lock_mode X ,因为是RR隔离级别,所以sess1 还持有两个gap锁[1,2]-[2,5], [2,5]-[3,6] 。
b. 事务1的日志也即sess2执行的事务,申请对 a=5 加锁，一个rec lock 和两个gap锁,因为sess1中delete还没释放，故sess2的事务1等待sess1的事务2释放a=5的锁资源。
c. 然后根据WAITING FOR THIS LOCK TO BE GRANTED，提示事务2 insert语句正在等待 lock_mode X locks gap before rec insert intention waiting，因为insert语句 [4,2] 介于gap锁[1,2]-[2,5]之间，所以有了提示 "lock_mode X locks gap",insert语句必须等待前面 sess2中delete 获取锁并且释放锁。于是,sess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),循环等待，造成死锁。 问题如果sess1 执行 insert into ty(a,b) values(5,10); sess2会遇到死锁吗？

因为作者没贴具体的表结构和表数据，所以没看懂这个死锁，也没复现出来（Mysql 5.7.12 事务隔离级别为RR）。

按对锁的理解，事务1事务2执行的第一个delete语句都能成功，只有一种可能，就是数据库没a=5这条记录，间隙锁与间隙锁不冲突。按这个设定执行，那么事务2的第二个delete语句就不会阻塞，所以也就不存在死锁。怎么复现这个死锁？？？？