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!
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.
链接地址: http://www.djcxy.com/p/32846.html