Home T-SQL function not thread safe after UPDLOCK
 I have a simple getNextID procedure that retrieves an id value in a table and increments the value by 1. It was built with some multi threading in mind, but it appears that the UPDLOCK in the procedure doesn't actually make it thread safe as was intended and I'm trying to understand why. The idea was that the UPDLOCK during the initial select would prevent any other threads from performing that select until the update at the bottom of the procedure was complete; however, that doesn't appear to be the case since I'm getting duplicate values when two threads fire at the same time. After reading a few other threads, I think what might be happening is the UPDLOCK is preventing other thread from updating the row, but it isn't preventing them from performing the initial select before the update. So both threads are performing the same select (retrieving the same value), then thread 2 is waiting for thread 1 to update and then thread 2 updates the row to the same value. Am I understanding what the lock is doing correctly? Would the proper way to accomplish the thread-proofing be to wrap it all in a BEGIN/COMMIT TRANSACTION?  CREATE PROCEDURE getNextID ( @NextNumber int OUTPUT ,@id_type VARCHAR(20) ) AS BEGIN SELECT @NextNumber = (last_used_number + 1) FROM its_id_sequence WITH (UPDLOCK) WHERE id_type = @id_type UPDATE its_id_sequence SET last_used_number = @NextNumber WHERE id_type = @id_type END  Thanks!