Suppose I have ID column on a table and the max ID value is 100. Then I insert 50 row in this table in a transaction, the max ID will be 150. but the the transaction is rolled back.but the Next ID value is 151,not 101. It means Max ID value not rolled back when when roll back a transaction. How to resolve this problem?
Asked
Active
Viewed 3,974 times
1
-
In what way is it a problem? – Dan Bracuk Apr 22 '13 at 19:29
-
3And it should not be rolled back. Why would you care? Identity values are presumed to have gaps by definition. If you don't want gaps you cannot use identities. However most of the time, the perceived need for no gaps is not a real requirement just a flawed assumption. – HLGEM Apr 22 '13 at 19:40
1 Answers
2
This is how the IDENTITY column works, and it is very much by design. The reason is that the values are handed out but not rolled back to improve efficiency. If you had to lock the entire table for every transaction that assigned an identity value, you'd absolutely kill concurrency and performance. In all honesty, you should not care about gaps because you can't prevent them with an IDENTITY column. There are workarounds but they also kill concurrency and performance.
Aaron Bertrand
- 272,866
- 37
- 466
- 490