auto_increment空洞问题

作者: 银河网站登录  发布:2019-11-01

在项目中时常会有这种需求,用户通过第三方系统登录时如果尚未注册,则自动给用户注册,注册过的用户自动登录。有时候图省事可能就直接INSERT INTO user ON DUPLICAET KEY UPDATE...一句 SQL 解决了,功能都正常,问题就是如果用户表中有auto_increment字段,则会导致auto_increment字段产生空洞问题,一段时间后会发现用户ID会经常出现不连续的情况,虽然mysql的自增ID可以很大,一般系统是够用的,但是对于强迫症患者这个是无法接受的。我测试的mysql版本为5.5.58,使用的是Innodb引擎,隔离级别为Repeatable Read。

1 场景

当用户从第三方登录时,假定用的是手机号做唯一标识,通常在我们自己的系统中会建一个用户表,如下:

 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 |

2 分析

在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。

  • 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 锁,从而在语句执行过程中都可能产生空洞。

3 一种错误示范

那为了减少第一节中的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似乎木有问题,这是一个排他锁,一个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。

4 解决方案

为此,如果要优化auto_increment的浪费问题,又要避免上一节提到的死锁问题,还是有点事情要做的。可行的几种方法如下:

  • 要么就是干脆一点,在查询用户是否存在时直接用GET_LOCK(mobile),通过字符串锁而不是FOR UPDATE来避免上一节提到的问题。
  • 要么就是先不加FOR UPDATE查询一次用户表,如果用户不存在,然后再INSERT IGNORE INTO user ...。多一次查询,后面的逻辑不变。
  • 当然,percona的这篇文章avoiding-auto-increment-holes-on-innodb-with-insert-ignore银河网站登录,还有个很tricky的方法来避免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;

5 参考资料

  • 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

本文由银河网站登录发布于银河网站登录,转载请注明出处:auto_increment空洞问题

关键词:

上一篇:这里有一大波
下一篇:你应该这么走