SQL Server – Generating PK Value Programmatically in Multi-Threaded Applications

primary-keysql serversql-server-2008sql-server-2008-r2

Suppose We create a table, with identity disabled:

CREATE TABLE TestIdentity (
    Id    int NOT NULL,
    abc   varchar(10) NULL,
    CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);

Note : I have no rights to make Primary Key to identity-enabled, so I have to calculate next Id value from the application programme.

Suppose from multiple connections data is inserted in this table, Id field value is generated by C# application code, the problem is in multi-threaded application
multiple requests might generate the same id to insert row. In that case SQL server, PK constraint violates.

What is the best solution to reduce PK constraint violation?

Best Answer

You could try this technique will single thread the inserts into this table, but should guarantee uniqueness. Your C# code would need to do the following in a transaction. Select the current max Id and hold the lock so other users will wait. Add +1 to the Id and insert that row. Commit the transaction

BEGIN TRANSACTION
select @Id = max(Id) from TestIdentity WITH (TABLOCKX,HOLDLOCK)
set @Id += 1
insert into TestIdentity... new row...
COMMIT
Related Question