Home T-SQL function not thread safe after UPDLOCK
Reply: 0

T-SQL function not thread safe after UPDLOCK

user1533
1#
user1533 Published in June 23, 2018, 5:31 pm

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!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310886 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO