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.

May be it was just a typo, but unit tests should not hit the database. You were probably talking about an integration (or end-to-end) test. — Arialdo Martini, Apr 11 '12 at 08:55

score 7 · Accepted Answer · answered Jun 06 '11 at 21:29

7

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.

answered Jun 06 '11 at 21:29

Michael Petito

12,891
4
40
54

OK, fair enough. I can easily run the `DBCC` command as I mentioned earlier. Cheers for that. – Jason Evans Jun 06 '11 at 21:32

score 2 · Answer 2 · edited May 23 '17 at 12:07

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

For example

You start a transaction
You insert a record in table t: the identity field is set to 10
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
John commits his transaction. A record with id = 11 is stored
You rollback your transaction
You reseed the identity to the previous value, that is 9
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

Using TransactionScope does not reseed identity column

2 Answers2