mysql innodb 自增主键与innodb_autoinc_lock_mode

在MySQL 5.1.22之前，innodb使用一个表锁解决自增字段的一致性问题（内部是用一个计数器维护，每次自增时要加表锁），如果一行一行的插入数据则没有什么问题，但是如果大量的并发插入就废了，表锁会引起SQL堵塞，不但影响效率，而且可能会瞬间达到max_connections而崩溃。

在 5.1.22之后，innodb使用新的方式解决自增字段一致性问题，对于可以预判行数的insert语句，innodb使用一个轻量级的互斥量。如：某一insert语句1执行前，表的AUTO_INCREMENT=1，语句1的插入行数已知为3，innodb在语句1的实际插入操作执行前就预分配给该语句三个自增值，当有一个新的insert语句2要执行时，读取的AUTO_INCREMENT=4，这样虽然语句1可能还没有执行完，语句2就可直接执行无需等待语句2。

这种方式对于可预判插入行数的插入语句有效，如：insert和replace。对于无法提前获知插入行数的语句，如：insert...select...、replace...select...和load data则innodb还是使用表锁。

insert语句中有时会显示的设置自增字段的值，对于这种情况innodb还是会预分配给语句总行数的自增值而不是只有实际使用系统自增的行。因而有可能会造成自增字段的值不连续。如：

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

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

| c1  | c2   |

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

|   1 | a    |

| 101 | b    |

|   5 | c    |

| 102 | d    |

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

下一条语句获取的 AUTO_INCREMENT=105，而不是103。

INSERT ... ON DUPLICATE KEY UPDATE语句与之前一种情况类似，也会造成自增字段的值不连续,而且如果使用新的自增互斥方式，对于replication应该避免使用INSERT ... ON DUPLICATE KEY UPDATE语句。

设置新自增互斥方式：通过配置选项：innodb_autoinc_lock_mode，它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：

innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表锁)
innodb_autoinc_lock_mode = 1 (默认)(“consecutive” lock mode：可预判行数时使用新方式，不可时使用表锁) 

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode：全部使用新方式，不安全，不适合replication)

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html