Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

I have a .net transaction with a SQL insert to a SQL Server 2005 database. The table has an identity primary key.

When an error occurs within the transaction, Rollback() is called. The row inserts are rolled back correctly, however the next time I insert data to the table, the identity is incremented as if the rollback never occurred. So essentially there are gaps in the identity sequence. Is there any way to have the Rollback() method reclaim the missing identity?

Am I not approaching this the right way?

like image 207
muhan Avatar asked Nov 11 '08 23:11

muhan


People also ask

Is identity column auto increment SQL Server?

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .

Does Raiserror rollback transaction?

Some errors automatically rollback a transaction, some don't. If you want to be sure, you have to use RAISERROR, or IF condition ROLLBACK TRAN.

What is the difference between identity and auto increment in SQL?

what is the difference between auto increment and identity? You could say that "Autoincrement" is the general term and "IDETINTY" is an implementation. In SQL 2012, there is a second implementation, sequences. (Which is in many regards is a better implementation.)

What is identity increment?

IDENTITY[(seed,increment)] In this syntax: The seed is the value of the first row loaded into the table. The increment is the incremental value added to the identity value of the previous row.


1 Answers

If you think about it, the auto-increment number should not be transactional. If other transactions had to wait to see if the auto-number was going to be used or "rolled back", they would be blocked by the existing transaction using the auto-number. For example, consider my psuedo code below with table A using an auto-number field for the ID column:

User 1 ------------ begin transaction insert into A ... insert into B ... update C ... insert into D ... commit   User 2 ----------- begin transaction insert into A ... insert into B ... commit 

If user 2's transaction starts a millisecond after user 1's, then their insert into table A would have to wait for user 1's entire transaction to complete just to see if the auto-number from the first insert into A was used.

This is a feature, not a bug. I would recommend using another scheme to generate auto-numbers if you need them to be tightly sequential.

like image 90
Jason Jackson Avatar answered Oct 09 '22 23:10

Jason Jackson