Why MySQL Auto‑Increment IDs Aren’t Always Sequential and How to Optimize Them

Test Environment

MySQL version: 8.0. Table t has columns: id (primary key), c (unique index), and d (regular field).

1. Characteristics of Auto‑Increment Values

Where are they stored?

In MySQL 5.7 and earlier, the next auto‑increment value is kept only in memory. After a restart the server scans the table for MAX(id) and uses MAX(id)+1 as the new start value.

Since MySQL 8.0, the value is recorded in the redo log. On restart InnoDB recovers the last value from the redo log, ensuring the same sequence continues.

You can view the current value with SHOW TABLE STATUS or by checking the AUTO_INCREMENT column in information_schema.tables.

How is the value modified?

If an INSERT omits the id column or supplies NULL / 0, MySQL inserts the current AUTO_INCREMENT value.

If the statement provides an explicit id, that value is used directly.

When the supplied value X is less than the current auto‑increment Y, the counter stays unchanged; if X ≥ Y, the counter is advanced to X+1.

2. How an INSERT Changes the Counter

insert into t values(null, 1, 1);

Execution flow (illustrated in the original diagram):

AUTO_INCREMENT = 1 – the next generated id.

Engine receives the row (0,1,1) and detects that id is not supplied.

InnoDB fetches the current AUTO_INCREMENT value (1).

The row is rewritten to (1,1,1) and the counter becomes 2.

The INSERT completes.

If the INSERT fails, the counter is not rolled back, which can create gaps.

3. Gaps Caused by Unique‑Key Conflicts

When a duplicate‑key error occurs on column c, the INSERT aborts but the auto‑increment value has already been incremented.

insert into t values(null, 1, 1);  -- first insert succeeds, id=1
insert into t values(null, 1, 1);  -- second insert fails with Duplicate key error

After the failure the counter is 3, so the next successful INSERT will receive id=3, leaving id=2 unused.

4. Gaps from Transaction Rollback

The same principle applies when an INSERT is rolled back due to any transaction error: the allocated auto‑increment value is not reclaimed.

5. Gaps from Batch Inserts

MySQL allocates a block of IDs for each batch request, doubling the block size each time the same statement asks for more IDs.

First request: 1 ID allocated.

Second request: 2 IDs allocated.

Third request: 4 IDs allocated.

Subsequent requests continue to double the block size.

Example:

insert into t values(null,1,1);
insert into t values(null,2,2);
insert into t values(null,3,3);
insert into t values(null,4,4);
create table tt like t;
insert into tt(c,d) select c,d from t;
insert into tt values(null,5,5);

The first three statements obtain IDs 1‑4, the fourth obtains IDs 5‑8, so the final row in tt gets id=8, demonstrating a gap.

6. Auto‑Increment Lock and Its Optimizations

When a transaction inserts into a table with an AUTO_INCREMENT column, it acquires an auto‑increment lock. Concurrent INSERTs must wait for the lock to be released.

MySQL 5.0 used a statement‑level lock. Starting with MySQL 5.1.22, the innodb_autoinc_lock_mode variable controls the lock scope:

Traditional (0) : lock released after the statement finishes; guarantees consistency but limits concurrency.

Consecutive (1) (default): lock released immediately for simple INSERTs, but retained for INSERT … SELECT and other batch operations.

Interleaved (2) : lock released right after each ID is allocated; maximizes concurrency but can break consistency in certain replication setups.

7. What MySQL 8.0 Changes

MySQL 8.0 defaults to innodb_autoinc_lock_mode=2 and sets binlog_format=row. This combination allows high‑concurrency batch inserts ( INSERT … SELECT) without sacrificing data consistency.