Why MySQL Auto‑Increment IDs Aren’t Always Sequential (And How to Optimize Them)

1. Characteristics of Auto‑Increment Values

Auto‑increment primary keys are often assumed to be continuous, but MySQL does not guarantee sequential values. The storage location and handling of the auto‑increment counter differ between MySQL versions.

2. Where the Auto‑Increment Counter Is Stored

In MySQL 5.7 and earlier, the counter resides only in memory. On each server restart, InnoDB scans the table for the maximum id value and sets the next auto‑increment value to max(id)+1.

Since MySQL 8.0, changes to the counter are recorded in the redo log, allowing the value to be recovered after a restart without scanning the table.

The current counter can be inspected via the table’s AUTO_INCREMENT property.

3. How the Counter Is Modified During INSERT

When inserting a row into a table t with an auto‑increment column id:

If the id column is omitted, set to NULL, or set to 0, MySQL uses the current AUTO_INCREMENT value.

If a specific value is supplied for id, MySQL stores that value directly.

The relationship between the supplied value X and the current counter Y determines the outcome:

If X < Y, the counter remains unchanged.

If X ≥ Y, the counter is advanced to a new value greater than X.

4. Demonstrating the INSERT Flow

The following statement illustrates the steps: insert into t values(null, 1, 1); Execution steps:

Initial AUTO_INCREMENT=1 (next generated id will be 1).

InnoDB receives the row with (0,1,1).

Because id is not specified, InnoDB fetches the current counter (1).

The row is written with id=1 and the counter is incremented to 2.

Insert completes.

If the INSERT fails, the counter is not rolled back, leading to gaps.

5. Scenarios That Produce Gaps in Auto‑Increment Values

5.1 Unique‑Key Conflict

When a second INSERT attempts to use a duplicate value for a unique column (e.g., column c), the statement fails with a duplicate‑key error. The auto‑increment counter has already been advanced, so the next successful INSERT receives a higher id, creating a gap.

5.2 Transaction Rollback

A transaction that inserts a row and then rolls back still leaves the counter incremented, because the rollback does not revert the counter.

5.3 Batch Inserts

MySQL allocates a block of auto‑increment values for each batch. The allocation size doubles with each subsequent request within the same statement, e.g., 1 → 2 → 4, etc. If the batch fails after allocation, the reserved ids are lost, producing non‑continuous ids.

Example batch:

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);

After these statements, the next generated id may jump from 4 to 8, leaving gaps.

6. Optimizing Auto‑Increment Locking

MySQL uses an auto‑increment lock to serialize access to the counter. The lock behavior is controlled by the innodb_autoinc_lock_mode system variable.

Traditional (0) : The lock is held for the entire statement. Guarantees consistency but limits concurrency.

Consecutive (1) : For simple INSERTs the lock is released immediately after the value is obtained; for INSERT…SELECT it is held until the statement finishes.

Interleaved (2) : The lock is released right after each value is allocated, maximizing concurrency but potentially breaking consistency in certain replication setups.

7. Improvements in MySQL 8.0

MySQL 8.0 defaults innodb_autoinc_lock_mode=2 (Interleaved) and uses binlog_format=row. This combination improves concurrency for bulk inserts while preserving data consistency.