xml地图|网站地图|网站标签 [设为首页] [加入收藏]

再谈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.Locking

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 |

1.1加锁模式:共享锁与独占锁

InnoDB实现了两类行级锁, shared(S)locks 和exclusive(X)locks

  • 共享锁用于事务读取
  • 独占锁用于事务更新或者删除
  • S锁与S锁不冲突,与X锁冲突;X锁与S锁冲突,与X锁冲突

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

1.2加锁粒度:意向锁

InnoDB支持多粒度锁,也就是行锁与表锁的共存。意向锁就是InnoDB使用的表级锁,表明了事务之后对于这个表所需要行级锁的类型(S|X)。因此意向锁也分为Intention shared(IS)和Intention exclusive(IX)。

For example,SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock.

反过来说,在获取S|X锁之前,需要取得相应的IS(或IX)|IX锁。
而这些锁的相容表如下:

X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

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。

1.3加锁类型

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;

Record Lock

Record lock是在索引项上的锁,例如下面的SQL会将所有c1=10的行全部锁住,无法insert、update、delete

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

对于没有索引的表,InnoDB也会创建一个隐藏的聚簇索引。

READ COMMITTED只有Record Lock没有Gap Lock和Next-key Lock,所以locking read(即LOCK IN SHARE MODEFOR UPDATE)、UPDATE和DELETE用的都是Record Lock
REPEATABLE READ则有Record Lock、Gap Lock和Next-key Lock

InnoDB使用行级锁时,是当它搜索或扫描一个表的索引,在它遇到的那个索引记录上加上共享或独占锁,所以InnoDB的行级锁其实就是索引记录锁(Record Lock)。

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_read-committed

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

Gap Lock

Gap Lock是将索引记录之间的范围加锁的锁,索引值在这段范围内的记录无法insert,如下SQL将锁住10<=c1<=20

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

Gap Lock可以是一段索引值、单个索引值或者空,这是在性能和并发度之间权衡的结果,所以只有部分事务隔离等级使用了。
Gap Lock在使用唯一索引查询单行结果的时候并不会被用到,但当这个唯一索引是联合索引,并且查询条件只包括了部分索引列的时候还是会被使用的。例如下面的SQL只会使用一个Record Lock,如果id有唯一索引的话,并不影响之前索引值上的插入。

SELECT  *  FROM child WHERE id =  100;

当id没有索引,或者不是唯一索引的时候,那么索引值之前的到上一个索引值之间的范围也会被锁。
Gap Lock只会阻止在这个gap范围内的插入操作,所以Gap X-Lock与Gap S-Lock的效果是一样的,并不会冲突。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks

Next-key Lock

Next-key Lock是在某一个索引记录上的Record Lock和这个索引记录之前的这段范围的Gap Lock的组合。
所以如果Session A拥有一个索引记录R上的共享|独占Next-key Lock,Session B就不能在R之前的这段索引记录范围内插入新的索引记录。
例如一个索引拥有10、11、13、20几个值,那么可能的Next-key Lock就包括了如下的间隔

(-infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, infinity)

InnoDB的默认事务隔离级别是REPETABLE READ,InnoDB使用Next-key Lock来搜索和扫描索引,可以防止Phantom Rows。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

Insert Intention Lock

Insert Intention Lock是insert操作在插入行(获取该行的X锁)之前使用的一种Gap Lock,多个Session如果要插入同一个Gap,但如果是不同的索引位置那么是不会冲突的。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks

AUTO-INC Lock

AUTO-INC Lock是事务往有Auto Increment字段的表插入记录时使用的一种表级锁,多个插入的事务需要等待来保证主键值的连续性。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-auto-inc-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode

2.Transaction Model

多版本(multi-versioning)数据库与两阶段加锁(two-phase locking)的结合。默认是行级锁以及无锁的一致性读(nonlocking consistent reads)。

2.1Transaction Isolation Levels

隔离性是数据库在面对多个事务同时执行修改或者查询时,调节性能、可靠性、一致性以及结果的可重现性的平衡的关键。
共有如下四个级别:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

InnoDB默认是REPEATABLE READ

REPEATABLE READ

  1. 同一个事务所有的Consistent reads(也就是默认情况,也就是不加锁)使用本事务第一次读时建立的快照,也就是说同一个事务中的各个SELECT彼此具有一致性(也就是可重复读,不会看到同时发生的其他事务对于数据的修改)。
  2. Locking reads(SELECT WITH FOR UPDATE OR LOCK IN SHARE MODE),UPDATE,DELETE语句,会根据语句是否在唯一索引上使用唯一的条件而选择不同的锁。
    • 唯一索引上的唯一条件,InnoDB会使用Record Lock把这个index Record锁住,而不是Gap Lock。
    • 其他的情况下,InnoDB会使用Gap Lock或Next-Key Lock将扫描的索引范围给锁住,以阻止其他Session在这段范围插入记录。

READ COMMITTED

  1. 每一个Consistent read,即使是在同一个事务中,都会重设读取最新的快照(所以会出现不可重复读的问题)。
  2. Locking reads,UPDATE,DELETE语句,InnoDB只会使用Record Lock锁住这个索引值,不会锁住Gap,也就是允许锁住的记录附近的值的插入(会有幻读的问题),Gap Locking只会在外键检查和重复键检查中使用。
  • 对于UPDATE、DELETE语句,InnoDB只会保持它需要更新或删除的行的锁,对于并不匹配的行的Record Locks,会在MySQL计算完WHERE语句后释放。
  • 对于一个UPDATE语句,如果一个行已经被锁,InnoDB使用一个半一致性读(semi-consistent read),返回最新提交到MySQL的数据版本,如果这个行匹配UPDATE的WHERE字句,那么MySQL会重读这个行,并加锁或者等待锁。

READ UNCOMMITTED

SELECT语句不会加锁,可能会读到一个行稍早的数据版本,因此在这个级别上,读是不一致的。也就是脏读。其他方面这个级别和READ COMMITTED是类似的。

SERIALIZABLE

这个级别类似于REPEATABLE READ

  • autocommit disabled,InnoDB会隐性地把所有SELECT转换为SELECT IN SHARE MODE
  • antocommit enabled,SELECT就是自己的事务

2.2autocommit、commit、rollback

在InnoDB,所有的用户活动都发生在事务中,如果autocommit开启了,每个SQL语句都会自己构成一个事务,MySQL默认为每个Session开启autocommit,所以每个SQL语句执行完并没有出错的话,MySQL都会执行一次commit。如果语句返回错误,那么会根据错误执行commitrollback。

  1. 对于开启了autocommit的Session,可以通过START TRANSACTION或者BEGIN语句来开启一个多语句的事务,然后通过COMMITROLLBACK来结束事务。
  2. 如果autocommit关闭了,那么session永远处于一个开启的事务中,一个COMMITROLLBACK语句会结束当前的事务然后开启新的事务。(如果session结束时没有显式地提交最后的事务,那么MySQL会回滚)

某些语句会隐式地提交事务。
一个COMMIT语句表明当前事务的修改已经持久化,并且对其他session可见;而一个ROLLBACK语句取消了当前事务做的所有修改。COMMIT与ROLLBACK会释放所有当前事务的InnoDB锁。

2.3Consistent nonblocking read

snapshot

某一个特定时间的数据表示,即使其他事务提交了修改也保持不变,特定的隔离级别使用这个来实现一致性读(consistent read)。

consistent read

是使用快照(snapshot)信息来展示查询结果的一个读操作,也就意味着InnoDB使用multi-versioning来展示某个时刻数据库快照的查询结果。这个查询能看到这个时刻之前提交的事务修改,而不会看到同一时刻其他事务进行的修改,例外是能看到本事务之前提交的修改。如果查询的数据已经被其他事务修改,那么原始数据会通过undo log的内容来重建(恢复)。这个技术避免了一些锁带来的并发问题。
REPEATABLE READ级别,snapshot是进行本事务第一次读操作时的数据,只有事务提交之后才能得到一个新版本的snapshot。
READ COMMITTED级别,snapshot在每次consistent读操作时重设。
Consistent read是InnoDB在RC|RR级别处理SELECT语句时的默认模式,因为consistent read不会给它访问的表加锁,其他session可以在一个consistent read操作时自由地修改这些表。
假设默认的级别上,当你执行一个consistent read时,InnoDB会给你的事务一个时间点,你的查询看到的数据库就是这个时间点的数据库。如果其他事务在这个时间点之后删除了一行并提交,你看到的数据库中那一行并不会被删掉,插入与更新也类似。

snapshot状态适用于SELECT语句,而不是DML语句,如果事务A插入或更新了一些行并提交,那么RR级别的其他事务B即使无法通过查询看到这个变化,但事务B的DELETE/UPDATE语句也是会影响到刚才提交的那些行,如果发生了这种情况,那么这些变化对于当前事务B就会可见,例如:

SELECT  COUNT(c1)  FROM t1 WHERE c1 =  'xyz';  
-- Returns 0: no rows match.  
DELETE  FROM t1 WHERE c1 =  'xyz';  
-- Deletes several rows recently committed by other transaction.  
SELECT  COUNT(c2)  FROM t1 WHERE c2 =  'abc';  
-- Returns 0: no rows match.  
UPDATE t1 SET c2 =  'cba'  WHERE c2 =  'abc';  
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.  
SELECT  COUNT(c2)  FROM t1 WHERE c2 =  'cba';  
-- Returns 10: this txn can now see the rows it just updated.

你可以通过提交事务来推进时间点。
这也就是所谓的Multi-versioned concurrency control。
下面的例子中,session A只有在session B提交了插入操作并且自己也提交之后才能看到B插入的行,因为只有在A提交之后A的时间点才能越过B的提交。

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

