面试官:MySQL 唯一索引为什么会导致死锁?
唯一性索引unique影响
唯一性索引表创建
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`score` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
`modify_user_id` bigint(11) DEFAULT 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(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`score` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
`modify_user_id` bigint(11) DEFAULT 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 ('关羽','二年二班',80) on 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,如
img
解决办法:
- 1、尽量对存在多个唯一键的table使用该语句
- 2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句
结论:
- 这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
- insert ignore能忽略重复数据,只插入不重复的数据。
- replace into和insert … on duplicate key update,都是替换原有的重复数据,区别在于replace
into是删除原有的行后,在插入新行,如有自增id,这个会造成自增id的改变;insert … on duplicate key update在遇到重复行时,会直接更新原有的行,具体更新哪些字段怎么更新,取决于update后的语句。
参考
- Mysql中unique与primary约束的区别分析(转)
- MySQL避免插入重复记录:唯一性约束
- MySQL优化–INSERT ON DUPLICATE UPDATE死锁
我是蜗牛,大厂程序员,专注技术原创和个人成长,正在互联网上摸爬滚打。欢迎关注我,和蜗牛一起成长,我们一起牛~下期见!
相关文章
- MySQL 索引:类型、优势和应用。(mysql有哪些索引)
- 深入理解MySQL主键和索引技术(mysql主键索引)
- Mysql:一步一步指导MySQL安装版:一步一步指引安装(mysql安装版安装)
- MySQL索引与外键:提升数据库性能的必备技巧(mysql索引外键)
- MySQL中实现不重复数据的方法(mysql不重复数据)
- MySQL多人同时访问:管理安全之道(mysql多人访问)
- MySQL查询:以逗号分隔的方式实现(mysql逗号分隔查询)
- MySQL如何利用前缀索引提升效率(mysql创建前缀索引)
- 高效实用!MySQL删除范围数据技巧一览(mysql删除范围数据)
- 深入了解MySQL的非聚集索引(mysql非聚集索引)
- MySQL: 如何更有效利用字段加索引(mysql字段加索引)
- MySQL:了解不同类型的索引(mysql什么索引)
- MySQL中的反向索引技术(反向索引mysql)
- MySQL优化之前缀索引的秘诀(mysql 前缀索引)
- MySQL如何通过建立索引提升查询效率(mysql 建 索引)
- 优点MySQL索引:优势分析(mysql 索引有哪些)
- MySQL字符串索引:高效管理数据库(mysql字符串索引)
- MySQL主表概念解析(mysql中主表是什么)
- MySQL繁体字支持在C語言中(c 中mysql繁体字)
- MySQL在cnblog上的异常作用(cnblog mysql)
- C MySQL函数使用中的问题及解决方案(c mysql函数的问题)
- AIX上搭建MySQL的新版本篇(aix 版本 mysql)
- MySQL三大存储引擎选择适合你的数据库引擎(mysql三大存储引擎)
- MySQL拆行教程轻松学会一行拆成多行(mysql 一行拆开)
- 深入了解MySQL索引行锁在不同索引中的应用(mysql不同索引行锁)
- 无需中断服务教你如何在MySQL中实现不停机创建索引(mysql不停机创建索引)
- MySQL索引失效问题的解决方法(mysql 不能使用索引)