Why MySQL AUTO_INCREMENT Primary Keys Are Not Monotonic or Continuous

In relational databases, a primary key uniquely identifies each record. MySQL commonly uses an auto‑incrementing integer as the primary key, but this design has several nuances that can lead to non‑monotonic and non‑continuous values.

Early versions of MySQL stored the AUTO_INCREMENT counter in memory, resetting it to the maximum existing ID after a server restart. This caused gaps when records were deleted and the server was restarted, and could even produce duplicate keys if the counter was not correctly reinitialized.

MySQL determines the next auto‑increment value using SHOW CREATE TABLE to read the current counter. When inserting rows, the engine fetches the current AUTO_INCREMENT value, uses it as the primary key, and then increments the counter.

CREATE TABLE `trades` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17130 DEFAULT CHARSET=utf8mb4

Two main reasons explain why the auto‑increment value may become non‑continuous:

Older MySQL versions kept the counter in memory, so after a restart the counter was recomputed from the table data.

The AUTO_INCREMENT acquisition does not use a transaction lock; concurrent inserts can cause conflicts, leading to rolled‑back transactions that leave gaps.

InnoDB controls the locking behavior with the innodb_autoinc_lock_mode variable, which has three modes:

Traditional (0) : All INSERT statements acquire a table‑level lock on the auto‑increment counter, released after the statement.

Consecutive (1) (default): Bulk inserts (e.g., INSERT ... SELECT, LOAD DATA) acquire a table‑level lock, while simple INSERTs only lock the counter briefly.

Interleaved (2) : No table‑level lock is taken, but when the number of rows per statement is uncertain, duplicate keys may occur.

None of these modes fully guarantee continuous primary keys. The only way to ensure continuity is to serialize all insert transactions, for example by using the highest isolation level (SERIALIZABLE), though this can impact performance.

MySQL 8.0 changed the behavior: each change to the maximum auto‑increment value is written to the redo log and stored in an engine‑private system table at each checkpoint. This persistence allows the server to recover the correct counter after a crash or restart, eliminating the non‑monotonic issue caused by earlier versions.

Concurrency can still produce gaps: if two transactions insert rows concurrently and one rolls back due to a unique‑key conflict, the other’s successful insert leaves a missing value. The lock acquired for AUTO_INCREMENT is a statement lock, not a transaction lock, so it does not prevent such gaps.

In summary, MySQL’s auto‑increment primary keys are designed for performance and high‑throughput inserts, sacrificing strict continuity. Early MySQL versions stored the counter in memory, leading to gaps after restarts; MySQL 8.0 mitigates this by persisting the counter. Proper database design, such as using foreign keys and adhering to relational principles, can reduce the risk of data inconsistency caused by missing or duplicated keys.