面試官：MySQL 唯一索引為什么會(huì)導(dǎo)致死鎖？

來源：https://www.cnblogs.com/hongdada/p/9970176.html

唯一性索引unique影響

唯一性索引表創(chuàng)建

    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 '創(chuàng)建時(shí)間',
      `create_user_id` bigint(11) DEFAULT NULL COMMENT '創(chuàng)建人id',
      `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改時(shí)間',
      `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='學(xué)生信息表';

在其中創(chuàng)建了唯一索引name,就是說這個(gè)學(xué)生表同名的學(xué)生只能由一位。

命令添加unique:

    alter table sc add unique (name);

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

刪除：

    alter table sc drop index `name`;

唯一性索引作用

先行插入部分?jǐn)?shù)據(jù)：

    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 ('關(guān)羽','一年二班',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 '創(chuàng)建時(shí)間',
      `create_user_id` bigint(11) DEFAULT NULL COMMENT '創(chuàng)建人id',
      `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改時(shí)間',
      `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='學(xué)生信息表';

這時(shí)的Auto_Increment=5 再次執(zhí)行sql:

    insert into sc (name,class,score) values ('呂布','二年二班',77)
    > 1062 - Duplicate entry '呂布' for key 'name'
    > 時(shí)間: 0.01s

此時(shí)再次查看表定義，會(huì)發(fā)現(xiàn)Auto_Increment=6

unique除了在插入重復(fù)數(shù)據(jù)的時(shí)候會(huì)報(bào)錯(cuò)，還會(huì)使auto_increment自動(dòng)增長(zhǎng)

unique與primary key的區(qū)別

簡(jiǎn)單的講，primary key=unique+not null

具體的區(qū)別：

(1) 唯一性約束所在的列允許空值，但是主鍵約束所在的列不允許空值。

(2) 可以把唯一性約束放在一個(gè)或者多個(gè)列上，這些列或列的組合必須有唯一的。但是，唯一性約束所在的列并不是表的主鍵列。

(3) 唯一性約束強(qiáng)制在指定的列上創(chuàng)建一個(gè)唯一性索引。在默認(rèn)情況下，創(chuàng)建唯一性的非聚簇索引，但是，也可以指定所創(chuàng)建的索引是聚簇索引。

(4) 建立主鍵的目的是讓外鍵來引用.

(5) 一個(gè)表最多只有一個(gè)主鍵，但可以有很多唯一鍵

存在唯一鍵沖突時(shí)，避免策略

insert ignore

insert ignore會(huì)忽略數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù)(根據(jù)主鍵或者唯一索引判斷)，如果數(shù)據(jù)庫(kù)沒有數(shù)據(jù)，就插入新的數(shù)據(jù)，如果有數(shù)據(jù)的話就跳過這條數(shù)據(jù).

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

執(zhí)行上面的語句，會(huì)發(fā)現(xiàn)并沒有報(bào)錯(cuò)，但是主鍵還是自動(dòng)增長(zhǎng)了。

replace into

• replace into 首先嘗試插入數(shù)據(jù)到表中。如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)(根據(jù)主鍵或者唯一索引判斷)則先刪除此行數(shù)據(jù)，然后插入新的數(shù)據(jù)，否則，直接插入新數(shù)據(jù)。

• 使用replace into，你必須具有delete和insert權(quán)限

    replace into sc (name,class,score) values ('呂布','二年二班',77);

此時(shí)會(huì)發(fā)現(xiàn)呂布的班級(jí)跟年齡都改變了，但是id也變成最新的了，所以不是更新，是刪除再新增

insert on duplicate key update

• 如果在insert into 語句末尾指定了on duplicate key update，并且插入行后會(huì)導(dǎo)致在一個(gè)UNIQUE索引或PRIMARY KEY中出現(xiàn)重復(fù)值，則在出現(xiàn)重復(fù)值的行執(zhí)行UPDATE；如果不會(huì)導(dǎo)致重復(fù)的問題，則插入新行，跟普通的insert into一樣。

• 使用insert into，你必須具有insert和update權(quán)限

• 如果有新記錄被插入，則受影響行的值顯示1；如果原有的記錄被更新，則受影響行的值顯示2；如果記錄被更新前后值是一樣的，則受影響行數(shù)的值顯示0

    insert into sc (name,class,score) values ('關(guān)羽','二年二班',80) on duplicate key update score=100;
    > Affected rows: 2
    > 時(shí)間: 0.008s

舊數(shù)據(jù)中關(guān)羽是一年二班，70分，現(xiàn)在插入，最后發(fā)現(xiàn)只有分?jǐn)?shù)變成了100，班級(jí)并沒有改變。

    4 關(guān)羽  一年二班  100 2018-11-16 15:32:18   2018-11-16 15:51:51 

id沒有發(fā)生變化，數(shù)據(jù)只更新，但是auto_increment還是增長(zhǎng)1了。

死鎖

insert … on duplicate key 在執(zhí)行時(shí)，innodb引擎會(huì)先判斷插入的行是否產(chǎn)生重復(fù)key錯(cuò)誤，如果存在，在對(duì)該現(xiàn)有的行加上S(共享鎖)鎖，如果返回該行數(shù)據(jù)給mysql,然后mysql執(zhí)行完duplicate后的update操作， 然后對(duì)該記錄加上X(排他鎖)，最后進(jìn)行update寫入。

如果有兩個(gè)事務(wù)并發(fā)的執(zhí)行同樣的語句，那么就會(huì)產(chǎn)生death lock，如

解決辦法：

• 1、盡量對(duì)存在多個(gè)唯一鍵的table使用該語句

• 2、在有可能有并發(fā)事務(wù)執(zhí)行的insert 的內(nèi)容一樣情況下不使用該語句

結(jié)論：

• 這三種方法都能避免主鍵或者唯一索引重復(fù)導(dǎo)致的插入失敗問題。

• insert ignore能忽略重復(fù)數(shù)據(jù)，只插入不重復(fù)的數(shù)據(jù)。

• replace into和insert … on duplicate key update，都是替換原有的重復(fù)數(shù)據(jù)，區(qū)別在于replace

into是刪除原有的行后，在插入新行，如有自增id，這個(gè)會(huì)造成自增id的改變；insert … on duplicate key
update在遇到重復(fù)行時(shí)，會(huì)直接更新原有的行，具體更新哪些字段怎么更新，取決于update后的語句。

參考

• Mysql中unique與primary約束的區(qū)別分析（轉(zhuǎn)）

• MySQL避免插入重復(fù)記錄：唯一性約束

• MySQL優(yōu)化–INSERT ON DUPLICATE UPDATE死鎖