Jump to content

failed transaction upsets the auto increment values


ajoo
Go to solution Solved by WinstonLA,

Recommended Posts

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.      

 

 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.