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

Test Environment

MySQL version: 8.0

Table t has columns: id (primary key, auto‑increment), unique index c, and ordinary column d.

1. Characteristics of Auto‑Increment Values

1.1 Where the auto‑increment value is stored

MySQL 5.7 and earlier : The next auto‑increment value is kept only in memory. On server restart the engine scans the table for MAX(id) and sets the next value to MAX(id)+1.

MySQL 8.0 and later : Changes to the auto‑increment counter are recorded in the redo log, allowing the value to be recovered after a restart.

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

1.2 How the auto‑increment value is modified

When inserting a row into t:

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

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

The counter is then adjusted based on the relationship between the inserted value X and the current counter Y:

If X < Y, the counter remains unchanged.

If X ≥ Y, the counter is set to X+1.

2. How INSERT Changes the Auto‑Increment Counter

Example statement: INSERT INTO t VALUES (NULL, 1, 1); Execution flow:

AUTO_INCREMENT = 1 – the next generated id will be 1.

INSERT INTO t VALUES (NULL,1,1) – InnoDB receives the row with a placeholder id.

GET AUTO_INCREMENT = 1 – InnoDB fetches the current counter.

AUTO_INCREMENT = 2, INSERT INTO t VALUES (1,1,1) – The row is stored with id = 1 and the counter is advanced to 2.

Insert completes.

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

3. Scenarios That Cause Gaps

3.1 Unique‑key conflict

When a second INSERT tries to insert a row with a duplicate value in column c, MySQL raises a Duplicate key error. The auto‑increment counter has already been advanced, so the failed row leaves a gap.

3.2 Transaction rollback

Rolling back a transaction after an INSERT also leaves the counter unchanged, producing the same gap effect.

3.3 Batch INSERT

MySQL allocates a block of IDs for each batch request, doubling the block size each time the same statement requests IDs. 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 SELECT c,d FROM t;
INSERT INTO tt (c,d) VALUES (5,5);

After the first three INSERTs, the counter jumps from 1 to 7. The subsequent INSERT into tt receives id = 8, demonstrating a non‑continuous sequence.

4. Auto‑Increment Locking and Optimizations

4.1 What is an auto‑increment lock?

When a transaction inserts into a table with an AUTO_INCREMENT column, it acquires a table‑level lock that prevents other transactions from obtaining new IDs until the lock is released.

4.2 Lock‑mode options (innodb_autoinc_lock_mode)

Traditional (0) : Lock is held until the statement finishes. Guarantees consistency but limits concurrency.

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

Interleaved (2) : The lock is released right after the ID is allocated, maximizing concurrency but risking replication inconsistency if binlog format is not ROW.

4.3 MySQL 8.0 defaults

MySQL 8.0 sets innodb_autoinc_lock_mode=2 and binlog_format=ROW by default, which improves performance for bulk inserts while preserving data consistency.

5. Practical Takeaways

• Do not rely on auto‑increment values being gap‑free; they are designed for uniqueness, not continuity.

• Choose the appropriate innodb_autoinc_lock_mode based on your workload: use mode 1 for most OLTP workloads, mode 2 for high‑throughput bulk loads with row‑based binlog.

• Be aware that failed INSERTs, duplicate‑key errors, and transaction rollbacks will permanently advance the counter.

• In MySQL 8.0 the redo‑log persistence of the counter eliminates the need for a full table scan on restart.