MySQL主鍵自增在什么情況下會(huì)出現(xiàn)空洞

本篇內(nèi)容主要講解“MySQL主鍵自增在什么情況下會(huì)出現(xiàn)空洞”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL主鍵自增在什么情況下會(huì)出現(xiàn)空洞”吧!

為了便于說明，我們創(chuàng)建一個(gè)表t，其中id是自增主鍵字段、c是唯一索引。

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

自增值保存在哪兒？

在這個(gè)空表t里面執(zhí)行insert into t values(null, 1, 1);插入一行數(shù)據(jù)，再執(zhí)行show create table命令，就可以看到如下圖所示的結(jié)果：

圖1 自動(dòng)生成的AUTO_INCREMENT值

可以看到，表定義里面出現(xiàn)了一個(gè)AUTO_INCREMENT=2，表示下一次插入數(shù)據(jù)時(shí)，如果需要自動(dòng)生成自增值，會(huì)生成id=2。

其實(shí)，這個(gè)輸出結(jié)果容易引起這樣的誤解：自增值是保存在表結(jié)構(gòu)定義里的。實(shí)際上，表的結(jié)構(gòu)定義存放在后綴名為.frm的文件中，但是并不會(huì)保存自增值。

不同的引擎對(duì)于自增值的保存策略不同。

• MyISAM引擎的自增值保存在數(shù)據(jù)文件中。

• InnoDB引擎的自增值，其實(shí)是保存在了內(nèi)存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才實(shí)現(xiàn)了“如果發(fā)生重啟，表的自增值可以恢復(fù)為MySQL重啟前的值”，具體情況是：

• 在MySQL 5.7及之前的版本，自增值保存在內(nèi)存里，并沒有持久化。每次重啟后，第一次打開表的時(shí)候，都會(huì)去找自增值的最大值max(id)，然后將max(id)+1作為這個(gè)表當(dāng)前的自增值。?
  舉例來說，如果一個(gè)表當(dāng)前數(shù)據(jù)行里最大的id是10，AUTO_INCREMENT=11。這時(shí)候，我們刪除id=10的行，AUTO_INCREMENT還是11。但如果馬上重啟實(shí)例，重啟后這個(gè)表的AUTO_INCREMENT就會(huì)變成10。?
  也就是說，MySQL重啟可能會(huì)修改一個(gè)表的AUTO_INCREMENT的值。

• 在MySQL 8.0版本，將自增值的變更記錄在了redo log中，重啟的時(shí)候依靠redo log恢復(fù)重啟之前的值。

理解了MySQL對(duì)自增值的保存策略以后，我們?cè)倏纯醋栽鲋敌薷臋C(jī)制。

自增值修改機(jī)制

在MySQL里面，如果字段id被定義為AUTO_INCREMENT，在插入一行數(shù)據(jù)的時(shí)候，自增值的行為如下：

1. 如果插入數(shù)據(jù)時(shí)id字段指定為0、null 或未指定值，那么就把這個(gè)表當(dāng)前的 AUTO_INCREMENT值填到自增字段；

2. 如果插入數(shù)據(jù)時(shí)id字段指定了具體的值，就直接使用語句里指定的值。

根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系，自增值的變更結(jié)果也會(huì)有所不同。假設(shè)，某次要插入的值是X，當(dāng)前的自增值是Y。

1. 如果X<Y，那么這個(gè)表的自增值不變；

2. 如果X≥Y，就需要把當(dāng)前自增值修改為新的自增值。

新的自增值生成算法是：從auto_increment_offset開始，以auto_increment_increment為步長，持續(xù)疊加，直到找到第一個(gè)大于X的值，作為新的自增值。

其中，auto_increment_offset 和 auto_increment_increment是兩個(gè)系統(tǒng)參數(shù)，分別用來表示自增的初始值和步長，默認(rèn)值都是1。

備注：在一些場(chǎng)景下，使用的就不全是默認(rèn)值。比如，雙M的主備結(jié)構(gòu)里要求雙寫的時(shí)候，我們就可能會(huì)設(shè)置成auto_increment_increment=2，讓一個(gè)庫的自增id都是奇數(shù)，另一個(gè)庫的自增id都是偶數(shù)，避免兩個(gè)庫生成的主鍵發(fā)生沖突。

當(dāng)auto_increment_offset和auto_increment_increment都是1的時(shí)候，新的自增值生成邏輯很簡單，就是：

