7.5.11.5 How an Auto-increment Column Works in InnoDB

After a database startup, when a user first does an insert to a table T where an auto-increment column has been defined, and the user does not provide an explicit value for the column, then InnoDB executes SELECT MAX(auto-inc-column) FROM T, and assigns that value incremented by one to the column and the auto-increment counter of the table. We say that the auto-increment counter for table T has been initialised.

InnoDB follows the same procedure in initialising the auto-increment counter for a freshly created table.

Note that if the user specifies in an insert the value 0 to the auto-increment column, then InnoDB treats the row like the value would not have been specified.

After the auto-increment counter has been initialised, if a user inserts a row where he explicitly specifies the column value, and the value is bigger than the current counter value, then the counter is set to the specified column value. If the user does not explicitly specify a value, then InnoDB increments the counter by one and assigns its new value to the column.

The auto-increment mechanism, when assigning values from the counter, bypasses locking and transaction handling. Therefore you may also get gaps in the number sequence if you roll back transactions which have got numbers from the counter.

The behaviour of auto-increment is not defined if a user gives a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.

User Comments

In our database scheme, it is very important that the Auto-Increment id field allow a value of '0'. Is there any way to modify InnoDB to allow an explicit set of '0' to an auto-increment column?

With open-source software anything is possible, but perhaps not justifiable considering the time it would take for your organisation to modify the source. If the model of using 0 is not already in place, I would reccomend rethinking your schema. Of course, you can always make arrangements with MySQL AB to sponsor a change for your specific needs.

If 0 is a form of flag in your scheme, I would reccomend moving this functionality to a seperate column.

It doesn't quite make sense to me why the gaps in column numbering sequences with auto_increment enabled are tolerated. I suppose it makes sense as to why it happens, but shouldn't there be a workaround for this? In our specific application, we need to have an order number (generated by the auto_increment mechanism) to pass to our credit card clearing house; if the credit card is denied, the transaction is rolled back. This causes problems because of the fact that there ends up being large gaps in what should be a consecutive order number sequence. Are there ways to get around this without locking the entire table while waiting on the clearing house for authorization?

getting gaps in autoincrement numbers is part of the transactionnal system. You just can't imagine it could work another way. But what you can do is designing your entities so that the rollback should almost never happen !

The example of orders for Credit Card transactions is a bad one ! What you want to order is "successful transactions" and not every transaction. What I mean is that in your design, you have to separate a Credit Card transaction from the successful orders. In this case, you should insert a new successful order only if you get a 'go' from the transaction process above : and then match you order number with the credit card transaction number.

If you still don't want to make a différence between these two distinct entities, and use the transaction number as the order number : you should then accept to record them all and add a functionnal status wich could mark them as "OK", "CANCELLED" or "ABORTED"

In both cases, you should then never get "unexplained GAPS" in order number unless you encounter a major probleme somewhere else in your application.

Again one way to reduce that, is to commit as soon as possible the creation of the transaction with a "ongoing" status, and at the end of you operation, you update the status to "OK" or "CANCELLED". In this was the worst that could happen to you is to have remaining "ongoing" transactions : but at least you'll never have GAPS again.

This trick is one of those many accounting solutions use for invoicing !

jr

Add your own comment.