mysql如何处理auto_increment

官网原文在这里，
http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html


重点就这段，因为它是默认使用的锁模式：


innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
This is the default lock mode. In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time.


“Simple inserts” (for which the number of rows to be inserted is known in advance) avoid table-level AUTO-INC locks by obtaining the required number of auto-increment values under the control of a mutex (a light-weight lock) that is only held for the duration of the allocation process, not until the statement completes. No table-level AUTO-INC lock is used unless anAUTO-INC lock is held by another transaction. If another transaction holds an AUTO-INC lock, a “simple insert” waits for theAUTO-INC lock, as if it were a “bulk insert”.


This lock mode ensures that, in the presence of INSERT statements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any“INSERT-like” statement are consecutive, and operations are safe for statement-based replication.


Simply put, this lock mode significantly improves scalability while being safe for use with statement-based replication. Further, as with “traditional” lock mode, auto-increment numbers assigned by any given statement are consecutive. There isno change in semantics compared to “traditional” mode for any statement that uses auto-increment, with one important exception.


The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “simple insert”. For such inserts, InnoDB allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.




重点归纳:

1）INSERT和REPLACE叫做简单插入；
INSERT ... SELECT, REPLACE ... SELECT, LOAD DATA 叫做成批插入
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 叫做混合插入

2）简单插入和混合插入不会表锁；
成批插入会表锁；

3）虽然混合插入预先也能知道插入几条，但INNODB会分给比所需更多的自增值，这将导致自增值出现断层。（我理解是本来，123456，混合插入变成126789）

4）无论简单插入、混合插入、成批插入，自增id都是各自连续的。原因是，比如成批插入在简单插入前执行，那简单插入语句一定会等待成批插入语句全部执行完再执行。