How SQL Server handles UPDATE transactions?

Question

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?

You should really leave it up to SQL Server to handle consecutive numbers, by using the `INT IDENTITY` column type. **Don't try to do this on your own!** there are soooooooooo many ways to fail .... — marc_s, Dec 05 '12 at 10:08
Dare I ask why you're trying to re-invent `IDENTITY` columns? Plus why would you keep a table of the current values centrally? Waste of time, probably wrong, and less accurate than querying the table directly. — Bridge, Dec 05 '12 at 10:10
The database is more than 12 years old and i am not allowed to change the old logics!! — Yaqub Ahmad, Dec 05 '12 at 10:14

score 1 · Accepted Answer · answered Dec 05 '12 at 10:13

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.

How SQL Server handles UPDATE transactions?

1 Answers1

Linked