1. 如果準(zhǔn)備插入的值>=當(dāng)前自增值，新的自增值就是“準(zhǔn)備插入的值+1”；

2. 否則，自增值不變。

這就引入了我們文章開頭提到的問題，在這兩個(gè)參數(shù)都設(shè)置為1的時(shí)候，自增主鍵id卻不能保證是連續(xù)的，這是什么原因呢？

自增值的修改時(shí)機(jī)

要回答這個(gè)問題，我們就要看一下自增值的修改時(shí)機(jī)。

假設(shè)，表t里面已經(jīng)有了(1,1,1)這條記錄，這時(shí)我再執(zhí)行一條插入數(shù)據(jù)命令：

insert into t values(null, 1, 1);

這個(gè)語句的執(zhí)行流程就是：

1. 執(zhí)行器調(diào)用InnoDB引擎接口寫入一行，傳入的這一行的值是(0,1,1);

2. InnoDB發(fā)現(xiàn)用戶沒有指定自增id的值，獲取表t當(dāng)前的自增值2；

3. 將傳入的行的值改成(2,1,1);

4. 將表的自增值改成3；

5. 繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在c=1的記錄，所以報(bào)Duplicate key error，語句返回。

對(duì)應(yīng)的執(zhí)行流程圖如下：

                                                       圖3 一個(gè)自增主鍵id不連續(xù)的復(fù)現(xiàn)步驟

可以看到，這個(gè)操作序列復(fù)現(xiàn)了一個(gè)自增主鍵id不連續(xù)的現(xiàn)場(chǎng)(沒有id=2的行）?？梢?，唯一鍵沖突是導(dǎo)致自增主鍵id不連續(xù)的第一種原因。

同樣地，事務(wù)回滾也會(huì)產(chǎn)生類似的現(xiàn)象，這就是第二種原因。

下面這個(gè)語句序列就可以構(gòu)造不連續(xù)的自增id，你可以自己驗(yàn)證一下。

insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
//插入的行是(3,2,2)

你可能會(huì)問，為什么在出現(xiàn)唯一鍵沖突或者回滾的時(shí)候，MySQL沒有把表t的自增值改回去呢？如果把表t的當(dāng)前自增值從3改回2，再插入新數(shù)據(jù)的時(shí)候，不就可以生成id=2的一行數(shù)據(jù)了嗎？

其實(shí)，MySQL這么設(shè)計(jì)是為了提升性能。接下來，我就跟你分析一下這個(gè)設(shè)計(jì)思路，看看自增值為什么不能回退。

假設(shè)有兩個(gè)并行執(zhí)行的事務(wù)，在申請(qǐng)自增值的時(shí)候，為了避免兩個(gè)事務(wù)申請(qǐng)到相同的自增id，肯定要加鎖，然后順序申請(qǐng)。

1. 假設(shè)事務(wù)A申請(qǐng)到了id=2， 事務(wù)B申請(qǐng)到id=3，那么這時(shí)候表t的自增值是4，之后繼續(xù)執(zhí)行。

2. 事務(wù)B正確提交了，但事務(wù)A出現(xiàn)了唯一鍵沖突。

3. 如果允許事務(wù)A把自增id回退，也就是把表t的當(dāng)前自增值改回2，那么就會(huì)出現(xiàn)這樣的情況：表里面已經(jīng)有id=3的行，而當(dāng)前的自增id值是2。

4. 接下來，繼續(xù)執(zhí)行的其他事務(wù)就會(huì)申請(qǐng)到id=2，然后再申請(qǐng)到id=3。這時(shí)，就會(huì)出現(xiàn)插入語句報(bào)錯(cuò)“主鍵沖突”。

而為了解決這個(gè)主鍵沖突，有兩種方法：

1. 每次申請(qǐng)id之前，先判斷表里面是否已經(jīng)存在這個(gè)id。如果存在，就跳過這個(gè)id。但是，這個(gè)方法的成本很高。因?yàn)?，本來申?qǐng)id是一個(gè)很快的操作，現(xiàn)在還要再去主鍵索引樹上判斷id是否存在。

2. 把自增id的鎖范圍擴(kuò)大，必須等到一個(gè)事務(wù)執(zhí)行完成并提交，下一個(gè)事務(wù)才能再申請(qǐng)自增id。這個(gè)方法的問題，就是鎖的粒度太大，系統(tǒng)并發(fā)能力大大下降。

