MySQL唯一键死锁总结分析

目录

• 1、没有锁CASE
• 2、唯一键死锁CASE1
• 3、唯一键死锁CASE2
• 4、唯一键死锁CASE3

---

1、没有锁CASE

# 8.0.18

# RC

CREATE TABLE `t3` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,

  `age` int(11) NOT NULL DEFAULT '1',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB

2、唯一键死锁CASE1

# 8.0.18
# RC
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_name` (`name`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB

死锁产生的原因是：

1. S1对(2,2)记录加X锁，
2. S2/S3需要唯一键冲突检测，需要加S锁，由于X锁的存在，S锁的获取被阻塞。
3. S1提交或者回滚，因为S锁兼容，S2/S3都获得S锁，都希望得到X锁，发生死锁。

为什么S2/S3要加S锁，而不是直接等待X锁

1. S2/S3 在插入之前判断到了唯一键冲突，当前读模式

---

3、唯一键死锁CASE2

# 隔离级别RC
# 版本8.0.18

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB
insert into t1 select 1,'a',1;

事务 1 持有uk_name 唯一索引上的 S 锁（共享锁）
事务 1 想获取 uk_name 唯一索引上的 X 锁 （非 gap 锁的记录锁）
事务 2 持有uk_name 唯一索引上的 S 锁（共享锁）
事务 2 想获得 uk_name 唯一索引上的 X 锁（非 gap 锁的记录锁）

---

4、唯一键死锁CASE3

# 隔离级别RC
# 版本8.0.18

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB;
insert into t1 select 1,'a',1;

show engine innodb status
S2 HOLDS THE LOCK(S),WAITING FOR lock_mode X
S1 HOLDS THE LOCK(S): WAITING FOR lock_mode X