MySQL自增锁

MySQL自增锁

原文:http://www.cnblogs.com/billyxp/archive/2013/01/22/2871737.html

最近在工作中遇到很多使用MySQL自带的autoincrement函数作为发号器,在实际使用中当并发比较小的时候还没有问题,一旦并发增加就会出现很多问题,特此进行如下总结。

一、自增配置

通过如下建表语句就可以完成自增的配置

CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、修改自增大小

通过如下sql可以自动生成数字:

insert into test_inc values();

当增加3行后表中数据如下:

+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+

CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

使用 alter table test_inc auto_increment=10;将自增修改成10后再次插入的数据为10.

+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 10 |
| 11 |
+----+

三、自增幅度

自增幅度通过auto_increment_offset和auto_increment_increment这2个参数进行控制

set global auto_increment_increment=2;
set global auto_increment_offset=2;
set session auto_increment_increment=2;
set session auto_increment_offset=2;

生成偶数的自增

set global auto_increment_increment=2;
set global auto_increment_offset=1;
set session auto_increment_increment=2;
set session auto_increment_offset=1;

生成奇数的自增

auto_increment_offset表示起始数字

auto_increment_increment表示调动幅度(即每次增加n个数字,2就代表每次+2)

四、获得最后一个数字

通过使用last_insert_id()函数可以获得最后一个插入的数字

select last_insert_id();

五、自增锁

5.1.22之前InnoDB自增值是通过其本身的自增长计数器来获取值,该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING),锁不是在每次事务完成后释放,而是在完成对自增长值插入的SQL语句后释放,要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候,innoDB会在内存里保存一个计数器用来记录auto_increment的值,当插入一个新行数据时,就会用一个表锁来锁住这个计数器,直到插入结束。如果大量的并发插入,表锁会引起SQL堵塞。

在5.1.22之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode,该实现方式是通过轻量级互斥量的增长机制完成的。它是专门用来在使用auto_increment的情况下调整锁策略

innodb_autoinc_lock_mode:可以设定3个值,0,1,2

0:traditonal 通过表锁的方式进行,所有类型的insert都用AUTO-inc locking。

1:consecutive 默认值,产生一个轻量锁,对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作,对于bulk insert 则还是使用表锁的方式进行。

2:interleaved 对所有的insert-like 自增长值的产生使用互斥量机制完成,并发性能最高,并发插入可能导致自增值不连续,可能会导致Statement 的 Replication 出现不一致,使用该模式,需要用 Row Replication的模式。

在mysql5.1.22之前,mysql的INSERT-LIKE语句会在执行整个语句的过程中使用一个AUTO-INC锁将表锁住,直到整个语句结束(而不是事务结束)。因此在使用INSERT…SELECT、INSERT…values(…),values(…)时,LOAD DATA等耗费时间较长的操作时,会将整个表锁住,而阻塞其他的insert-like,update等语句。推荐使用程序将这些语句分成多条语句,一一插入,减少单一时间的锁表时间。

ps:这个参数值控制InnoDB引擎的设置,所有Myisam均为traditonal,每次均会进行表锁。但是Innodb会视参数不同而产生不同的锁。目前MySQL默认的配置为1。

六、自增的过程

第一种,插入空值的时候

当innodb_autoinc_lock_mode=0时

复制代码
1、申请AUTO_INC锁

2、得到当前的AUTO_INCREMENT值n,并加1

3、执行插入操作,并将n写入新增的对应字段中。

4、释放AUTO_INC锁。
复制代码

第二种,插入已经有值的自增

 

复制代码
1、插入第一条数据

2、如果失败流程结束

3、如果成功,申请AUTO_INC锁

4、调用set_max函数,修改AUTO_INCREMENT

5、语句结束,释放AUTO_INC锁
复制代码

 

七、存在的问题

1、复制的问题

在innodb_autoinc_lock_mode=2的时候,由于是来一个分配一个,故当replication模式为SBR(statement-based replication, SBR)的时候,如果发生Bulk inserts会在分配的时候向其他insert分配,就会出现主从不一致的情况,但是如果改为RBR就不会出现这种情况。

也就是说在RBR(row-based replication, RBR)模式下,innodb_autoinc_lock_mode=2是安全的,其他情况还是建议设置为1.

2、load data的问题

当使用load data语句的时候,就算innodb_autoinc_lock_mode=1也会退化回0,这是因为为了保证数据的一致性。首先要说一下load data的执行过程,在主库上load data为原始SQL语句,而在从库上会先将文件传输过去在tmp下生成临时问题,然后在执行load data语句。为了保证主库和从库的自增ID的一致性,binlog中会有set insert_ID命令,标明这个load语句的第一行的自增ID值,这样在表锁的情况下,就可以保证一致性了。

3.自增主键不连续

5.1.22后 默认:innodb_autoinc_lock_mode = 
直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住

