MySQL

MySQL Auto Increment Jumping Unexpectedly

Gaps appearing in an AUTO_INCREMENT sequence — IDs jumping from 105 straight to 150, for example — usually aren't a bug, but a normal consequence of how MySQL reserves auto-increment values, which happens before a transaction commits, not after.

The Problem

Reviewing a table's IDs reveals unexpected gaps that don't correspond to any deliberate deletion:

SELECT id FROM orders ORDER BY id;
-- 103, 104, 105, 150, 151, 152...
-- what happened to 106-149?

No rows with those IDs were ever visibly created and then deleted — as far as application logs show, the sequence should be continuous.

Why It Happens

MySQL's AUTO_INCREMENT mechanism reserves a value as soon as an insert attempt begins, not only once it successfully commits. This design choice, while sometimes surprising, is intentional for performance reasons — undoing an auto-increment reservation would require more coordination between concurrent transactions than MySQL's default behavior provides. Common causes of the resulting gaps:

  • A failed insert (due to a constraint violation, a duplicate key, or any other error) still consumed an auto-increment value before failing, and that value is never reused
  • A transaction that inserted rows was rolled back, but the auto-increment values reserved during that transaction are not returned to the pool — they remain permanently skipped
  • InnoDB's auto-increment locking mode (particularly in its default, most performant setting) pre-allocates a range of values for bulk inserts, and if not all reserved values in that range end up used, the unused ones create a gap
  • A server restart in certain configurations and MySQL versions can cause the in-memory auto-increment counter to be recalculated based on the maximum existing value plus some buffer, occasionally creating a larger-than-expected jump

The Fix

In most cases, gaps in auto-increment values are completely harmless and don't require any fix at all — an ID column's job is to be a unique identifier, not to be a gapless sequential count, and applications that depend on IDs having no gaps are relying on a guarantee MySQL was never actually designed to provide.

If your application logic mistakenly assumes IDs are sequential without gaps (for example, using the difference between max ID and row count as a proxy for "number of records," which silently breaks the moment any gap exists), fix that assumption in the application rather than trying to force MySQL's auto-increment to behave gaplessly, which fights against the database's actual design:

-- Correct way to count rows, not by ID math
SELECT COUNT(*) FROM orders;

If you need a genuinely gapless, sequential number for a specific business requirement (like a legally required sequential invoice number), don't rely on AUTO_INCREMENT for that purpose at all — implement a separate, explicitly managed sequence with its own transactional logic that specifically avoids reserving a number until the row is guaranteed to commit successfully:

-- Example: a separate table-based sequence with explicit locking,
-- generating the number only within the same transaction as the actual insert
SELECT next_invoice_number FROM sequence_table FOR UPDATE;
UPDATE sequence_table SET next_invoice_number = next_invoice_number + 1;

This pattern trades some performance and concurrency (since it requires row-level locking around the sequence) for the strict gaplessness guarantee that AUTO_INCREMENT alone can't provide.

To reduce (though not entirely eliminate) gaps caused by InnoDB's bulk-insert pre-allocation behavior specifically, you can adjust the innodb_autoinc_lock_mode setting, though this involves a genuine tradeoff with insert concurrency performance and should be changed deliberately, with an understanding of what you're giving up:

SET GLOBAL innodb_autoinc_lock_mode = 0;

Mode 0 (traditional) provides more predictable, gapless behavior for simple inserts at the cost of reduced concurrency for bulk insert operations, compared to the default mode 2 (interleaved) commonly used in modern MySQL versions for better multi-threaded insert performance.

Still Not Working?

If gaps are unusually large and frequent, and you've ruled out normal causes like failed inserts and rollbacks, check application-level retry logic for insert operations — a pattern that retries a failed insert by simply attempting it again (rather than checking whether the original failure was transient or permanent first) can consume multiple auto-increment values in quick succession for what should have been a single logical insert attempt, producing gaps disproportionate to the actual number of genuine failures occurring.

You might also like