InnoDBd的auto_increment以及innodb_autoinc_lock_mode

InnoDBauto_increment,类似oraclesequence,可以自动增长,通常用于主键;

auto_increment必须为索引列,其值一旦分配就不能随着相应sql的回滚而回退;

 

InnoDB表创建auto_increment列时,其数据字典会分配一个auto-increment计数器,在内存中操作维护;

第一次向表插入数据时,InnoDB会执行如下语句查看列当前最大值

Select max(ai_col) from t for update

在最大值上增加auto_increment_increment(默认为1)并赋予新插入的ai_col列,若表为空则初始值由auto_increment_offset(默认为1)决定;

也可以显示的为ai_col列赋值,当其大于当前计数器值时则重置计数器为此值,若为Null0则照旧使用计数器;

 

根据其插入行数可将insert分为3类:

Simple inserts:执行时可以确定行数

Bulk inserts:执行时行数不确定,load data/insert … select/replace … insert

Mixed-mode inserts:只有部分行使用auto_increment值,如insert … on duplicate key update

 

在对表进行并发bulk insert时,无法确定每行的auto_increment值,在进行recover或语句级复制时,会出现同一行记录拥有不同的auto_increment值;

 

早期版本,InnoDB在访问auto_increment计数器时会对相应表添加名为auto-inc的表级锁,在sql运行结束时释放,在此之前其他insert会被阻塞;

此举虽然保证了数据一致,但是降低了并发度,为此InnoDB引入了innodb_autoinc_lock_mode,用户可根据实际情况定制锁策略,该参数有如下值 

0:采用传统锁模式,所有insert操作都要申请auto-inc锁;

1bulk-inserts采用auto-inc锁,而simple insert则使用更为轻量级的mutex,如表已经有了auto-inc锁,simple insert也要等待直至其释放;

2:所有insert都不采用auto-inc锁,生成的auto_increment可能不连贯

 

至于最特殊的mixed-mode insert,会依据innodb_autoinc_lock_mode的值而产生不同的效果

--c1列采用auto_increment,且当前最大值为100,分别将innodb_autoinc_lock_mode设置为0/1/2进行如下操作

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

0

  • +-----+------+
  • | c1  | c2   |
  • +-----+------+
  • |   1 | a    |
  • | 101 | b    |
  • |   5 | c    |
  • | 102 | d    |
  • +-----+------+

--下一个auto_increment值为103,因为其按行一次分配一个,而不是在执行时一次性分配

1

  • +-----+------+
  • | c1  | c2   |
  • +-----+------+
  • |   1 | a    |
  • | 101 | b    |
  • |   5 | c    |
  • | 102 | d    |

+-----+------+

--下一条auto_increment105,在sql执行时一次性分配

2

  • +-----+------+
  • | c1  | c2   |
  • +-----+------+
  • |   1 | a    |
  • |   x | b    |
  • |   5 | c    |
  • |   y | d    |
  • +-----+------+

--X/Y具体值受其他并发操作影响


转自:http://blog.itpub.net/15480802/viewspace-752853/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值