SQLServer when is UPDLOCK Applied in Select

Question

I am issuing the following query with an UPDLOCK applied:

select @local_var = Column
from table (UPDLOCK)
where OtherColumn = @parameter

What happens is that multiple connections hit this routine which is used inside a stored procedure to compute a unique id. Once the lock acquires we compute the next id, update the value in the row and commit. This is done because the client has a specific formatting requirement for certain Object ID's in their system.

The UPDLOCK locks the correct row and blocks the other processes, but every now and then we get a duplicate id. It seems the local variable is given the current value before the row is locked. I had assumed that the lock would be obtained before the select portion of the statement was processed.

I am using SQLServer 2012 and the isolation level is set to read committed.

If there is other information required, just let me know. Or if I am doing something obviously stupid, that information is also welcome.

Answer 1

From the SQL Server documentation on UPDLOCK:

Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.

That means that other processes can still read the values.

Try using XLOCK instead, that will lock other reads out as well.