菜单

auto_increment空洞难题,MySQL自增列锁形式

2020年1月19日 - 数据网络

 

MySQL中auto_increment字段估计大家都经常用到,特别是innodb引擎。我也经常用,只知道mysql可以保证这个字段在多进程操作时的原子性,具体原理不甚了了,一次心血来潮,遂去查阅了MySQL手册以及相关资料,了解了个大概。本文只探究mysql5.5中innodb引擎auto_increment的问题,因为myisam引擎不会存在auto_increment空洞问题。

关于MySQL innodb_autoinc_lock_mode介绍,innodbstrictmode

innodb_autoinc_lock_mode这个参数控制着在向有auto_increment
列的表插入数据时,相关锁的行为;

通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡

【0】我们先对insert做一下分类

首先insert大致上可以分成三类:

1、simple insert 如insert into t(name) values(‘test’)

2、bulk insert 如load data | insert into … select …. from ….

3、mixed insert 如insert into t(id,name)
values(1,’a’),(null,’b’),(5,’c’);

【1】innodb_autoinc_lock_mode
的说明

innodb_auto_lockmode有三个取值:

1、0 这个表示tradition 传统

2、1 这个表示consecutive 连续

3、2 这个表示interleaved 交错

【1.1】tradition(innodb_autoinc_lock_mode=0)
模式:

1、它提供了一个向后兼容的能力

2、在这一模式下,所有的insert语句(“insert like”)
都要在语句开始的时候得到一个表级的auto_inc锁,在语句结束的时候才释放这把锁,注意呀,这里说的是语句级而不是事务级的,一个事务可能包涵有一个或多个语句。

3、它能保证值分配的可预见性,与连续性,可重复性,这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。

4、由于在这种模式下auto_inc锁一直要保持到语句的结束,所以这个就影响到了并发的插入。

 【1.2】consecutive(innodb_autoinc_lock_mode=1)
模式:

1、这一模式下去simple insert 做了优化,由于simple
insert一次性插入值的个数可以立马得到 确定,所以mysql可以一次生成几个连续的值,用于这个insert语句;总的来说这个对复制也是安全的(它保证了基于语句复制的安全)

2、这一模式也是mysql的默认模式,这个模式的好处是auto_inc锁不要一直保持到语句的结束,只要语句得到了相应的值后就可以提前释放锁

【1.3】interleaved(innodb_autoinc_lock_mode=2)
模式

1、由于这个模式下已经没有了auto_inc锁,所以这个模式下的性能是最好的;但是它也有一个问题,就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

【2】如果你的二进制文件格式是mixed | row
那么这三个值中的任何一个对于你来说都是复制安全的。

由于现在mysql已经推荐把二进制的格式设置成row,所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2
这样可能知道更好的性能。

最后以一个关于auto_increment 的例子来结束

例子:不要没事去更新一个auto_increment
列的值

第一步:重现一下场景

create table t(x int auto_increment not null primary key);
insert into t(x) values(0),(null),(3);
select * from t;
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+

第二步:重现一下引发问题的SQL

update t set x=4 where x=1;
select * from t;
+---+
| x |
+---+
| 2 |
| 3 |
| 4 |
+---+

第三步:重现一下总是的表现形式

