failed transaction upsets the auto increment values

[ajoo]

Hi everyone,

 

I have an application that makes use of a transaction, inserting and updating values in different tables. One of the first tables it inserts into has an auto increment ID field. This ID values is used / updated in some other tables. Everything works fine. If however the transaction fails then the auto_increment values are upset - meaning that the sequence is jumped.

 

For ex. if I had auto_increment values from 1 to 6 in this table and then the insert query fails for an auto_increment value of ID=7, then this value of ID = 7 is lost because the next time the query is run the auto_increment ID value will be equal to 8 and accordingly updated in the tables. ID value = 7 is lost. Is there any way to prevent this ? Please know that this is a multi - user application.

 

Thanks.      

 

 

[Barand]

And the missing id=7 matters because...?

 

Your database should function just the same if ids were allocated randomly rather than sequentially. The only thing that matters is that it should be unique.

[ajoo]

Hi Guru Barand, 

 

Thanks for the response. Well the missing id=7 doesn't matter really and the database will function correctly as you said. It's just that I feel that in a multi-user app it could result in a whole lot of lost ids and since the corresponding entries in different tables would have different values it could result in confusion especially if the number of entries is large. 

 

I mean if there is a way to keep the correspondence simple without much work then I would like to know and if it can be achieved in a simple manner I would like to implement it. 

 

Thanks loads for now and for so many times previously. 

[WinstonLA]

http://stackoverflow.com/questions/2787910/why-does-mysql-autoincrement-increase-on-failed-inserts

[ajoo]

Hi Thanks Winston, That was good reading. Well I guess gaps are good. Guru Barand is always right. Thanks !