在项目中时常会有这种需求，用户通过第三方系统登录时如果尚未注册，则自动给用户注册，注册过的用户自动登录。有时候图省事可能就直接INSERT INTO user ON DUPLICAET KEY UPDATE…一句 SQL 解决了，功能都正常，问题就是如果用户表中有auto_increment字段，则会导致auto_increment字段产生空洞问题，一段时间后会发现用户ID会经常出现不连续的情况，虽然mysql的自增ID可以很大，一般系统是够用的，但是对于强迫症患者这个是无法接受的。我测试的mysql版本为5.5.58，使用的是Innodb引擎，隔离级别为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
当用户从第三方登录时，我们校验通过后，会将手机号插入到user表里注册用户。如果用户已经存在，则更新最后登录时间，为了简便，经常像下面这么做，功能上看起来是没错的，问题就是运行一段时间后会发现user表的id字段居然是不连续的，而且经常两个id之间空洞还很大，比如上一个id是4，下一个变成了21。如下面例子中，再插入一条新记录时，id会变成3，也就是说id=2这个值被浪费了。
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官方文档已经提到过这个问题了其实，当表t1中列a已经有一个值为1的情况下，通常情况执行下面这两条语句效果是一样的，但是注意了，如果表t1是InnoDB引擎而且有一列为auto_increment的情况下，影响是不一样的，会产生前面提到的auto_increment空洞问题。MyISAM引擎的表不受此影响，不会产生空洞问题。
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;
更加确切的说，产生空洞问题还跟innodb_autoinc_lock_mode这个MySQL配置相关。该配置在MySQL5.1引入，是为了提升auto_increment字段的并发性能引入的，默认值为1。该值可以配置为0（traditional lock mode），1(consecutive lock mode)，2（interleaved lock mode），除了0基本不产生空洞外，配置其他值都是可能有auto_increment空洞的，简单总结如下，更详细的可以参考 innodb-auto-increment-handling。
2）如果设置为0，是traditional lock mode，则任意插入语句都会加 AUTO-INC 锁，基本不会产生空洞，除了1中的rollback情况外。
3）如果设置为1或者2的时候，simple inserts语句(simple inserts指的是那种能够事先确定插入行数的语句，比如INSERT/REPLACE INTO 等插入单行或者多行的语句，语句中不包括嵌套子查询)不会有空洞。但是对于bulk inserts（bulk inserts指的是事先无法确定插入行数的语句，比如INSERT/REPLACE INTO … SELECT FROM…, LOAD DATA等）和mixed-mode inserts(指的是simple inserts类型中有些行指定了auto_increment列的值有些没有指定，比如：INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’)和INSERT … ON DUPLICATE KEY UPDATE这种语句)会预先分配auto_increment值，导致一些浪费。 特别是设置为2的时候，在执行任意插入语句都不会加 AUTO-INC 锁，从而在语句执行过程中都可能产生空洞。
那为了减少第一节中的auto_increment空洞问题，一种方法就是INSERT前先判断下用户是否存在，不存在才执行插入语句，否则用户每次登录都会导致auto_increment值被浪费。方案如下：
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();
这个代码乍看是没有问题了，mobile是unique key，这样的FOR UPDATE似乎木有问题，这是一个lock read，而且是排他锁，一个session对这条记录加了排他锁，其他session不能对这条记录加锁和修改（不能 LOCK IN SHARE MODE 以及 UPDATE 等，要注意下SELECT FOR UPDATE只在事务中或者autocommit关闭的情况下才会加锁）。但是，这只在记录存在的情况下才是对记录加X锁，没有Gap锁。而如果这个记录不存在，则对第一个不满足条件的记录加Gap锁，保证没有满足条件的记录插入。
如果mobile=15012345678这条记录不存在，并发的多个session都可以进入SELECT … FOR UPDATE，因为都是加的Gap锁(X locks gap before rec)，Gap锁之间是兼容的。此时，其中任意一个session再执行 INSERT INTO user(mobile, last_login_time) VALUES(‘15012345678’, NOW())语句会因为加insert intention lock(注：插入意向锁是一种特殊的Gap锁，不是MySQL的表级意向锁IS，IX等)超时而执行失败。其实此时的Gap锁不只是锁住了 15012345678 这条记录，如果表中有其他的记录，会将可能插入 15012345678 的区间都锁住，MySQL加锁详细分析可以见参考资料5。
为此，如果要优化auto_increment的浪费问题，又要避免上一节提到的加锁超时问题，还是有点事情要做的。可行的几种方法如下：
a) 通过GET_LOCK(name, timeout)而不是FOR UPDATE来避免上一节提到的问题。
b) 如果对数据没有很强的提交读的需求，可以不加FOR UPDATE查询，如果记录不存在，然后再INSERT IGNORE INTO …。
c) 更tricky的做法，percona的这篇文章avoiding-auto-increment-holes-on-innodb-with-insert-ignore描述了一种很独特的方法来避免auto_increment的空洞问题，有兴趣的可以参考。
MySQL Innodb如果出现了一些加锁问题，可以通过下面这几个命令来辅助分析。
show engine innodb status;
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;