mysql unique 锁_MySQL唯一键死锁总结分析

[TOC]

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

死锁产生的原因是：

S1对(2,2)记录加X锁，

S2/S3需要唯一键冲突检测，需要加S锁，由于X锁的存在，S锁的获取被阻塞。

S1提交或者回滚，因为S锁兼容，S2/S3都获得S锁，都希望得到X锁，发生死锁。

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

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