在项目中偶尔会有这种需求，用户通过第三方系统登录时如果尚未注册，则自动给用户注册，注册过的用户则自动登录，更新最近登录时间等信息。有时候图省事可能就直接INSERT INTO user ON DUPLICAET KEY UPDATE...一句 SQL 解决了，功能没问题，只是如果用户表中有auto_increment字段，则容易导致auto_increment字段产生空洞问题，一段时间后会发现用户ID会经常出现不连续的情况，虽然MySQL的自增ID一般都够用，但是如果能减少不必要的空洞更好。

场景

当用户从第三方登录时，假定用的是手机号做唯一标识，通常在我们自己的系统中会建一个用户表，如下(MySQL版本为5.5.58，隔离级别为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中unique key的列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。

1）如果事务回滚了，则不管是0，1，2都会导致事务中使用过的auto_increment的值浪费。
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前先判断下用户是否存在，不存在才执行插入语句，存在则更新最近登录时间。

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等，它与前面FOR UPDATE加的Gap锁不兼容)超时而执行失败。其实此时的Gap锁不只是锁住了 15012345678 这条记录，如果表中有其他的记录，会将可能插入 15012345678 的区间都锁住，MySQL加锁详细分析可以见参考资料5。另外，是否启用Gap锁跟MySQL的配置项innodb_locks_unsafe_for_binlog有关，默认值为0，即是启用状态。

解决方案

若要减少auto_increment的浪费问题，又要避免上一节提到的加锁超时问题，还是有点事情要做的。可行的几种方法如下：

a) 如果对数据没有很强的提交读的需求，可以不加FOR UPDATE查询，若记录不存在，再INSERT IGNORE INTO ...。
b) 如果需要提交读的，可以通过GET_LOCK(str, timeout)而不是FOR UPDATE来避免上一节提到的问题。
c) percona的这篇文章avoiding-auto-increment-holes-on-innodb-with-insert-ignore描述了一种很tricky的方法来避免auto_increment的空洞问题，有兴趣的可以参考。

MySQL innodb在RR模式下，在非唯一索引上使用FOR UPDATE要慎重，因为会加gap lock，如果加锁不当很容易出现死锁。MySQL InnoDB引擎如果出现了一些加锁问题，可以通过下面这几个命令来辅助分析。

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

再谈MySQL auto_increment空洞问题

再谈MySQL auto_increment空洞问题

场景

分析

一种错误示范

解决方案

参考资料