insert into t(x) values(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

第四步:对问题的总结

执行完第一步的时候mysql知道下一个auto_increment值是4。

执行完第二步的时候mysql并不知道4已经被人为的占用了,所以执行第三步的时候就出错了。

以上这篇关于MySQL
innodb_autoinc_lock_mode介绍就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持帮客之家。

innodb_autoinc_lock_mode介绍,innodbstrictmode
innodb_autoinc_lock_mode这个参数控制着在向有auto_increment
列的表插入数据时,相关锁的行为; 通…

对于innodb_autoinc_lock_mode
各种参数的值的含义,网上也有各种详解,看玩觉得意犹未尽,这里不做阐述,只动手测试,看看性能上,到底有没有理论上所说的差别。
对于自增列的锁定,据说是innodb_autoinc_lock_mode =
2模式下有较高的性能,MySQL 8.0下innodb_autoinc_lock_mode
默认值为2。
于是通过修改改参数,测试不同参数下的一些性能表现,其结果还是比较出乎意料的……

1.传统auto_increment原理

传统的auto_increment实现机制:mysql
innodb引擎的表中的auto_increment字段是通过在内存中维护一个auto-increment计数器,来实现该字段的赋值,注意自增字段必须是索引,而且是索引的第一列,不一定要是主键。例如我现在在我的数据库test中创建一个表t,语句如下:

CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB;

则字段a为auto_increment类型,在mysql服务器启动后,第一次插入数据到表t时,InnoDB引擎会执行等价于下面的语句:

SELECT MAX(a) FROM t FOR UPDATE;

Innodb获取到当前表中a字段的最大值并将增加1(默认是增加1,如果要调整为增加其他数目,可以设置auto_increment_increment这个配置的设置)然后赋值给该列以及内存中该表对应的计数器。

如果表t为空,则InnoDB用来设置的值为为1.当然这个默认值夜可以通过
auto_increment_offset这个配置项来修改。

auto-increment计数器初始化以后,如果插入数据没有指定auto_increment列的值,则Innodb直接增加auto-increment计数器的值并将增加后的值赋给新的列。如果插入数据指定了auto_increment列的值且这个值大于该表当前计数器的值,则该表计数器的值会被设置为该值。

插入数据时如果指定auto_increment列的值为NULL或者0,则和你没有指定这个列的值一样,mysql会从计数器中分配一个值给该列.而如果指定auto_increment列的值为负数或者超过该列所能存储的最大数值,则该行为在mysql中没有定义,可能会出现问题.根据我的测试来看,插入负值会有警告,不过最终存储的数据还是正确的.如果是超过了比如上面定义的表t的bigint类型的最大值,同样会有警告,而且插入的数值是bigint类型所能存储的最大值18446744073709551615.

在传统的auto_increment设置中,每次访问auto-increment计数器的时候,
INNODB都会加上一个名为AUTO-INC锁直到该语句结束(注意锁只持有到语句结束,不是事务结束).AUTO-INC锁是一个特殊的表级别的锁,用来提升包含auto_increment列的并发插入性能.因此,两个事务不能同时获取同一个表上面的AUTO-INC锁,如果持有AUTO-INC锁太长时间可能会影响到数据库性能(比如INSERT
INTO t1… SELECT … FROM t2这类语句).

 

2.改进的auto_increment

鉴于传统auto_increment机制要加AUTO-INC这种特殊的表级锁,性能还是太差,于是在mysql5.1开始,新增加了一个配置项innodb_autoinc_lock_mode来设定auto_increment方式.可以设置的值为0,1,2.其中0就是第一节中描述的传统auto_increment机制,而1和2则是新增加的模式,默认该值为1,可以中mysql配置文件中修改该值.这里主要来看看这两种新的方式的差别,在描述差别前需要先明确几个插入类型:

下面看看设置innodb_autoinc_lock_mode为不同值时的情况:

测试环境:
MySQL 8.0.12 ON CentOS 7,1核1G内存
虽然环境资源配置有限,这里目的不是做性能测试,主要是对比在innodb_autoinc_lock_mode
= 0和innodb_autoinc_lock_mode =
2模式下的性能对比(没有做参数为1的情况)

3.可能产生空洞原因总结

经过上面的文档分析,下面总结下针对auto_increment字段的各种类型的inserts语句可能出现空洞问题的原因:

另外注意的一点是,在master-slave这种架构中,复制如果采用statement-based
replication这种方式,则innodb_autoinc_lock_mode=0或1才是安全的。而如果是采用row-based
replication或者mixed-based
replication,则innodb_autoinc_lock_mode=0,1,2都是安全的。

测试方法:
本地Python开启多个线程,每个线程循环一定的数量,向某一个表中插入数据,看最终的时间表现情况
测试代码如下,开启20个线程,每个线程循环插入10000条数据,同时将当前线程Id写入当前数据行中(用来多个线程的执行是否是均匀或者说是交替的),看最终的时间。

4.实例

测试的两个表分别为t和t1,定义分别如下:

CREATE TABLE `t` (
  `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB;


CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB

首先在表t插入1-10000000共1千万条数据,为了后面测试方便。开启session1,执行下面语句:

insert into t1(c2) select * from t;

然后开启session2,在t1中插入数据:

insert into t1(c2) values(400);

针对innodb_autoinc_lock_mode不同的情况,新插入的数据的c1的值也不同。

innodb_autoinc_lock_mode=0时,因为session1的语句都是加AUTO-INC锁,因此,session1先开始的话,c1列的值都是1-10000000连续的值,由于在传统机制下,auto_increment值都是一个个分配,因此session2插入的数据c1的值则是10000001。最终看到的就是有两条这样的数据(400,400),(10000001,400)。

innodb_autoinc_lock_mode=1时,同样session1也会加AUTO-INC锁,但是由于该模式下会预先分配auto_increment的值,所以可以看到在session2中插入的数据的c1值不会是10000001,但是不会是1-10000000这其中的数字,因为session1有加AUTO-INC锁。最终的数据会是这样两条:(400,400),
(10026856,400)。

innodb_autoinc_lock_mode=2时,session1不会加AUTO-INC锁,因此虽然session2是后执行,但是并不影响auto_increment值分配,最终的值跟我们执行session2的时间有关,最终的值可能是这样的:(400,400),(1235603,400)这样的,会占用1-10000000之间的值。

#coding=utf-8import threadingimport pymysqlfrom time import ctime,sleepconnstr_tencent = {'host': '***.***.***.***', 'port': 3306, 'user': 'root', 'password': 'root', 'db': 'db01', 'charset': 'utf8mb4'}def access_mysql(para): conn = pymysql.connect(host=connstr_tencent['host'],       port=connstr_tencent['port'],       user=connstr_tencent['user'],       password=connstr_tencent['password'],       db=connstr_tencent['db'],       charset=connstr_tencent['charset'],       connect_timeout = 100000 ) cursor = conn.cursor() for i in range(10000):  cursor.execute(" insert into test_autoicrement(col2,col3,col4) values ('thread:{0}','thread:{0}','thread:{0}'); ".format(str(para))) cursor.close() conn.commit() conn.close()def main(): # 生成线程 threads = [] for i in range(20):  t = threading.Thread(target=access_mysql, args=(i,))  threads.append(t)
for t in threads:  t.setDaemon(True)  t.start() for t in threads:  t.join()if __name__ == '__main__': print("begin at {0}".format(ctime())) main() print("finsh at {0}".format(ctime()))

5.另外几点

1)关于innodb_autoinc_lock_mode=1时,auto_increment预先分配策略可以参照参考资料2,假定表t中已经初始有一条记录1,然后在表t中我们用““insert
into t select NULL from
t执行四次,可以看到表t中最终的记录会是1,2,3,4,6,7,8,9,13,14,15,16,17,18,19,20“`,其中5,10,11,12都浪费掉了。参考资料1后面部分也有讲到预分配问题。

2)INSERT INTO t1…SELECT … FROM t这类语句会对表t1加record
lock,如果隔离级别是read
committed,或者设置了innodb_locks_unsafe_for_binlog且隔离级别不是serialize,则不会对t加锁,否则对t加shared
next-key lock。

 

6.参考资料

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图