We have a table which is used to Generate Unique Numeric keys. These keys are then used as a PrimaryKey in other tables. Table structure is like this:
TableName VARCHAR
CurrentKey INT
So we have data in this table like
TableName Customers
CurrentKey 400
So when we need next primary key for table Customers
we get the CurrentKey
from this table where TableName
is Customers
, it will give us 400 we increment (400+1) it and we update this key in the table also. So our CurrentKey
is 401 now.
The sql we use for this purpose is:
SQL1:
DECLARE @CurrentKey INT
UPDATE myTable
SET @CurrentKey = CurrentKey = CurrentKey + 1
WHERE TableName = @TableName
My question is that Do we need to LOCK the table so that the keys may not duplicate if multiple users call this at the same time? i am sure that SQL Server will not allow duplicate data but i don't know HOW... Query with Table Lock:
SQL2
BEGIN TRANSACTION
DECLARE @CurrentKey INT
UPDATE myTable WITH (TABLOCKX)
SET @CurrentKey = CurrentKey = CurrentKey + 1
WHERE TableName = @TableName
END TRANSACTION
Can someone please explain how the SQL Server handles UPDATE
calls?
Each SQL statement runs in a transaction, and an update
statement always uses a lock to protect its update. SQL Server does not allow you to read a half-modified row (with some exceptions for data that is larger than 8k.)
Your first statement should be fine.