可見，這兩個(gè)方法都會(huì)導(dǎo)致性能問題。造成這些麻煩的罪魁禍?zhǔn)?，就是我們假設(shè)的這個(gè)“允許自增id回退”的前提導(dǎo)致的。

因此，InnoDB放棄了這個(gè)設(shè)計(jì)，語句執(zhí)行失敗也不回退自增id。也正是因?yàn)檫@樣，所以才只保證了自增id是遞增的，但不保證是連續(xù)的。

自增鎖的優(yōu)化

可以看到，自增id鎖并不是一個(gè)事務(wù)鎖，而是每次申請(qǐng)完就馬上釋放，以便允許別的事務(wù)再申請(qǐng)。其實(shí)，在MySQL 5.1版本之前，并不是這樣的。

接下來，我會(huì)先給你介紹下自增鎖設(shè)計(jì)的歷史，這樣有助于你分析接下來的一個(gè)問題。

在MySQL 5.0版本的時(shí)候，自增鎖的范圍是語句級(jí)別。也就是說，如果一個(gè)語句申請(qǐng)了一個(gè)表自增鎖，這個(gè)鎖會(huì)等語句執(zhí)行結(jié)束以后才釋放。顯然，這樣設(shè)計(jì)會(huì)影響并發(fā)度。

MySQL 5.1.22版本引入了一個(gè)新策略，新增參數(shù)innodb_autoinc_lock_mode，默認(rèn)值是1。

1. 這個(gè)參數(shù)的值被設(shè)置為0時(shí)，表示采用之前MySQL 5.0版本的策略，即語句執(zhí)行結(jié)束后才釋放鎖；

2. 這個(gè)參數(shù)的值被設(shè)置為1時(shí)：

• 普通insert語句，自增鎖在申請(qǐng)之后就馬上釋放；

• 類似insert … select這樣的批量插入數(shù)據(jù)的語句，自增鎖還是要等語句結(jié)束后才被釋放；

3. 這個(gè)參數(shù)的值被設(shè)置為2時(shí)，所有的申請(qǐng)自增主鍵的動(dòng)作都是申請(qǐng)后就釋放鎖。

你一定有兩個(gè)疑問：為什么默認(rèn)設(shè)置下，insert … select 要使用語句級(jí)的鎖？為什么這個(gè)參數(shù)的默認(rèn)值不是2？

答案是，這么設(shè)計(jì)還是為了數(shù)據(jù)的一致性。

我們一起來看一下這個(gè)場(chǎng)景：

                                                            圖4 批量插入數(shù)據(jù)的自增鎖

在這個(gè)例子里，我往表t1中插入了4行數(shù)據(jù)，然后創(chuàng)建了一個(gè)相同結(jié)構(gòu)的表t2，然后兩個(gè)session同時(shí)執(zhí)行向表t2中插入數(shù)據(jù)的操作。

你可以設(shè)想一下，如果session B是申請(qǐng)了自增值以后馬上就釋放自增鎖，那么就可能出現(xiàn)這樣的情況：

• session B先插入了兩個(gè)記錄，(1,1,1)、(2,2,2)；

• 然后，session A來申請(qǐng)自增id得到id=3，插入了（3,5,5)；

• 之后，session B繼續(xù)執(zhí)行，插入兩條記錄(4,3,3)、 (5,4,4)。

你可能會(huì)說，這也沒關(guān)系吧，畢竟session B的語義本身就沒有要求表t2的所有行的數(shù)據(jù)都跟session A相同。

是的，從數(shù)據(jù)邏輯上看是對(duì)的。但是，如果我們現(xiàn)在的binlog_format=statement，你可以設(shè)想下，binlog會(huì)怎么記錄呢？

由于兩個(gè)session是同時(shí)執(zhí)行插入數(shù)據(jù)命令的，所以binlog里面對(duì)表t2的更新日志只有兩種情況：要么先記session A的，要么先記session B的。

但不論是哪一種，這個(gè)binlog拿去從庫執(zhí)行，或者用來恢復(fù)臨時(shí)實(shí)例，備庫和臨時(shí)實(shí)例里面，session B這個(gè)語句執(zhí)行出來，生成的結(jié)果里面，id都是連續(xù)的。這時(shí)，這個(gè)庫就發(fā)生了數(shù)據(jù)不一致。

