Auto Increment Skipping Numbers

Auto Increment skipping numbers?

The default auto_increment behavior in MySQL 5.1 and later will "lose" auto-increment values if the INSERT fails. That is, it increments by 1 each time, but doesn't undo an increment if the INSERT fails. It's uncommon to lose ~750 values but not impossible (I consulted for a site that was skipping 1500 for every INSERT that succeeded).

You can change innodb_autoinc_lock_mode=0 to use MySQL 5.0 behavior and avoid losing values in some cases. See http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html for more details.

Another thing to check is the value of the auto_increment_increment config variable. It's 1 by default, but you may have changed this. Again, very uncommon to set it to something higher than 1 or 2, but possible.

I agree with other commenters, autoinc columns are intended to be unique, but not necessarily consecutive. You probably shouldn't worry about it so much unless you're advancing the autoinc value so rapidly that you could run out of the range of an INT (this has happened to me).


How exactly did you fix it skipping 1500 for ever insert?

The cause of the INSERT failing was that there was another column with a UNIQUE constraint on it, and the INSERT was trying to insert duplicate values in that column. Read the manual page I linked to for details on why this matters.

The fix was to do a SELECT first to check for existence of the value before attempting to INSERT it. This goes against common wisdom, which is to just try the INSERT and handle any duplicate key exception. But in this case, the side-effect of the failed INSERT caused an auto-inc value to be lost. Doing a SELECT first eliminated almost all such exceptions.

But you also have to handle a possible exception, even if you SELECT first. You still have a race condition.

You're right! innodb_autoinc_lock_mode=0 worked like a charm.

In your case, I would want to know why so many inserts are failing. I suspect that like many SQL developers, you aren't checking for success status after you do your INSERTs in your AJAX handler, so you never know that so many of them are failing.

They're probably still failing, you just aren't losing auto-inc id's as a side effect. You should really diagnose why so many fails occur. You could be either generating incomplete data, or running many more transactions than necessary.

Sequential increment skipping numbers

serial columns, or IDENTITY in Postgres 10 or later, draw numbers from a SEQUENCE and gaps are to be expected. Their job is to make concurrent write access possible with unique numbers - not necessarily gap-less numbers.

If you don't actually have concurrent write access, there are simple ways to achieve (mostly) gap-less numbers. Like:

INSERT INTO tbl (info) 
SELECT 'xxx'
WHERE NOT EXISTS (SELECT FROM tbl WHERE info = 'xxx');

That doesn't burn a serial ID from the SEQUENCE because a duplicate insert is skipped. (The INSERT might still fail for any other reason - and burn a serial number. You could reset the SEQUENCE in such a case:

  • How to reset postgres' primary key sequence when it falls out of sync?

While inserting multiple rows in a single statement, you also have to rule out duplicates within the inserted set. Example code:

  • Return data from subselect used in INSERT in a Common Table Expression

But if you do have concurrent writes, none of the above works reliably, on principle. You better learn to accept gaps in the IDs. You can always have a query with row_number() OVER (ORDER BY id) to generate gap-less numbers after the fact. However, the numbers are still arbitrary to a degree. Smaller numbers were not necessarily committed earlier. There are exceptions under concurrent write load. Related:

  • Primary Key Value Not Incrementing Correctly
  • Serial numbers per group of rows for compound key
  • Auto increment table column

Or consider a UUID instead (dat type uuid) and avoid the inherent problem of duplicates with random values in a huge key space. Not at all serial, though:

  • Generating a UUID in Postgres for Insert statement?

Why does my auto-incremented Id skip numbers in SQL Server?

What is causing this?

A couple of potential causes come to mind:

  • Rows were deleted?
  • The results you're looking at aren't sorted by id?
  • Identifiers were allocated in a transaction which wasn't committed?
  • The database engine allocated potential identifiers as an internal performance tuning and that allocation was lost (unexpected server re-start, for example)?

There could be more potential causes I'm not thinking of.

How can I prevent this in the future?

Depends on the cause. But it's kind of a moot point, really. Why would you need to "prevent this"? What exactly is wrong with this situation? Identifiers don't need to be consecutive, they just need to be unique. (And preferably sequential for an index, otherwise the database will have to re-build the index.)

How can I stop my current table from continuing on this dark road?

The dark road of... generating unique identifiers? Well, I guess you could manually supply unique identifiers. GUIDs are good for that sort of thing. There are pros and cons, though. (I don't know if recent implementations have improved this, but historically GUIDs don't make for a good clustered index.)

MySQL: Why does my INSERT statement skip 56 numbers when auto-incrementing the id?

This behavior has something to do with "bulk inserts" and the innodb_autoinc_lock_mode setting.

As far as I understand it (the documentation isn't quite clear about this), when you use a INSERT INTO ... SELECT statement, MySQL cannot know how many rows are actually being inserted before running the query, but the IDs for the new AUTO_INCREMENT values have to be reserved when using innodb_autoinc_lock_mode=1 (consecutive) or 2 (interleaved). From my observation it reserves a set of AUTO_INCREMENT numbers where the count is a power of 2 (cannot confirm this, only a guess). See the following example:

CREATE TABLE sourceTable(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20)
);

CREATE TABLE targetTable(
id INT AUTO_INCREMENT PRIMARY KEY,
original VARCHAR(30)
);

INSERT INTO sourceTable(name) VALUES ('one');
INSERT INTO sourceTable(name) VALUES ('two');
INSERT INTO sourceTable(name) VALUES ('three');
INSERT INTO sourceTable(name) VALUES ('four');
INSERT INTO sourceTable(name) VALUES ('five');

INSERT INTO targetTable(original) SELECT name FROM sourceTable;

INSERT INTO targetTable(original) VALUES ('manual');

SELECT * FROM targetTable;

This will generate the following output:

+----+----------+
| id | original |
+----+----------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 5 | five |
| 8 | manual |
+----+----------+

When inserting the 5 rows from the source table, it reserves the next 8 possible AUTO_INCREMENT values because that is the closest power of 2 number greater than 5. However, it will use only 5 of them since you insert only 5 rows.

In your case, you are inserting 200 rows, so the closest power of 2 number greater than 200 would be 256. So you have a "gap" of 56 missing AUTO_INCREMENT values and the next entry gets the ID 256.

MySQL skipping auto_increment values

This is a documented behavior:

“Lost” auto-increment values and sequence gaps

In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”. Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.

In your use case, the auto_increment is assigned a new value before the unique constraint on username is checked. Then the constraint check fails and the transaction is rolled back, leaving a gap in the sequence.

There are various other cases than can lead to gaps in the auto_increment sequence (eg when using the INSERT ... IGNORE or INSERT ... ON DUPLICATE KEY syntax), that are in essence related to an implicit or explicit rollback of the transaction.

Bottom line: do not assume that aut_increment keys are sequential. What is guaranteed is uniqueness, and, to some extent, increasing numbers.



Related Topics



Leave a reply



Submit