当前位置：首页 > article >正文

mysql唯一索引下插入数据死锁过程分析

article 2025/3/9 23:09:32

隔离级别：RR、RC

死锁过程：

表结构

CREATE TABLE `lingluo` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_bc` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

INSERT INTO `ideamake_mc`.`lingluo` (`a`, `b`, `c`, `d`) VALUES (5, 5, 5, 5);
INSERT INTO `ideamake_mc`.`lingluo` (`a`, `b`, `c`, `d`) VALUES (10, 10, 10, 10);

重现步骤

通过mysql8.0，performance_schema库下的data_locks进行加锁分析：

开始之前先贴一个mysql innodb锁类型兼容图

（https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html）
在这里插入图片描述

session1执行完insert into lingluo values(6,6,6,6); 此时加入的是IX意向排他锁（表示事务想要在某些行上加X锁）；
session2执行完insert into lingluo values(7,6,6,6); 此时session1检测到出现唯一键冲突，session1需要再加一个行锁X,REC_NOT_GAP（可以理解为之前的插入隐适锁升级为显示锁）。
此时session2也是做一个当前读判断有无发生唯一索引冲突，所以需要加上S锁，根据上文的锁类型兼容图可知会被session1 的X锁给阻塞住了，进入WAITING状态
session3执行完insert into lingluo values(8,6,6,6); 与session2一样，session3需要加的S锁也是会被阻塞住
session1执行rollback回滚事务，此时session1加的所有X锁、IX锁都会释放。session2、session3都能成功获取到S锁。但紧接着session2、session3都要插入数据，所以需要获取一个插入意向锁X,GAP,INSERT_INTENTION，此时各自的插入意向锁会被对方的S锁给阻塞从而产生死锁，mysql死锁检测触发，会选择其中一个事务进行回滚。
上述截图需要重点关注第一个字段事务ID（由于截图没截全字段名未显示出来），session1的事务ID是：102253、session2的事务ID是：102258、session3的事务ID是：102259。根据事务ID确定每个加锁情况是属于哪个事务的。
贴上死锁检测分析报告辅助理解

2025-03-04 09:42:11 0x70000c4e5000
*** (1) TRANSACTION:
TRANSACTION 102258, ACTIVE 37 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 123145523183616, query id 621 localhost 127.0.0.1 root update
insert into lingluo values(7,6,6,6)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 108 page no 5 n bits 72 index uk_bc of table `test`.`lingluo` trx id 102258 lock mode S locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;
 2: len 4; hex 8000000a; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 108 page no 5 n bits 72 index uk_bc of table `test`.`lingluo` trx id 102258 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;
 2: len 4; hex 8000000a; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 102259, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 123145525313536, query id 647 localhost 127.0.0.1 root update
insert into lingluo values(8,6,6,6)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 108 page no 5 n bits 72 index uk_bc of table `test`.`lingluo` trx id 102259 lock mode S locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;
 2: len 4; hex 8000000a; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 108 page no 5 n bits 72 index uk_bc of table `test`.`lingluo` trx id 102259 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;
 2: len 4; hex 8000000a; asc     ;;

*** WE ROLL BACK TRANSACTION (2)