Why Do MySQL Auto‑Increment IDs Skip? Understanding Gaps and Solutions

About Auto‑Increment Primary Keys

Auto‑increment primary keys are a common strategy for generating unique identifiers in database tables; when inserting a row, setting the key to NULL (or omitting it) lets the database assign the next value automatically.

Where the auto‑increment value is stored

Different storage engines handle the auto‑increment counter differently:

MyISAM stores the counter in the data file.

InnoDB (MySQL 5.7 and earlier) kept the counter in memory, recomputing it on restart by finding the maximum existing ID and adding the step size. Starting with MySQL 8.0, the counter changes are recorded in the redo log, allowing recovery after a restart.

How the auto‑increment allocation works

The diagram above shows the steps MySQL follows to assign an auto‑increment ID during an INSERT.

Cases Where Auto‑Increment IDs Are Not Continuous

1. Explicitly specifying the ID

If an INSERT statement provides a value for the primary‑key column, the auto‑increment mechanism is bypassed, which can create gaps.

2. Unique‑index violation during integration testing

insert into example_table values (null,111,1,0,'mock',now(),now());

This succeeds and the ID increments normally.

insert into example_table values (null,112,1,0,mock,now(),now());

The above fails because the creator column is not quoted, causing a syntax error; the attempted ID allocation is rolled back, but the allocated ID is not reclaimed, leading to a gap.

3. Transaction rollback

insert into example_table values (null,114,1,0,'mock',now(),now());

If this statement is executed inside a transaction that later rolls back, the allocated ID (e.g., 29) is lost, because the auto‑increment lock is released after the ID is assigned, not after the transaction commits.

4. Batch insert where some rows fail

insert into example_table values (null,111,1,0,'mock',now(),now()),(null,112,1,0,'mock',now(),now());

When a multi‑row INSERT succeeds, IDs are allocated consecutively. However, for INSERT ... SELECT statements, the number of rows is not known in advance, and failures can leave gaps.

5. Non‑default auto‑increment step

If the table is defined with AUTO_INCREMENT = 5 or a custom auto_increment_increment value, IDs will increase by that step, which may appear as non‑continuous.

Auto‑Increment Lock Modes (InnoDB)

InnoDB uses an auto‑increment lock whose behavior can be configured with innodb_autoinc_lock_mode:

0 – Traditional behavior (lock released after statement finishes).

1 – Default: lock released immediately for simple INSERTs, but held for bulk inserts.

2 – Lock released immediately for all INSERTs.

Understanding these modes helps explain why some IDs are skipped after failed statements.

Conclusion

MySQL’s auto‑increment mechanism can produce gaps due to explicit ID values, unique‑index violations, transaction rollbacks, batch insert failures, or custom step settings. These gaps are generally harmless, but being aware of the underlying allocation process helps developers diagnose unexpected ID sequences and design more robust data‑loading workflows.