MySQL自增锁

转载 2013年12月05日 23:44:40

最近在工作中遇到很多使用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();

五、自增锁

如果存在自增字段,MySQL会维护一个自增锁,和自增锁相关的一个参数为(5.1.22版本之后加入)

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

0:traditonal (每次都会产生表锁)

1:consecutive (会产生一个轻量锁,simple insert会获得批量的锁,保证连续插入)

2:interleaved (不会锁表,来一个处理一个,并发最高)

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的时候,如果发生Bulk inserts会在分配的时候向其他insert分配,就会出现主从不一致的情况,但是如果改为RBR就不会出现这种情况。

也就是说在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值,这样在表锁的情况下,就可以保证一致性了。

八、insert的补充说明

复制代码
1.“INSERT-like”:

INSERT, INSERTSELECT, REPLACE, REPLACESELECT, and LOAD DATA, INSERTVALUES(),VALUES()

2.“Simple inserts”

就是通过分析insert语句可以确定插入数量的insert语句, INSERT, INSERTVALUES(),VALUES()

3.“Bulk inserts”

就是通过分析insert语句不能确定插入数量的insert语句, INSERTSELECT, REPLACESELECT, LOAD DATA

4.“Mixed-mode inserts”

INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');

INSERTON DUPLICATE KEY UPDATE
复制代码

 

九、官方链接

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


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

Mysql新增字段到大数据表导致锁表

年初跳完槽,终于可以安心开始写一些博客了。进入新的公司,还是有蛮多收获的。那就先记一个昨天遇到的问题吧。      昨天晚上7点左右,对一张表进行加字段,大概200多万条记录,字段90多个的大表,结...
  • woyantianyi
  • woyantianyi
  • 2016年06月16日 11:34
  • 7150

Mysql中那些锁机制之InnoDB

Mysql中那些锁机制之InnoDB http://www.2cto.com/database/201508/429967.html 我们知道mysql在以前,存储引擎默认是MyISAM,但是随...
  • zhanghongzheng3213
  • zhanghongzheng3213
  • 2016年06月20日 16:51
  • 4653

mysql事务锁等待时间

记得以前,当出现:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction,要解决是一件麻烦的事情;特别是当...
  • sofia1217
  • sofia1217
  • 2015年12月28日 15:11
  • 2027

mysql插入数据后返回自增ID的方法

mysql插入数据后返回自增ID的方法   mysql和oracle插入的时候有一个很大的区别是,oracle支持序列做id,mysql本身有一个列可以做自增长字段,mysql在插入一条数据后,如...
  • ling_du
  • ling_du
  • 2016年04月17日 17:23
  • 3619

Mysql实现数据的不重复写入(insert if not exists)以及新问题:ID自增不连续的解答

最近做数据处理时候,遇到一个问题。用一个id自增主键时候,数据表中会插入大量重复数据(除ID不同)。这虽然对最终数据处理结果没有影响,但是有1个问题,如果数据量超大,对处理的速度影响成几何倍数增长!所...
  • u013171165
  • u013171165
  • 2014年03月18日 18:24
  • 10466

Mysql实现数据的不重复写入(insert if not exists)以及新问题:ID自增不连续的解答

最近做数据处理时候,遇到一个问题。用一个id自增主键时候,数据表中会插入大量重复数据(除ID不同)。这虽然对最终数据处理结果没有影响,但是有1个问题,如果数据量超大,对处理的速度影响成几何倍数增长!所...
  • liyong199012
  • liyong199012
  • 2014年03月19日 10:37
  • 25596

MYSQL获取自增主键【4种方法】

通常我们在应用中对mysql执行了insert操作后,需要获取插入记录的自增主键。本文将介绍java环境下的4种方法获取insert后的记录主键auto_increment的值: 通过JDBC2....
  • UltraNi
  • UltraNi
  • 2013年07月17日 13:28
  • 46342

MYSQL获取自增主键【4种方法】

抄袭自http://blog.csdn.net/ultrani/article/details/9351573 通常我们在应用中对MySQL执行了insert操作后,需要获取插入记录的自增主键。本文...
  • fengyong7723131
  • fengyong7723131
  • 2016年11月10日 11:15
  • 746

MYSQL 中利用insert对自增字段进行插值

mysql的insert/replace/update/delete & insert,update,delete多表操作 2008-03-28 15:44:10 from WriteDream...
  • ty_soft
  • ty_soft
  • 2012年02月14日 19:08
  • 14962

MySQL 使用自增ID主键和UUID 作为主键的优劣比较详细过程(500W单表)

测试缘由  一个开发同事做了一个框架,里面主键是uuid,我跟他建议说mysql不要用uuid用自增主键,自增主键效率高,他说不一定高,我说innodb的索引特性导致了自增id做主键是效率最好的,为了...
  • mchdba
  • mchdba
  • 2016年08月22日 19:26
  • 22488
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL自增锁
举报原因:
原因补充:

(最多只允许输入30个字)