Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
MyISAM or InnoDB tables? –  Cristian Porta Mar 6 at 10:28
    
@CristianPorta, it's InnoDB. –  Question Overflow Mar 6 at 10:31
    
Can you share your show variables like '%innodb_autoinc_lock_mode%'; output? –  Cristian Porta Mar 6 at 10:57
    
Are you sure there is no other connection/activity related to the table (inserting rows)? –  ypercube Mar 6 at 11:04
1  
@QuestionOverflow a good starting point: dev.mysql.com/doc/refman/5.5/en/… –  Cristian Porta Mar 6 at 11:37

1 Answer 1

up vote 1 down vote accepted

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 care of potential performance issues with triggers though).

share|improve this answer
    
Can you provide some references where this behaviour is being discussed? –  Question Overflow Mar 6 at 11:11
    
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. –  David Spillett Mar 6 at 11:47
1  
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." –  ypercube Mar 6 at 18:50
    
@ypercube, thanks, that's most helpful of you. –  Question Overflow Mar 7 at 3:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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