SQL Server Identity column FAQs

Q. How is identity values generated? Does SQL Server internally use lock as synchronization mechanism?

A. Identity values are generated in-memory using light-weight synchronization, i.e. Spinlock (roughly speaking, Interlocked* function with yield).

When an identity-value is either generated or claimed for use by DML (insert), a log record is used to track that value (OR one greater than it) has been used, before the use of the value is committed. Some exception includes BCP (bulk insert), which gets or uses these values without logging but is then responsible to do a logged value-add (into identity manager) before the BCP is committed. These log records are used during recover to recover the greatest value given out for the sequence.

Q. I understand that Identity value generation does not incur lock by SQL Server, how come I experience lock contention between concurrent inserters?

A. Quite often, the lock contention is Storage Engine page latch/lock contention. Suppose session A and session B are both inserting at the same time to the same identity column, and the identity column is primary or unique key, i.e. an index is built on it. Both sessions will get nearly the same identity values. Therefore they will probably write access the same page in the index. That means there will be contention, and that is natural. There are a couple of ways to handle this. Following list some simple approaches.

1. Only one inserter. The monotonic sequencing of identity value is beneficial. The last index page is likely to stay in Buffer pool, so transaction will be faster.

2. Introduce unique value (or random number generator) that will be specific to a session, such as user name, node id, etc. Concatenate this with the identity value as the index key.

E.g.

Create table table_foo(

               order_id integer identity,

               session_id integer,

               primary key (session_id, order_id))

I also see some customers using identity column to simulate standard Sequence object feature, where a table is used to generate sequence values. Note row-level lock on inserted/deleted rows is incurred here, and possibly page lock (if index is added on the identity column without much thought).