SQL Identity (autonumber) is added even with transaction rollback

Question

I have a .net transaction with inserting SQL into a SQL Server 2005 database. The table has a primary authentication key.

When an error occurs in a transaction, Rollback() called. Row inserts are returned correctly, however the next time I insert data into a table, the identifier is incremented, as if rollback never occurred. Thus, essentially there are gaps in the sequence of identity. Is there a way for the Rollback() method to return a missing identifier?

I do not approach this correctly?

Answer 1

If you think about it, the auto-increment number should not be transactional. If other transactions had to wait for the automatic number or rollback to be used, they would be blocked by the existing transaction using the automatic number. For example, consider my psuedo code below with table A using the 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 the transaction of user 2 begins with a millisecond after user 1, then inserting them into table A will have to wait until the completion of the entire transaction of user 1 to see if the automatic number from the first insert in A. was used.

This is a function, not an error. I would recommend using a different scheme to generate automatic numbers if you need them to be tightly sequential.

Answer 2

If you depend on the fact that your credentials are gapless, then yes - you are doing it wrong. The whole point of a surrogate key is to not have a business sense.

And no, there is no way to change this behavior (without waiting for the jump of your own auto-increment and suffering from the performance consequences of blocking other inserts).

Answer 3

You get spaces in your sequence if you DELETE too.

Sequences must be unique, but they do not have to be sequential. The fact that they are monotonously increasing is just a coincidence of implementation.

Answer 4

As far as I know, rows to insert require autonomy and rollback that number is lost forever. If you depend on the fact that this offline number is in sequence, you can consider the approach you are using.

Answer 5

I do not think that there is a requirement that keys with autostart are sequential. In fact, I do not think they may be necessary:

• transaction a starts and inserts
• transaction b starts and inserts

• transaction cancels

  you will get a hole. nothing to do.

Answer 6

All the other posters that say they don’t worry about it, and that you should get spaces, are right. If business value matters, and this value does not exaggerate spaces, then do not use the identifier column.

FYI, if for any reason you want to remove spaces, most databases have a way to reconfigure automatic numbering to the number of your choice. This is a pain in the ass, and if you need to do this regularly, you should definitely not use the autonumber / identity field as described above. But here is the code for this in the SQL server:

DBCC CHECKIDENT ("Product", RESEED, 0)

This establishes that the product table starts with 1 (although if you have entries in the table, it will obviously skip the identification values ​​already made.) Other RDBMS manufacturers have their own syntax, but the effect is about the same, so look "reseed identity" or "resed autonumber" in system help files or intervals.

Again: this is for special occasions and not for regular use. Do not put it in a stored procedure and force us all to come.

Answer 7

Muhan is trying to think about this in the context of many concurrent connections performing this transaction, and not one at a time. Some will fail, and some will succeed. You want SQL Server to focus on starting new queries as they arrive, rather than keeping the identity column without spaces. IMO this (gaps in values) is definitely not worth the time.

Answer 8

No. Sequence applications use an autonomous transaction. In Oracle, an autonomous transaction was once internal to dbms, but now appears for your own use (and is often used incorrectly)

 PRAGMA AUTONOMOUS_TRANSACTION;'