在項(xiàng)目中偶爾會(huì)有這種需求士嚎，用戶通過(guò)第三方系統(tǒng)登錄時(shí)如果尚未注冊(cè)戳粒，則自動(dòng)給用戶注冊(cè)役首，注冊(cè)過(guò)的用戶則自動(dòng)登錄，更新最近登錄時(shí)間等信息掺逼。有時(shí)候圖省事可能就直接INSERT INTO user ON DUPLICAET KEY UPDATE...一句 SQL 解決了吃媒，功能沒(méi)問(wèn)題，只是如果用戶表中有auto_increment字段吕喘，則容易導(dǎo)致auto_increment字段產(chǎn)生空洞問(wèn)題赘那，一段時(shí)間后會(huì)發(fā)現(xiàn)用戶ID會(huì)經(jīng)常出現(xiàn)不連續(xù)的情況，雖然MySQL的自增ID一般都?jí)蛴寐戎剩侨绻軠p少不必要的空洞更好募舟。

場(chǎng)景

當(dāng)用戶從第三方登錄時(shí)，假定用的是手機(jī)號(hào)做唯一標(biāo)識(shí)闻察，通常在我們自己的系統(tǒng)中會(huì)建一個(gè)用戶表拱礁，如下(MySQL版本為5.5.58琢锋，隔離級(jí)別為Repeatable Read)：

 CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) DEFAULT NULL,
  `last_login_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

當(dāng)用戶從第三方登錄時(shí)，我們校驗(yàn)通過(guò)后呢灶，會(huì)將手機(jī)號(hào)插入到user表里注冊(cè)用戶吴超。如果用戶已經(jīng)存在，則更新最后登錄時(shí)間鸯乃，為了簡(jiǎn)便鲸阻，經(jīng)常像下面這么做，功能上看起來(lái)是沒(méi)錯(cuò)的缨睡，問(wèn)題就是運(yùn)行一段時(shí)間后會(huì)發(fā)現(xiàn)user表的id字段居然是不連續(xù)的鸟悴，而且經(jīng)常兩個(gè)id之間空洞還很大，比如上一個(gè)id是4奖年，下一個(gè)變成了21遣臼。如下面例子中，再插入一條新記錄時(shí)拾并，id會(huì)變成3，也就是說(shuō)id=2這個(gè)值被浪費(fèi)了鹏浅。

mysql> INSERT INTO user(mobile, last_login_time) VALUES('15012345678',
 NOW()) ON DUPLICATE KEY UPDATE last_login_time = NOW();
Query OK, 1 row affected (0.00 sec)

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
......
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |

mysql> INSERT INTO user(mobile, last_login_time) VALUES('15012345678', 
NOW()) ON DUPLICATE KEY UPDATE last_login_time = NOW();
Query OK, 2 rows affected (0.00 sec)

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+-------+---------------------------------------------------------------------
| user  | CREATE TABLE `user` (
......
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |

分析

在MySQL官方文檔其實(shí)提到過(guò)這個(gè)問(wèn)題嗅义，當(dāng)表t1中unique key的列a已經(jīng)有一個(gè)值為1的情況下，通常情況執(zhí)行下面這兩條語(yǔ)句效果是一樣的隐砸，但是之碗，如果表t1是InnoDB引擎而且有一列為auto_increment的情況下，影響是不一樣的季希，會(huì)浪費(fèi)一個(gè)auto_increment值褪那。(注：MyISAM引擎的表不受此影響，不會(huì)產(chǎn)生空洞問(wèn)題）

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

確切的說(shuō)式塌，產(chǎn)生空洞問(wèn)題還跟innodb_autoinc_lock_mode這個(gè)MySQL配置相關(guān)博敬。該配置在MySQL5.1引入，是為了提升auto_increment字段的并發(fā)性能引入的峰尝，默認(rèn)值為1偏窝。該值可以配置為0（traditional lock mode），1(consecutive lock mode)武学，2（interleaved lock mode）祭往，除了0基本不產(chǎn)生空洞外，配置其他值都是可能有auto_increment空洞的火窒，簡(jiǎn)單總結(jié)如下硼补，更詳細(xì)的可以參考 innodb-auto-increment-handling。

• 1）如果事務(wù)回滾了熏矿，則不管是0已骇，1离钝，2都會(huì)導(dǎo)致事務(wù)中使用過(guò)的auto_increment的值浪費(fèi)。

• 2）如果設(shè)置為0疾捍，是traditional lock mode奈辰，則任意插入語(yǔ)句都會(huì)加 AUTO-INC 鎖，基本不會(huì)產(chǎn)生空洞乱豆，除了1中的rollback情況外奖恰。

• 3）如果設(shè)置為1或者2的時(shí)候，simple inserts語(yǔ)句(simple inserts指的是那種能夠事先確定插入行數(shù)的語(yǔ)句宛裕，比如INSERT/REPLACE INTO 等插入單行或者多行的語(yǔ)句瑟啃，語(yǔ)句中不包括嵌套子查詢)不會(huì)有空洞。但是對(duì)于bulk inserts（bulk inserts指的是事先無(wú)法確定插入行數(shù)的語(yǔ)句揩尸，比如INSERT/REPLACE INTO ... SELECT FROM..., LOAD DATA等）和mixed-mode inserts(指的是simple inserts類型中有些行指定了auto_increment列的值有些沒(méi)有指定蛹屿，比如：INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')和INSERT ... ON DUPLICATE KEY UPDATE這種語(yǔ)句)會(huì)預(yù)先分配auto_increment值，導(dǎo)致一些浪費(fèi)岩榆。 特別是設(shè)置為2的時(shí)候错负，在執(zhí)行任意插入語(yǔ)句都不會(huì)加 AUTO-INC 鎖，從而在語(yǔ)句執(zhí)行過(guò)程中都可能產(chǎn)生空洞勇边。

一種錯(cuò)誤示范

為了減少第一節(jié)中的auto_increment空洞問(wèn)題犹撒，一種方法就是INSERT前先判斷下用戶是否存在，不存在才執(zhí)行插入語(yǔ)句粒褒，存在則更新最近登錄時(shí)間识颊。

with transaction:
    user = SELECT * FROM user WHERE mobile = '15012345678' FOR UPDATE;
    if not user:
       INSERT INTO user(mobile, last_login_time) VALUES('15012345678', NOW()) 
    UPDATE user SET last_login_time = NOW();

這個(gè)代碼乍看是沒(méi)有問(wèn)題了，mobile是unique key奕坟，FOR UPDATE看似沒(méi)有問(wèn)題祥款，這是一個(gè)lock read，而且是排他鎖月杉。一個(gè)session對(duì)這條記錄加了排他鎖刃跛，其他session不能對(duì)這條記錄加鎖和修改，如不能 LOCK IN SHARE MODE 以及 UPDATE 等(注：SELECT ... FOR UPDATE只在事務(wù)中或者autocommit關(guān)閉的情況下才會(huì)加鎖）沙合。但是奠伪，這只在記錄存在的情況下才是對(duì)記錄加X(jué)鎖，沒(méi)有Gap鎖首懈。而如果這個(gè)記錄不存在绊率，則對(duì)第一個(gè)不滿足條件的記錄加Gap鎖，保證沒(méi)有滿足條件的記錄插入究履。

如果mobile=15012345678這條記錄不存在滤否，并發(fā)的多個(gè)session都可以進(jìn)入SELECT ... FOR UPDATE，都是加的Gap鎖(X locks gap before rec)最仑，這兩個(gè)Gap鎖之間是兼容的藐俺。此時(shí)炊甲，其中任意一個(gè)session再執(zhí)行 INSERT INTO user(mobile, last_login_time) VALUES('15012345678', NOW())語(yǔ)句會(huì)因?yàn)榧?code>insert intention lock(注：插入意向鎖是一種特殊的Gap鎖，不是MySQL的表級(jí)意向鎖IS欲芹，IX等卿啡，它與前面FOR UPDATE加的Gap鎖不兼容)超時(shí)而執(zhí)行失敗。其實(shí)此時(shí)的Gap鎖不只是鎖住了 15012345678 這條記錄菱父，如果表中有其他的記錄颈娜，會(huì)將可能插入 15012345678 的區(qū)間都鎖住，MySQL加鎖詳細(xì)分析可以見(jiàn)參考資料5浙宜。另外官辽，是否啟用Gap鎖跟MySQL的配置項(xiàng)innodb_locks_unsafe_for_binlog有關(guān)，默認(rèn)值為0粟瞬，即是啟用狀態(tài)同仆。

解決方案

若要減少auto_increment的浪費(fèi)問(wèn)題，又要避免上一節(jié)提到的加鎖超時(shí)問(wèn)題裙品，還是有點(diǎn)事情要做的俗批。可行的幾種方法如下：

• a) 如果對(duì)數(shù)據(jù)沒(méi)有很強(qiáng)的提交讀的需求市怎，可以不加FOR UPDATE查詢扶镀，若記錄不存在，再INSERT IGNORE INTO ...焰轻。
• b) 如果需要提交讀的，可以通過(guò)GET_LOCK(str, timeout)而不是FOR UPDATE來(lái)避免上一節(jié)提到的問(wèn)題昆雀。
• c) percona的這篇文章avoiding-auto-increment-holes-on-innodb-with-insert-ignore描述了一種很tricky的方法來(lái)避免auto_increment的空洞問(wèn)題辱志，有興趣的可以參考。

MySQL innodb在RR模式下狞膘，在非唯一索引上使用FOR UPDATE要慎重揩懒，因?yàn)闀?huì)加gap lock，如果加鎖不當(dāng)很容易出現(xiàn)死鎖挽封。MySQL InnoDB引擎如果出現(xiàn)了一些加鎖問(wèn)題已球，可以通過(guò)下面這幾個(gè)命令來(lái)輔助分析。

show engine innodb status;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;

參考資料

• https://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
• https://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html
• https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html
• https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/
• http://hedengcheng.com/?p=844