查看原文
其他

为什么 MySQL 唯一索引会导致死锁,“有心杀贼,无力回天”?

Java精选 2022-08-09
>>号外:关注“Java精选”公众号,菜单栏->聚合->干货分享,回复关键词领取视频资料、开源项目。

唯一性索引unique影响

唯一性索引表创建

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
        `id` int(11NOT NULL AUTO_INCREMENT,
        `name` varchar(200CHARACTER SET utf8 DEFAULT NULL,
        `class` varchar(200CHARACTER SET utf8 DEFAULT NULL,
        `score` int(11DEFAULT NULL,
        `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        `create_user_id` bigint(11DEFAULT NULL COMMENT '创建人id',
        `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
        `modify_user_id` bigint(11DEFAULT NULL COMMENT '最后修改人id',
        PRIMARY KEY (`id`),
        UNIQUE KEY `name` (`name`)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

在其中创建了唯一索引name,就是说这个学生表同名的学生只能由一位。

命令添加unique:

alter table sc add unique (name);
alter table sc add unique key `name_score` (`name`,`score`);

删除:

alter table sc drop index `name`;

唯一性索引作用

先行插入部分数据:

insert into sc (name,class,score) values ('吕布','一年二班',67);
insert into sc (name,class,score) values ('赵云','一年二班',90);
insert into sc (name,class,score) values ('典韦','一年二班',89);
insert into sc (name,class,score) values ('关羽','一年二班',70);

再次查看表定义:

show create table sc;
CREATE TABLE `sc` (
                      `id` int(11NOT NULL AUTO_INCREMENT,
                      `name` varchar(200CHARACTER SET utf8 DEFAULT NULL,
                      `class` varchar(200CHARACTER SET utf8 DEFAULT NULL,
                      `score` int(11DEFAULT NULL,
                      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `create_user_id` bigint(11DEFAULT NULL COMMENT '创建人id',
                      `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      `modify_user_id` bigint(11DEFAULT NULL COMMENT '最后修改人id',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `name` (`name`)
ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

这时的Auto_Increment=5 再次执行sql:

insert into sc (name,class,score) values ('吕布','二年二班',77)
1062 - Duplicate entry '吕布' for key 'name'
> 时间: 0.01s

此时再次查看表定义,会发现Auto_Increment=6

unique除了在插入重复数据的时候会报错,还会使auto_increment自动增长

unique与primary key的区别

简单的讲,primary key=unique+not null

具体的区别:

(1) 唯一性约束所在的列允许空值,但是主键约束所在的列不允许空值。

(2) 可以把唯一性约束放在一个或者多个列上,这些列或列的组合必须有唯一的。但是,唯一性约束所在的列并不是表的主键列。

(3) 唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下,创建唯一性的非聚簇索引,但是,也可以指定所创建的索引是聚簇索引。

(4) 建立主键的目的是让外键来引用.

(5) 一个表最多只有一个主键,但可以有很多唯一键

存在唯一键冲突时,避免策略

insert ignore

insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断),如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据.

insert ignore into sc (name,class,score) values ('吕布','二年二班',77)

执行上面的语句,会发现并没有报错,但是主键还是自动增长了。

replace into

  • replace into 首先尝试插入数据到表中。如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据,否则,直接插入新数据。
  • 使用replace into,你必须具有delete和insert权限
replace into sc (name,class,score) values ('吕布','二年二班',77);

此时会发现吕布的班级跟年龄都改变了,但是id也变成最新的了,所以不是更新,是删除再新增

insert on duplicate key update

  • 如果在insert into 语句末尾指定了on duplicate key update,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致重复的问题,则插入新行,跟普通的insert into一样。
  • 使用insert into,你必须具有insert和update权限
  • 如果有新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2;如果记录被更新前后值是一样的,则受影响行数的值显示0
insert into sc (name,class,score) values ('关羽','二年二班',80on duplicate key update score=100;
> Affected rows: 2
> 时间: 0.008s

旧数据中关羽是一年二班,70分,现在插入,最后发现只有分数变成了100,班级并没有改变。

4 关羽  一年二班  100 2018-11-16 15:32:18   2018-11-16 15:51:51

id没有发生变化,数据只更新,但是auto_increment还是增长1了。

死锁

insert … on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误,如果存在,在对该现有的行加上S(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作, 然后对该记录加上X(排他锁),最后进行update写入。

如果有两个事务并发的执行同样的语句,那么就会产生death lock,如

解决办法:

  • 1、尽量对存在多个唯一键的table使用该语句
  • 2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

结论:

  • 这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
  • insert ignore能忽略重复数据,只插入不重复的数据。
  • replace into和insert … on duplicate key update,都是替换原有的重复数据,区别在于replace

into是删除原有的行后,在插入新行,如有自增id,这个会造成自增id的改变;insert … on duplicate key update在遇到重复行时,会直接更新原有的行,具体更新哪些字段怎么更新,取决于update后的语句。

作者:hongdada

cnblogs.com/hongdada/p/9970176.html

往期精选  点击标题可跳转

为什么 Redis 使用单线程,而新版本开始引入多线程,你什么看法?

程序员的忠告:为什么避免使用 SELECT * 查询,效率低?

Spring Boot 框架整合 OAuth2 实现单点登录 SSO 详细完整源码教程!(附源码)

使用 Java 8 开发时的 4 大最佳技巧,你都知道吗?

Java 中如何解决 POI 读写 excel 几万行数据时内存溢出的问题?(附源码)

Java 泛型中的 T、E、K、V、?通配符,你确定都了解吗?

面试官:说说项目中 Java 多线程一般都用于哪些应用场景?

面试中不可忽视的 15 个 Java 冷知识,你知道多少?

面试官问:Redis 数据过多导致内存占满,会宕机吗?如何内存回收?

MySQL 中误删表数据,如何快速恢复丢失的数据?

如何重构千行“又臭又长”的类,IntelliJ IDEA 几分钟搞定,真牛逼!

点个赞,就知道你“在看”!

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存