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

T-SQL function not thread safe after UPDLOCK

Moose
1#
Moose Published in 2015-10-05 21:21:22Z

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!

M.Ali
2#
M.Ali Reply to 2015-10-05 21:47:25Z

Another way would be to slightly use a different version of an update query and obviously warp the whole thing into a transaction.

CREATE PROCEDURE getNextID
     @NextNumber INT          OUTPUT
    ,@id_type    VARCHAR(20)
AS
BEGIN
  SET NOCOUNT ON;

    DECLARE @NextValue TABLE (NextNumber int);

BEGIN TRANSACTION;

    UPDATE its_id_sequence
      SET last_used_number = ISNULL(@NextNumber, 0) + 1
     OUTPUT inserted.last_used_number INTO @NextValue(NextNumber)
    WHERE id_type = @id_type

    SELECT @NextNumber = NextNumber FROM @NextValue

COMMIT TRANSACTION;

END

So you update first without reading the value, and once updated then get the value and use it.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO