question

tobfuller avatar image
tobfuller asked

Can the sql scheduler yield in the middle of a session?

We have a stored procedure that is executed by multiple clients. The stored procedure grabs an integer value from a table, updates the table to increment the value, and then returns the originally grabbed value to the client - very straight forward. BEGIN TRAN SELECT @var = somevalue FROM TABLE WHERE [somecondition] UPDATE TABLE SET somevalue = somevalue + 1 WHERE [somecondition] RETURN @var COMMIT TRAN We had an issue a few days ago where two clients ended up with the same value from this proc. We're trying to figure out how this happened, and the only thing my simple mind can think of is that sql yielded right after the select @var=. At that same moment, another sql thread/scheduler called this procedure and ended up grabbing the same value. What do you all think? Am I way off on this? -Brian
sessions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
Probably no lock between the select and the update, so two things hit it at the same time. You should add a UPDLOCK hint to the SELECT query to prevent two processes from hitting it at the same time.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tobfuller avatar image tobfuller commented ·
We're thinking about swapping the update and the select. That way we'll obtain a lock on the table for the duration of the transaction - basically not allowing another call to the proc until the current one completes.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.