Rollback issue

[dave Beseke]

I have a C# application that creates a SqlConnection object with an ADO
connection string and then sets up a SQlTransaction object for that
connection. Several records are posted to different tables in the
database within this transaction. All these tables uses an identity
element as their primary key. If the transaction fails, the records are
rolled back, but the identity elements are not reset. In other words,
if the last good record had an identity of 1000, and I roll back a
record that was assigned 1001, the next good record end up with an
identity value of 1002. I have not been able to find information as to
whether this behavior is by design, but it seems to me that if I do a
rollback, these identity elements should also be rolled back. Is this
behavior a bug?

*** Sent via Developersdex http://www.developersdex.com ***

[Simon Hayes]

No - this is by design, to avoid processes blocking each other. It the
IDENTITY state was included in the transaction, then no other process
could get a new value until the first process committed. See also this
thread:

http://groups.google.ch/group/comp.d...448795a58d94b9

In general, you use IDENTITY where you need an artificial key and you
don't care what the key values are. If you need sequential values with
no gaps, or if you want the key values to have some meaning, then it
isn't a good solution.

If this isn't helpful, then you should probably give some more
information about exactly what you need to do, and why the IDENTITY
values matter to your application.

Simon