你可以分析一下，出現(xiàn)這個(gè)問題的原因是什么？

其實(shí)，這是因?yàn)樵瓗靤ession B的insert語句，生成的id不連續(xù)。這個(gè)不連續(xù)的id，用statement格式的binlog來串行執(zhí)行，是執(zhí)行不出來的。

而要解決這個(gè)問題，有兩種思路：

1. 一種思路是，讓原庫的批量插入數(shù)據(jù)語句，固定生成連續(xù)的id值。所以，自增鎖直到語句執(zhí)行結(jié)束才釋放，就是為了達(dá)到這個(gè)目的。

2. 另一種思路是，在binlog里面把插入數(shù)據(jù)的操作都如實(shí)記錄進(jìn)來，到備庫執(zhí)行的時(shí)候，不再依賴于自增主鍵去生成。這種情況，其實(shí)就是innodb_autoinc_lock_mode設(shè)置為2，同時(shí)binlog_format設(shè)置為row。

因此，在生產(chǎn)上，尤其是有insert … select這種批量插入數(shù)據(jù)的場(chǎng)景時(shí)，從并發(fā)插入數(shù)據(jù)性能的角度考慮，我建議你這樣設(shè)置：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row.這樣做，既能提升并發(fā)性，又不會(huì)出現(xiàn)數(shù)據(jù)一致性問題。

需要注意的是，我這里說的批量插入數(shù)據(jù)，包含的語句類型是insert … select、replace … select和load data語句。

但是，在普通的insert語句里面包含多個(gè)value值的情況下，即使innodb_autoinc_lock_mode設(shè)置為1，也不會(huì)等語句執(zhí)行完成才釋放鎖。因?yàn)檫@類語句在申請(qǐng)自增id的時(shí)候，是可以精確計(jì)算出需要多少個(gè)id的，然后一次性申請(qǐng)，申請(qǐng)完成后鎖就可以釋放了。

也就是說，批量插入數(shù)據(jù)的語句，之所以需要這么設(shè)置，是因?yàn)椤安恢酪A(yù)先申請(qǐng)多少個(gè)id”。

既然預(yù)先不知道要申請(qǐng)多少個(gè)自增id，那么一種直接的想法就是需要一個(gè)時(shí)申請(qǐng)一個(gè)。但如果一個(gè)select … insert語句要插入10萬行數(shù)據(jù)，按照這個(gè)邏輯的話就要申請(qǐng)10萬次。顯然，這種申請(qǐng)自增id的策略，在大批量插入數(shù)據(jù)的情況下，不但速度慢，還會(huì)影響并發(fā)插入的性能。

因此，對(duì)于批量插入數(shù)據(jù)的語句，MySQL有一個(gè)批量申請(qǐng)自增id的策略：

1. 語句執(zhí)行過程中，第一次申請(qǐng)自增id，會(huì)分配1個(gè)；

2. 1個(gè)用完以后，這個(gè)語句第二次申請(qǐng)自增id，會(huì)分配2個(gè)；

3. 2個(gè)用完以后，還是這個(gè)語句，第三次申請(qǐng)自增id，會(huì)分配4個(gè)；

4. 依此類推，同一個(gè)語句去申請(qǐng)自增id，每次申請(qǐng)到的自增id個(gè)數(shù)都是上一次的兩倍。

舉個(gè)例子，我們一起看看下面的這個(gè)語句序列：

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert…select，實(shí)際上往表t2中插入了4行數(shù)據(jù)。但是，這四行數(shù)據(jù)是分三次申請(qǐng)的自增id，第一次申請(qǐng)到了id=1，第二次被分配了id=2和id=3， 第三次被分配到id=4到id=7。

由于這條語句實(shí)際只用上了4個(gè)id，所以id=5到id=7就被浪費(fèi)掉了。之后，再執(zhí)行insert into t2 values(null, 5,5)，實(shí)際上插入的數(shù)據(jù)就是（8,5,5)。

這是主鍵id出現(xiàn)自增id不連續(xù)的第三種原因。

小結(jié)

今天，我們從“自增主鍵為什么會(huì)出現(xiàn)不連續(xù)的值”這個(gè)問題開始，首先討論了自增值的存儲(chǔ)。

