InnoDBd的auto_increment以及innodb_autoinc_lock_mode

InnoDB的auto_increment，类似oracle的sequence，可以自动增长，通常用于主键；

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列赋值，当其大于当前计数器值时则重置计数器为此值，若为Null或0则照旧使用计数器；

 

根据其插入行数可将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锁；

1：bulk-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_increment为105，在sql执行时一次性分配

2：

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

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

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