面试官：MySQL 唯一索引为什么会导致死锁？

╰半橙微兮° 2023-01-21 07:20 46阅读 0赞

![0D5F937FE12312312D81F17F.jpg][]

程序员的成长之路

互联网/程序员/技术/资料共享

[关注][Link 1]

阅读本文大概需要 4 分钟。

来自：[cnblogs.com/hongdada/p/9970176.html][cnblogs.com_hongdada_p_9970176.html]

# 唯一性索引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，如

![4738891e570d00f75edd50eaaec998be.png][]

img

解决办法：

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

# 结论： #

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

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

<END>

**推荐阅读：**

[给你看个宝贝：GitHub 最野的开源库，把你拿捏的死死的。。。][GitHub]

[不重启JVM，替换掉已经加载的类，偷天换日？][JVM]

最近面试BAT，整理一份面试资料《Java面试BATJ通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。

**获取方式：点个「****在看****」，点击上方小卡片，进入公众号后回复「****面试题****」领取，更多内容陆续奉上。**

朕已阅 ![4e07d7e82ed7827ed5181cb73d0908f5.gif][]

[0D5F937FE12312312D81F17F.jpg]: /images/20221020/885dd841a6c14b2da93071b3204f2f17.png
[Link 1]: https://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa24f0bacd5379ac93d74ffebac826d2401fd494f82e900e5102a8bf968f108e4ec72a4ad548&idx=3&lang=zh_CN&mid=2247487442&scene=21&sn=61e758d4bf9f15a3419fd028146469a3&token=1854383306#wechat_redirect
[cnblogs.com_hongdada_p_9970176.html]: https://mp.weixin.qq.com/s?__biz=MzUzMTA2NTU2Ng%3D%3D&chksm=fa496f8ecd3ee698f4954c00efb80fe955ec9198fff3ef4011e331aa37f55a6a17bc8c0335a8&idx=1&lang=zh_CN&mid=2247487551&scene=21&sn=18f64ba49f3f0f9d8be9d1fdef8857d9&token=899450012#wechat_redirect
[4738891e570d00f75edd50eaaec998be.png]: /images/20221020/0656cf73ddb84c629851cb8803acd541.png
[GitHub]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa272366cd50aa707924a738f8f723b27d0e8c31a4613f399216feec4911b8cf40659077ee0d&idx=1&mid=2247506958&scene=21&sn=97c0d6ff0243f82e303558937c067220#wechat_redirect
[JVM]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa272366cd50aa70a02d07b0a675f859f59c2dde5f3bd3fd32c1a58dec81342d753733fbc284&idx=2&mid=2247506958&scene=21&sn=d07ffbc53c03275766914309fa3f723a#wechat_redirect
[4e07d7e82ed7827ed5181cb73d0908f5.gif]: /images/20221020/2c3dbb8d85414e819fd3548ef16bd618.png