Column increment not generating unique values in MS SQL

Hi!,

Sometimes there is a need to increment a column value for generating unique ids. People usually increment the value by one to update the column's value, But sometimes the same value is generated as the requests come at same time and query executes for the request at the same time which results in duplicate values. Here is the solution for the problem

DECLARE @IncrementId AS INT

BEGIN TRANSACTION

SELECT @IncrementId = LastId + 1 FROM table_name WITH (TABLOCKX)

Commit Transaction

END

TABLOCKX acquires exclusive lock on the table it also restricts the deadlocks on the table, There is also TABLOCK which acquires "shared" lock on the table.