AUTO_INCREMENT Handling in XtraDB/InnoDB
AUTO_INCREMENT handling in the XtraDB and InnoDB storage engines have significant performance improvements. The innodb_autoinc_lock_mode server system variable determines the lock mode for auto-increments.
Traditional Lock Mode
Set by innodb_autoinc_lock_mode=0, the traditional lock mode holds a table-level lock for all INSERTs until the end of the statement.
Consecutive Lock Mode
Set by innodb_autoinc_lock_mode=1, the consecutive lock mode holds a table-level lock for all bulk INSERTs (such as LOAD DATA or INSERT ... SELECT) until the end of the statement. For simple INSERTs, no table-level lock is held. Instead, a lightweight mutex is used which scales significantly better. This is the default setting.
Interleaved Lock Mode
Set by innodb_autoinc_lock_mode=2, no table-level locks are held at all. This is the fastest and most scalable mode, but is not safe for statement-based replication.
Initializing
InnoDB and XtraDB use an auto-increment counter that is stored in memory. When the server restarts, the counter is re-initialized, which cancels the effects of any AUTO_INCREMENT = N option in the table statements.
