Changes in InnoDB Auto‑Increment Locking Mechanism After MySQL 5.1.22 and Configuration Options

Before MySQL 5.1.22, InnoDB used a table lock to guarantee auto‑increment consistency; this works for single‑row inserts but under heavy concurrent inserts the lock blocks SQL execution, reduces efficiency, and can quickly exhaust max_connections, causing crashes.

Starting with version 5.1.22, InnoDB adopts a lightweight mutex for statements whose row count can be predicted. For example, if an INSERT is known to add three rows and the current AUTO_INCREMENT value is 1, InnoDB pre‑allocates values 1‑3 before the actual insert, allowing a subsequent INSERT to read AUTO_INCREMENT=4 and execute immediately without waiting for the first statement to finish.

This mutex‑based approach is effective for predictable‑row statements such as INSERT and REPLACE. However, statements where the number of rows cannot be determined in advance—such as INSERT ... SELECT ..., REPLACE ... SELECT ..., and LOAD DATA —still rely on the traditional table lock.

When an INSERT explicitly sets values for the auto‑increment column, InnoDB still pre‑allocates values for the total number of rows in the statement, not only the rows that actually use the auto‑increment. This can create gaps in the sequence, as illustrated by the example where the next AUTO_INCREMENT becomes 105 instead of 103.

The INSERT ... ON DUPLICATE KEY UPDATE statement behaves similarly, causing non‑continuous auto‑increment values; with the new mutex mode it should be avoided in replication environments.

The new auto‑increment mutex behavior is controlled by the configuration option innodb_autoinc_lock_mode, which offers three choices: innodb_autoinc_lock_mode = 0 – “traditional” lock mode: always uses a table lock. innodb_autoinc_lock_mode = 1 – default “consecutive” lock mode: uses the mutex when the row count is known, otherwise falls back to a table lock. innodb_autoinc_lock_mode = 2 – “interleaved” lock mode: always uses the mutex; this mode is unsafe for replication and generally not recommended.