InnoDB AUTO_INCREMENT Lock Modes

• Simple inserts： number of rows to be inserted can be determined in advance 事先知道插入的行数

• Bulk inserts： number of rows to be inserted (and the number of required auto-increment values) is not known 事先不知道插入的行数

  • INSERT ... SELECT
  • REPLACE ... SELECT
  • LOAD DATA

• Mixed-mode inserts: These are “simple insert” statements that specify the auto-increment value for some (but not all) of the new rows 简单插入中有些自增ID是指定的
  • INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
  • INSERT … ON DUPLICATE KEY UPDATE

innodb_autoinc_lock_mode

• innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
  • 兼容旧版本（ <= MySQL 5.1)
  • all “INSERT-like” statements obtain a special table-level AUTO-INC lock ,normally held until the end of a statement

• innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
  • “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement （锁表)
  • “Simple inserts” 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. （预分配需要自增的数量，只对分配过程加锁）
  • mixed-mode inserts: InnoDB allocates more auto-increment values than the number of rows to be inserted （混合插入模式下，分配的自增比实际需要的多)

• innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
  • no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time
  • not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.