MySQL再谈MySQL auto_increment空洞问题

在档次中平时会有这种需要,用户通过第三方系统登录时要是没有注册,则自动给用户注册,注册过的用户自行登录。有时候图省事可能就径直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 参考资料

网站地图xml地图