Using TransactionScope does not reseed identity column

Question

I've started using TransactionScope to help with my unit tests, in order to put my test database back to it's previous state. Using this with SpecFlow, I have a base class like so:

public class TransactionScopedFeature
{
    private TransactionScope Scope { get; set; }

    [BeforeScenario]
    public void BaseSetup()
    {
        this.Scope = new TransactionScope(TransactionScopeOption.RequiresNew);
    }

    [AfterScenario]
    public void BaseCleanup()
    {
        if (this.Scope != null)
        {
            this.Scope.Dispose();
        }
    }
}

All of the above works, in that when I add records to the database, when I then query the tables after the tests have finished, those tables are empty. Great stuff, and very clever indeed!

My question relates to identity columns in these tables. What I've noticed is that, when I run my tests numerous times, the ID column of my test table increases by 1. I assumed that since the TransactionScope would rollback the changes, that the identity seed would also be rolled back.

Am I wrong in making this assumption - is this just how databases work? If that is the case, I could also run a SQL script before each of my scenarios that does this:

DBCC CHECKIDENT ('dbo.Items', reseed, 0)

I just wanted to check in case I was doing something wrong, or this is normal database behaviour.

Cheers. Jas.

Answer 1

The seed value for an identity column does not get rolled back with the rest of a transaction in SQL Server.

This is by design so that an exclusive lock does not have to be placed on the counter for the identity for the entire duration of the transaction.

Answer 2

Reseeding the identity after a rollback can be very dangerous: should another transaction insert a record, an identity collision would happen.

For example

1. You start a transaction
2. You insert a record in table t: the identity field is set to 10
3. John, in another concurrent client insert a record in table t. Since the first transaction has not committed nor rollback, the identity field is set to 11 for John's transaction
4. John commits his transaction. A record with id = 11 is stored
5. You rollback your transaction
6. You reseed the identity to the previous value, that is 9
7. You insert 2 new records. The second one will have the id = 11, with a consiquent identity collision

This may happen especially if your unit integration tests run in parallel (this is a very common behavior with NCrunch).

Rule of thumb: don't reseed after a rollback

Also, see the reply marc_s gave to this question