如果你需要时刻看到最新状态的数据库,使用RC级别或者locking read。

Consistent read对于特定DDL语句也不生效:

  • DROP TABLE,因为MySQL无法使用一个删除了的表。
  • ALTER TABLE,因为这个语句会生成一个临时复制表,然后删除原表,当你重新执行一个consistent read的时候,新表里的行对于你的snapshot来说是不存在的,所以事务会报错。

2.4Locking Reads

如果你在事务中查询数据,然后进行插入或者更新,普通的SELECT语句无法提供足够的保护,其他事务可以更新或者删除你刚刚查询的数据行。InnoDB支持两种locking reads来提供额外的保护:

  • SELECT...LOCK IN SHARE MODE
    给要读得行上加共享锁,其他的事务可以读这些行,但只有你的事务提交以后才能修改它们。如果其他事务修改了这些行但是没有提交,那么你的查询需要等待那个事务结束然后使用最新的值。
  • SELECT...FOR UPDATE
    给行以及相关的索引记录加排他锁,与UPDATE语句类似。其他事务对于这些行的更新、Locking Reads、或者某些隔离级别下的读都会阻塞。Consistent reads会忽视记录上的锁。
    所有被Locking reads设置的锁会在事务提交或回滚的时候释放。

SELECT FOR UPDATE对于行的加锁只有在autocommit禁用的时候有效,可以通过START TRANSACTION或者将autocommit设为0来禁用。

3.Locks set by different SQL Statements in InnoDB

Locking read、UPDATE、DELETE通常会给处理SQL过程中所有扫描到的Index Record加上Record Lock。它并不会在意WHERE语句是否将这些行排除了。InnoDB不会记住WHERE条件,只知道它扫描过的index范围。加的锁一般来说是Next-key Lock,同时也会阻塞对Record前面的Gap进行插入的操作。但是,gap Locking可以被显式地禁用,事务隔离等级也会影响所使用的锁。
如果一个查询用了非聚簇索引并且加的Record Lock也是排它锁,InnoDB也会取出对应的聚簇索引,并在上面加锁。
如果你的语句没有合适的索引使用,那么MySQL必须扫描整个表来处理语句,那么表中的每一行都会被加锁,也就会阻塞其他用户对于这个表所有的插入,所以给表建立好的索引很重要,以防查询时扫描很多不必要的行。
InnoDB加锁的情况如下:

  • SELECT...FROM是consistent read,不加锁除非隔离级别设为SERIALIZABLE。SERIALIZABLE级别下,查询会给它遇到的索引记录加Next-key Lock,但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。

  • SELECT...FROM...LOCK IN SHARE MODE给它遇到的所有索引记录加共享Next-key Lock,但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。

  • SELECT...FROM...FOR UPDATE给它遇到的所有索引记录加排它Next-key Lock,但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。
    对于查询时遇到的索引记录,SELECT-FOR-UPDATE会阻塞其他session进行SELECT-IN-SHARE或者某些级别下的读。Consistent read会忽视它数据视图上的记录上的所有锁。

  • UPDATE...WHERE...给它遇到的所有索引记录加排它Next-key Lock,但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。

  • 当UPDATE修改了一个聚簇索引记录,那么相关的非聚簇索引记录上的隐式锁会被拿掉。UPDATE操作也会把受到影响的非聚簇索引记录上的共享锁拿掉,当执行插入新的非聚簇索引记录前重复值扫描或者插入新的非聚簇索引记录时。

  • DELETE FROM...WHERE...给它遇到的所有索引记录加排它Next-key Lock,但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。

  • INSERT会给插入的行加上一个排它锁。这个锁是一个索引Record Lock,不是Next-key Lock(也就是没有Gap Lock)并且不会阻止其他事务往插入行之前的Gap中插入记录。
    插入行之前,会加一种Insert intention Gap Lock,表明打算要插入,这样多个插入同一个Gap的事务如果不是要插入到同一个位置那就不需要等待。
    如果发生duplicate key error,会加一个共享锁在冲突的行上。详见链接里的INSERT
    https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

  • INSERT...ON DUPLICATE KEY UPDATE与简单的INSERT不同,当发生duplicate key error发生的时候,会加一个排它锁在需要更新的行上。

  • REPLACE

  • INSERT INTO T SELECT...FROM S WHERE...加一个排它index Record Lock在每一个要插入到T的行上。详见文档。

  • AUTO_INCREMENT 详见文档。

  • LOCK TABLES 会加表锁,但这是在比InnoDB层更高的MySQL层加的锁。InnoDB在innodb_table_locks = 1(the default) and autocommit = 0的情况下能感知到表锁,而MySQL层一直能感知到行锁。

4.Phantom Rows

5.Deadlocks

本文由奥门金沙睹场www462net发布于汽车配件,转载请注明出处:再谈MySQL auto_increment空洞问题

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。