在MyISAM引擎里面，自增值是被寫在數(shù)據(jù)文件上的。而在InnoDB中，自增值是被記錄在內(nèi)存的。MySQL直到8.0版本，才給InnoDB表的自增值加上了持久化的能力，確保重啟前后一個(gè)表的自增值不變。

然后，我和你分享了在一個(gè)語句執(zhí)行過程中，自增值改變的時(shí)機(jī)，分析了為什么MySQL在事務(wù)回滾的時(shí)候不能回收自增id。

MySQL 5.1.22版本開始引入的參數(shù)innodb_autoinc_lock_mode，控制了自增值申請(qǐng)時(shí)的鎖范圍。從并發(fā)性能的角度考慮，我建議你將其設(shè)置為2，同時(shí)將binlog_format設(shè)置為row。我在前面的文章中其實(shí)多次提到，binlog_format設(shè)置為row，是很有必要的。今天的例子給這個(gè)結(jié)論多了一個(gè)理由。

insert語句的鎖為什么這么多

盡量在申請(qǐng)到自增id以后，就釋放自增鎖。

因此，insert語句是一個(gè)很輕量的操作。不過，這個(gè)結(jié)論對(duì)于“普通的insert語句”才有效。也就是說，還有些insert語句是屬于“特殊情況”的，在執(zhí)行過程中需要給其他資源加鎖，或者無法在申請(qǐng)到自增id以后就立馬釋放自增鎖。

那么，今天這篇文章，我們就一起來聊聊這個(gè)話題。

insert … select 語句

我們先從昨天的問題說起吧。表t和t2的表結(jié)構(gòu)、初始化數(shù)據(jù)語句如下，今天的例子我們還是針對(duì)這兩個(gè)表展開。

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

現(xiàn)在，我們一起來看看為什么在可重復(fù)讀隔離級(jí)別下，binlog_format=statement時(shí)執(zhí)行：

insert into t2(c,d) select c,d from t;

這個(gè)語句時(shí)，需要對(duì)表t的所有行和間隙加鎖呢？

其實(shí)，這個(gè)問題我們需要考慮的還是日志和數(shù)據(jù)的一致性。我們看下這個(gè)執(zhí)行序列：

                                                                               圖1 并發(fā)insert場(chǎng)景

實(shí)際的執(zhí)行效果是，如果session B先執(zhí)行，由于這個(gè)語句對(duì)表t主鍵索引加了(-∞,1]這個(gè)next-key lock，會(huì)在語句執(zhí)行完成后，才允許session A的insert語句執(zhí)行。

但如果沒有鎖的話，就可能出現(xiàn)session B的insert語句先執(zhí)行，但是后寫入binlog的情況。于是，在binlog_format=statement的情況下，binlog里面就記錄了這樣的語句序列：

insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;

這個(gè)語句到了備庫執(zhí)行，就會(huì)把id=-1這一行也寫到表t2中，出現(xiàn)主備不一致。

insert 循環(huán)寫入

當(dāng)然了，執(zhí)行insert … select 的時(shí)候，對(duì)目標(biāo)表也不是鎖全表，而是只鎖住需要訪問的資源。

如果現(xiàn)在有這么一個(gè)需求：要往表t2中插入一行數(shù)據(jù)，這一行的c值是表t中c值的最大值加1。

此時(shí)，我們可以這么寫這條SQL語句 ：

insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

這個(gè)語句的加鎖范圍，就是表t索引c上的(4,supremum]這個(gè)next-key lock和主鍵索引上id=4這一行。

它的執(zhí)行流程也比較簡單，從表t中按照索引c倒序，掃描第一行，拿到結(jié)果寫入到表t2中。

因此整條語句的掃描行數(shù)是1。

這個(gè)語句執(zhí)行的慢查詢?nèi)罩荆╯low log），如下圖所示：

                                                                                圖3 慢查詢?nèi)罩?-將數(shù)據(jù)插入表t

可以看到，這時(shí)候的Rows_examined的值是5。

我在前面的文章中提到過，希望你都能夠?qū)W會(huì)用explain的結(jié)果來“腦補(bǔ)”整條語句的執(zhí)行過程。今天，我們就來一起試試。

如圖4所示就是這條語句的explain結(jié)果。

                                                                      圖5 查看 Innodb_rows_read變化

可以看到，這個(gè)語句執(zhí)行前后，Innodb_rows_read的值增加了4。因?yàn)槟J(rèn)臨時(shí)表是使用Memory引擎的，所以這4行查的都是表t，也就是說對(duì)表t做了全表掃描。

