16

I am very perturbed by this weird behaviour I am seeing in the auto_increment value recorded in the bidID of a Bids table after performing bulk insertion using a stored procedure:

INSERT INTO Bids (itemID, buyerID, bidPrice)
 SELECT itemID, rand_id(sellerID, user_last_id), FLOOR((1 + RAND())*askPrice)
 FROM Items
 WHERE closing BETWEEN NOW() AND NOW() + INTERVAL 1 WEEK ORDER BY RAND() LIMIT total_rows;

For example, if the auto_increment bidID value is 101 at start, and I inserted 100 rows, the ending value becomes 213 instead of 201. However, the bidIDs of those inserted rows runs sequentially to a maximum of 201.

Having check the following,

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

I have no idea why it is happening. What could be causing the jump in the auto increment value?

8

3 Answers 3

14

This is not unusual and there are a couple of causes. Sometimes it is due to optimisations the query runner makes to reduce contention issues with the counter resource, improving efficiency when there are concurrent updates to the affected table. Sometimes it is due to transactions that got explicitly rolled back (or implicitly rolled back due to encountering an error).

The only guarantees from an auto_increment column (or IDENTITY in MSSQL, and the other names the concept goes by) is that each value will be unique and never smaller than a previous one: so you can rely on the values for ordering but you can not rely on them not to have gaps.

If you need the column's values to have no gaps at all you will need to manage the values yourself, either in another layer of business logic or in the DB via a trigger (be careful of potential performance issues with triggers though), of course if you do roll your own you will have to contend with all the concurrency/rollback/cleanup-after-delete/other issues that the DB engines work around by allowing gaps).

5
  • Can you provide some references where this behaviour is being discussed? Mar 6, 2014 at 11:11
  • 1
    There are quite a few references to the matter here, on SO, and generally. Search for "IDENTITY gaps", "auto_increment gaps", and so forth, and you should find plenty of discussion. You might add your DBMS name to make the search more specific, though this is quite a general concept so that might not make any real difference unless you are looking under the hood at how it works in detail. Mar 6, 2014 at 11:47
  • 4
    See the details for MySQL: AUTO_INCREMENT Handling in InnoDB, where it mentions: "Gaps in auto-increment values for “bulk inserts” ... For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible." Mar 6, 2014 at 18:50
  • @ypercube, thanks, that's most helpful of you. Mar 7, 2014 at 3:18
  • Also see bugs.mysql.com/bug.php?id=34696.
    – trss
    Jan 5, 2018 at 9:27
1

It could have multiple causes:

  1. Check if the auto_increment value on the table itself, has the next highest value.

  2. Mind that if you have transactions where you INSERT a row and rollback the transaction, that auto_increment value will be gone/skipped.

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.

  1. You set the general config setting innodb_autoinc_lock_mode=1 (consecutive) or innodb_autoinc_lock_mode=2 (interleaved). In this case single INSERT statements and bulk INSERT statements could be run through each other. Set innodb_autoinc_lock_mode=0 to ensure statements are run in order.

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement are consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.

With innodb_autoinc_lock_mode set to 2 (“interleaved”), there may be gaps in the auto-increment values generated by “bulk inserts,” but only if there are concurrently executing “INSERT-like” statements.

For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible.

  1. Say your current auto_increment value on a table is 50 and you also have an id of 1000 in your table. The next auto_increment value will be 51. But when you restart the MySQL server, your next auto_increment id will be 1001.

In MySQL 5.7 and earlier, the auto-increment counter is stored only in main memory, not on disk. To initialize an auto-increment counter after a server restart, InnoDB would execute the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

All quotes are from: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html:

0

According to the source code https://github.com/mysql/mysql-server/blob/8.0/sql/handler.cc#L3820, for statements like INSERT INTO ... SELECT, where the exact number of inserted rows is unknown in advance, the SQL handler reserves intervals of auto-increment values while processing the records, every time reserving twice as larger an interval as the previous one as soon as the number of rows reaches the highest value of the previous interval. For example, when inserting 4 records, first, an interval of 1 gets reserved, afterwards an interval of 2, then 4, and in the end 8. In this particular case, the next auto-increment value is going to be 8 instead of 5, and as a result, a gap between 4 and 8 appears. With larger numbers, the gap gets much more significant, for instance, while processing data in bulk say by 5000, the next auto-increment will be the current column value + 8192 instead of the current column value + 5000 + 1. As a workaround to this, data could be processed in batches equal to the value of 2^n-1. However, even with this, gaps can't be avoided at all but at least could be reduced.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.