How to reset Autoincremented Id when rollback occurs in sql

Question

When I try to insert a new row in the table, if there is any exception occurs in this transaction then data is rollback.

Now when a new entry is succesfully inserted next time, AutoIncrement id is updated with next value. Means there is Gap between two consequetive Unique Id in the table.

Is there any valid way to overcome this problem?

Thanks in advance

Answer 1

The answer has to be said - no.

The whole idea of IDENTITY columns is to not be meaningful, and to be transaction agnostic - so that the numbers can be dished out without care of other transactions rolling back or not. Imagine a 1000 insert per second system being held up for 10ms for each transaction (insert) to decide whether it will commit! (fyi 10ms * 100 = 1s)

Note: In SQL Server 2012 (latest SP/patch level at time of writing), there is a "feature" noted here on Connect related to identities.

Also even prior to 2012, you don't even need to rollback to consume an IDENTITY value - see here https://stackoverflow.com/a/16156419/573261

---

This applies to other major RDBMS as well for the same reasons, e.g.

PostgreSQL sequences

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

^{(emphasis mine)}