SQL Server SCOPE_IDENTITY() - thread safety

Question

We have to modify our database and manage the IDENTITY column ourselves (rather than relying on an auto-inc field).

Our solution is to have a "Generator" table, which has an auto-inc field. We insert to this table and then read the SCOPE_IDENTITY value to get our new ID, e.g.

insert into NewIDEntity
        (CreationDate)
        select
        GetDate()

select @EntityID = SCOPE_IDENTITY()

We are mainly concerned with the following scenario:

• Transaction 1 INSERTS into NewID and receives 101 as the new ID to insert into the Entity table.
• Before it can commit, Transaction 2 INSERTS into NEWID and also receives 101 as the new ID (this is because SCOPE_IDENTITY() will return the ID within the context of the current scope. The original row has not been committed yet so we would expect the value to be 101)

• Transaction 1 commits and the row is written.
• Transaction 2 attempts to commit but 101 has already been written and results in a Primary Key violation and aborts the transaction.

However on running this it seems that SCOPE_IDENTITY() is handled by SQL Server, even if running under READ UNCOMMITTED isolation level we get no conflicts.

Is this okay or are there gotchas that we haven't been able to find?

Thanks Duncan

Answer 1

Identity generation is essentially outside the scope of any transaction- the ID counter is always bumped as soon as an ID is generated, not just when the transaction is committed. This leads to identity sequences having gaps when transactions roll back, but that's because it is guaranteeing safety in exactly the sort of situation you describe.

Answer 2

I agree with Araqnid's answer, but just as a finer point, unless you are wanting to keep a history of generated ids, then you can also delete them when you are done. We also have a business requirement of generating IDs in a particular way (prefixed with other values) and the way I do this is by having a table that only has the identity field in it. Then my code looks like this:

/*
 * There is no "user data" being added to the table, so we just use
 * "Default Values".
 */
Insert  Into dbo.MasterIds Default Values

Set @MasterId = Scope_Identity()

/*
 * We don't keep just serial numbers on this table. Now that we have
 * the new MasterId delete it from the table.
 */
Delete  From dbo.MasterIds
Where   MasterId = @MasterId

Select  @MasterId As MasterId

Hope that helps.