這樣，我們就把整個(gè)執(zhí)行過程理清楚了：

1. 創(chuàng)建臨時(shí)表，表里有兩個(gè)字段c和d。

2. 按照索引c掃描表t，依次取c=4、3、2、1，然后回表，讀到c和d的值寫入臨時(shí)表。這時(shí)，Rows_examined=4。

3. 由于語義里面有l(wèi)imit 1，所以只取了臨時(shí)表的第一行，再插入到表t中。這時(shí)，Rows_examined的值加1，變成了5。

也就是說，這個(gè)語句會(huì)導(dǎo)致在表t上做全表掃描，并且會(huì)給索引c上的所有間隙都加上共享的next-key lock。所以，這個(gè)語句執(zhí)行期間，其他事務(wù)不能在這個(gè)表上插入數(shù)據(jù)。

至于這個(gè)語句的執(zhí)行為什么需要臨時(shí)表，原因是這類一邊遍歷數(shù)據(jù)，一邊更新數(shù)據(jù)的情況，如果讀出來的數(shù)據(jù)直接寫回原表，就可能在遍歷過程中，讀到剛剛插入的記錄，新插入的記錄如果參與計(jì)算邏輯，就跟語義不符。

由于實(shí)現(xiàn)上這個(gè)語句沒有在子查詢中就直接使用limit 1，從而導(dǎo)致了這個(gè)語句的執(zhí)行需要遍歷整個(gè)表t。它的優(yōu)化方法也比較簡單，就是用前面介紹的方法，先insert into到臨時(shí)表temp_t，這樣就只需要掃描一行；然后再從表temp_t里面取出這行數(shù)據(jù)插入表t1。

當(dāng)然，由于這個(gè)語句涉及的數(shù)據(jù)量很小，你可以考慮使用內(nèi)存臨時(shí)表來做這個(gè)優(yōu)化。使用內(nèi)存臨時(shí)表優(yōu)化時(shí)，語句序列的寫法如下：

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t  (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

insert 唯一鍵沖突

前面的兩個(gè)例子是使用insert … select的情況，接下來我要介紹的這個(gè)例子就是最常見的insert語句出現(xiàn)唯一鍵沖突的情況。

對(duì)于有唯一鍵的表，插入數(shù)據(jù)時(shí)出現(xiàn)唯一鍵沖突也是常見的情況了。我先給你舉一個(gè)簡單的唯一鍵沖突的例子。

                                                                                圖7 唯一鍵沖突--死鎖

在session A執(zhí)行rollback語句回滾的時(shí)候，session C幾乎同時(shí)發(fā)現(xiàn)死鎖并返回。

這個(gè)死鎖產(chǎn)生的邏輯是這樣的：

1. 在T1時(shí)刻，啟動(dòng)session A，并執(zhí)行insert語句，此時(shí)在索引c的c=5上加了記錄鎖。注意，這個(gè)索引是唯一索引，因此退化為記錄鎖（如果你的印象模糊了，可以回顧下第21篇文章介紹的加鎖規(guī)則）。

2. 在T2時(shí)刻，session B要執(zhí)行相同的insert語句，發(fā)現(xiàn)了唯一鍵沖突，加上讀鎖；同樣地，session C也在索引c上，c=5這一個(gè)記錄上，加了讀鎖。

3. T3時(shí)刻，session A回滾。這時(shí)候，session B和session C都試圖繼續(xù)執(zhí)行插入操作，都要加上寫鎖。兩個(gè)session都要等待對(duì)方的行鎖，所以就出現(xiàn)了死鎖。

這個(gè)流程的狀態(tài)變化圖如下所示。

                                                                         圖9 兩個(gè)唯一鍵同時(shí)沖突

可以看到，主鍵id是先判斷的，MySQL認(rèn)為這個(gè)語句跟id=2這一行沖突，所以修改的是id=2的行。

需要注意的是，執(zhí)行這條語句的affected rows返回的是2，很容易造成誤解。實(shí)際上，真正更新的只有一行，只是在代碼實(shí)現(xiàn)上，insert和update都認(rèn)為自己成功了，update計(jì)數(shù)加了1， insert計(jì)數(shù)也加了1。

到此，相信大家對(duì)“MySQL主鍵自增在什么情況下會(huì)出現(xiàn)空洞”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！