复制代码
root@localhost : test 04:23:28>show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

root@localhost : test 04:23:31>create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;
Query OK, 0 rows affected (0.16 sec)

root@localhost : test 04:23:35>insert into tmp_auto_inc(talkid) select talkId from talk_dialog limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

root@localhost : test 04:23:39>show create table tmp_auto_inc\G;
*************************** 1. row ***************************
       Table: tmp_auto_inc
Create Table: CREATE TABLE `tmp_auto_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `talkid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
复制代码

插入10条记录,但表的AUTO_INCREMENT=16,再插入一条的时候,表的自增id已经是不连续了。

原因:

      参数innodb_autoinc_lock_mode = 1时,每次会“预申请”多余的id(handler.cc:compute_next_insert_id),而insert执行完成后,会特别将这些预留的id空出,就是特意将预申请后的当前最大id回写到表中(dict0dict.c:dict_table_autoinc_update_if_greater)

      这个预留的策略是“不够时多申请几个”, 实际执行中是分步申请。至于申请几个,是由当时“已经插入了几条数据N”决定的。当auto_increment_offset=1时,预申请的个数是 N-1。

      所以会发现:插入只有1行时,你看不到这个现象,并不预申请。而当有N>1行时,则需要。多申请的数目为N-1,因此执行后的自增值为:1+N+(N-1)。测试中为10行,则:1+10+9 =20,和 16不一致?原因是:当插入8行的时候,表的AUTO_INCREMENT已经是16了,所以插入10行时,id已经在第8行时预留了,所以直接使用,自增值仍为16。所以当插入8行的时候,多申请了7个id,即:9,10,11,12,13,14,15。按照例子中的方法插入8~15行,表的AUTO_INCREMENT始终是16

验证:

插入16行:猜测 预申请的id:1+16+(16-1)= 32,即:AUTO_INCREMENT=32

复制代码
root@localhost : test 04:55:45>create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;
Query OK, 0 rows affected (0.17 sec)

root@localhost : test 04:55:48>insert into tmp_auto_inc(talkid) select talkId from sns_talk_dialog limit 16;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

root@localhost : test 04:55:50>show create table tmp_auto_inc\G;
*************************** 1. row ***************************
       Table: tmp_auto_inc
Create Table: CREATE TABLE `tmp_auto_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `talkid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
复制代码

和猜测的一样,自增id到了32。所以当插入16行的时候,多申请了17,18,19...,31 。

所以导致ID不连续的原因是因为innodb_autoinc_lock_mode = 1时,会多申请id。好处是:一次性分配足够的auto_increment id,只会将整个分配的过程锁住。

5.1.22前 默认:innodb_autoinc_lock_mode = 0

复制代码
root@localhost : test 04:25:12>show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 0     |
+--------------------------+-------+
1 row in set (0.00 sec)

root@localhost : test 04:25:15>create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;
Query OK, 0 rows affected (0.17 sec)

root@localhost : test 04:25:17>insert into tmp_auto_inc(talkid) select talkId from talk_dialog limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

root@localhost : test 04:25:21>show create table tmp_auto_inc\G;
*************************** 1. row ***************************
       Table: tmp_auto_inc
Create Table: CREATE TABLE `tmp_auto_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `talkid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
复制代码

插入10条记录,但表的AUTO_INCREMENT=11,再插入一条的时候,表的自增id还是连续的。

innodb_autoinc_lock_mode = 2 和 innodb_autoinc_lock_mode = 1 的测试情况一样。但该模式下是来一个分配一个,而不会锁表,只会锁住分配id的过程,和1的区别在于,不会预分配多个,这种方式并发性最高。但是在replication中当binlog_format为statement-based时存在问题

解决:

尽量让主键ID没有业务意义,或则使用simple inserts模式插入。

结论:

innodb_autoinc_lock_mode为0时候, 自增id都会连续,但是会出现表锁的情况,解决该问题可以把innodb_autoinc_lock_mode 设置为1,甚至是2。会提高性能,但是会在一定的条件下导致自增id不连续。

总结:

通过上面2个问题的说明,自增主键会产生表锁,从而引发问题;自增主键有业务意义,不连续的主键导致主从主键不一致到出现问题。对于simple inserts 的插入类型,上面的问题都不会出现。对于Bulk inserts的插入类型,会出现上述的问题。


八、insert的补充说明

INSERT-LIKE:   指所有的插入语句,比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等
Simple inserts:指在插入前就能确定插入行数的语句,包括INSERT、REPLACE,不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。
Bulk inserts:  指在插入前不能确定得到插入行的语句。如INSERT…SELECT, REPLACE…SELECT, LOAD DATA.
Mixed-mode inserts:指其中一部分是自增长的,有一部分是确定的。insert t1(id,xx) values(1,11)(null,22)

九、官方链接

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode 

  • 3
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值