What happens with auto-incremented value on rollback?
-
I'm trying to find the answer to this question. Let's say you have a MSSQL database which has a table with an auto-incremented field. If I have a Sql transaction sent to the server (through code) that does an insert into that table and the transaction fails (and does a rollback) will the current ID be lost or not? When does it generate the new ID? At transaction start or finish? Ex: table1 has column ID as auto-increment. the current ID is 100. If I send a transaction with an insert does is reserve the ID 101 and loose it if the transaction fails with rollback, meaning next ID would be 102? Or will it take the ID 101 only if the transaction commits successfully? thanks
-
I'm trying to find the answer to this question. Let's say you have a MSSQL database which has a table with an auto-incremented field. If I have a Sql transaction sent to the server (through code) that does an insert into that table and the transaction fails (and does a rollback) will the current ID be lost or not? When does it generate the new ID? At transaction start or finish? Ex: table1 has column ID as auto-increment. the current ID is 100. If I send a transaction with an insert does is reserve the ID 101 and loose it if the transaction fails with rollback, meaning next ID would be 102? Or will it take the ID 101 only if the transaction commits successfully? thanks
Not important. The point with autoincrementation is that your key field has unique values, and gaps in the sequence does not matter. And if it matters, you should probably rethink your design.
People say nothing is impossible, but I do nothing every day.
-
Not important. The point with autoincrementation is that your key field has unique values, and gaps in the sequence does not matter. And if it matters, you should probably rethink your design.
People say nothing is impossible, but I do nothing every day.
Ok so you don't know. Anyone else?
-
I'm trying to find the answer to this question. Let's say you have a MSSQL database which has a table with an auto-incremented field. If I have a Sql transaction sent to the server (through code) that does an insert into that table and the transaction fails (and does a rollback) will the current ID be lost or not? When does it generate the new ID? At transaction start or finish? Ex: table1 has column ID as auto-increment. the current ID is 100. If I send a transaction with an insert does is reserve the ID 101 and loose it if the transaction fails with rollback, meaning next ID would be 102? Or will it take the ID 101 only if the transaction commits successfully? thanks
-
I'm trying to find the answer to this question. Let's say you have a MSSQL database which has a table with an auto-incremented field. If I have a Sql transaction sent to the server (through code) that does an insert into that table and the transaction fails (and does a rollback) will the current ID be lost or not? When does it generate the new ID? At transaction start or finish? Ex: table1 has column ID as auto-increment. the current ID is 100. If I send a transaction with an insert does is reserve the ID 101 and loose it if the transaction fails with rollback, meaning next ID would be 102? Or will it take the ID 101 only if the transaction commits successfully? thanks
Identity (auto-increment) is not part of the transaction, so isn't rolled back. In your example, the ID for the next call would be 102.
-
Ok so you don't know. Anyone else?
piticcotoc wrote:
Ok so you don't know. Anyone else?
I believe he was trying to tell you that the answer to your question is useless information. Even if tests provided it one way, you could not rely on it to react the same between different database technologies or even between different versions of the same databases. It is not defined anywhere that I know of.
-
Ok so you don't know. Anyone else?
That's pretty rude: he gave you an answer.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
Identity (auto-increment) is not part of the transaction, so isn't rolled back. In your example, the ID for the next call would be 102.
Thanks guys. Exactly what I needed to find out.
-
That's pretty rude: he gave you an answer.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
Rude? If I didn't need the answer I wouldn't have asked the question.
-
The new IDENTITY value is generated when a row insert is initiated and will update the table's current identity value even when the transaction is rolled back. In simple terms, it would behave as if no rollback happened.
Thanks guys. Exactly what I needed to find out.
-
Rude? If I didn't need the answer I wouldn't have asked the question.
Maybe we can chalk this down to a lost in translation but what Jorgen said is correct. Identity columns allow you to assign a unqiue value to each row. It doesn't get reset to the previous value if you rollback the transaction. If you require no gaps in your keys then you will need to think of a different approach.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
-
Rude? If I didn't need the answer I wouldn't have asked the question.
Now you're just being a twat. He gave you an answer. You should have just moved on. What's the incentive for anyone else to help you in the future if they think you'll just be rude if you don't like the answer.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
Ok so you don't know. Anyone else?
I do know, and I did tell you to rethink your design. If you had used Oracle it would have been much worse, Oracle cache the sequences, so if you restart the server you can get a gap of twenty numbers. and if you have a cluster server they won't even be in order. (<for the nitpicks> I know it's a setting, but if you want higher performance you don't change it</for the nitpicks>)
People say nothing is impossible, but I do nothing every day.
-
Rude? If I didn't need the answer I wouldn't have asked the question.
Contrarywise; you don't need to know and yet you asked anyway.
-
I'm trying to find the answer to this question. Let's say you have a MSSQL database which has a table with an auto-incremented field. If I have a Sql transaction sent to the server (through code) that does an insert into that table and the transaction fails (and does a rollback) will the current ID be lost or not? When does it generate the new ID? At transaction start or finish? Ex: table1 has column ID as auto-increment. the current ID is 100. If I send a transaction with an insert does is reserve the ID 101 and loose it if the transaction fails with rollback, meaning next ID would be 102? Or will it take the ID 101 only if the transaction commits successfully? thanks
While Shameel and Russel gave you answers it is not the solution, if you are relying on the identity filed to give you sequential information (or any information) your design is WRONG. An ID field has only 1 function, to identify the record.
Never underestimate the power of human stupidity RAH
-
Rude? If I didn't need the answer I wouldn't have asked the question.
-
Now you're just being a twat. He gave you an answer. You should have just moved on. What's the incentive for anyone else to help you in the future if they think you'll just be rude if you don't like the answer.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
I did move on. Until someone gave an answer to my question which was all i needed to know. Why do some people think that a question must always be answered with with an assumption that everything you do is wrong and you should change your thinking according to theirs? Did any of you even bothered to ask why I needed the answer to that question? (except the 2 straight to the point answers - thanks again). No you didn't. You just assumed it is something vital to what I do and you advised me to change my logic. So to sum it up and end this hopefully: - i got the answer I needed which is great - i was looking for an answer and not a solution - the mayans were wrong about the world end (along with whoever misunderstood what they were trying to say) - check above fact and see if you find any analogy with this now turning into a flame thread Thanks
-
Contrarywise; you don't need to know and yet you asked anyway.
Mind-reading is a great feature to have. This way you know what I want better than me... oh wait; you don't and yet you assumed otherwise anyway
-
I did move on. Until someone gave an answer to my question which was all i needed to know. Why do some people think that a question must always be answered with with an assumption that everything you do is wrong and you should change your thinking according to theirs? Did any of you even bothered to ask why I needed the answer to that question? (except the 2 straight to the point answers - thanks again). No you didn't. You just assumed it is something vital to what I do and you advised me to change my logic. So to sum it up and end this hopefully: - i got the answer I needed which is great - i was looking for an answer and not a solution - the mayans were wrong about the world end (along with whoever misunderstood what they were trying to say) - check above fact and see if you find any analogy with this now turning into a flame thread Thanks
piticcotoc wrote:
Why do some people think that a question must always be answered with with an assumption that everything you do is wrong
..because we never use the value of an identity field; it does not (EVER) hold information. Asking what the number is indicates that you might indeed have a design-problem, and that might lead to problems in the long run. It's called free advice, and sometimes comes unsollicited.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
piticcotoc wrote:
Why do some people think that a question must always be answered with with an assumption that everything you do is wrong
..because we never use the value of an identity field; it does not (EVER) hold information. Asking what the number is indicates that you might indeed have a design-problem, and that might lead to problems in the long run. It's called free advice, and sometimes comes unsollicited.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
:wtf:
Quote:
It's called free advice, and sometimes comes unsollicited.
So do STDs but that doesn't mean I need them