How MySQL InnoDB Handles AUTO_INCREMENT: From 5.1 Locks to 8.0 Optimizations

MySQL 5.1之前的实现

Before MySQL 5.1, an AUTO_INCREMENT column (including non‑primary‑key columns with an index) was generated using a full‑table AUTO‑INC lock. Each INSERT acquired the lock, generated a sequential ID, inserted the row, and then released the lock, guaranteeing strictly increasing values but with poor concurrency.

CREATE TABLE t1 (
    c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c2 CHAR(1)
) ENGINE=InnoDB AUTO_INCREMENT=100;

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

The execution flow for a simple INSERT is:

Acquire full‑table AUTO‑INC lock.

Generate next auto‑increment value (e.g., 101), insert the row.

Repeat for each row (102, 103, …).

Release the lock.

MySQL 5.1版本带来的优化

Starting with MySQL 5.1, simple INSERT statements no longer acquire a full‑table lock. Instead, InnoDB uses a lightweight mutex only while allocating the auto‑increment value, releasing it immediately after. This reduces the critical section and improves concurrency.

INSERT INTO t1 (c2) VALUES ('a');

For bulk inserts such as INSERT ... SELECT, the full‑table lock is still required because the number of rows cannot be determined in advance.

MySQL 8.0版本之后的优化

From MySQL 8.0 onward, the default replication method switched to row‑based replication, allowing even complex inserts to avoid the full‑table lock. All INSERT statements now only hold the lightweight mutex while generating the auto‑increment value, which may result in non‑sequential IDs but offers the best concurrency.

总结

If a transaction that performed an INSERT rolls back, the generated auto‑increment values are not rolled back, leading to gaps. The behavior of InnoDB’s auto‑increment generation can be tuned with the innodb_autoinc_lock_mode system variable:

Mode 0 (traditional): full‑table lock for each row.

Mode 1 (consecutive): allocate a batch of IDs at the start of the statement.

Mode 2 (interleaved): allocate IDs one‑by‑one with a lightweight mutex.

Choosing the appropriate mode depends on the workload and replication requirements.