SE Anon or Wiki user (imported from SE)
Our company would like to understand the effects:
- If we have single table with Primary Key Identity column, `int primary key IDENTITY(1,1)`,
- and the Application is conducting multi-threading parallel processing, trying to conducts 1000s of simultaneous inserts on one table.
Would the identity primary key column fail, with a primary key violation in any way, for example: processors are trying to input the same identity number?
Or was SQL designed to be successful even in multithreading parallel insert?
Note: `IDENTITY_INSERT` is `OFF`
>Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number?
This is covered in the SQL Server product documentation:
>[**`CREATE TABLE (Transact-SQL) IDENTITY (Property)`**][1]
>
>Identity columns can be used for generating key values. The identity property on a column guarantees the following:
>
>* Each new value is generated based on the current seed & increment.
>* Each new value for a particular transaction is different from other concurrent transactions on the table.
The second point is the guarantee you need.
It is valid no matter how many user processes or threads access SQL Server concurrently.
To state it explicitly: yes, identity allocation is thread-safe.
---
A call to `CMEDSeqGen::GenerateNewValue` allocates the next value to a caller, and is protected by a latch (a lightweight synchronization object). It is not affected by user transactions, which means you can end up with gaps in case of rollbacks. Concurrent transactions can generate identity (and sequence) values without having to wait on a lock.
For more details, see my answer to a related question:
@@@ answer 1501
[1]: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property