Concurrency Question

[Jonathan Wood]

I posted earlier about the best way to add to a field value, and preventing
situations where the value could change between the time I read it and the
time I write the modified value. Some suggestions included using a
serializable transaction, and using the WHERE statement, which makes sense.

However, while looking into this, I came up with another, simpler syntax,
that I wonder if it eliminates concurrency issues. It looks like this:

CREATE PROCEDURE dbo.mc_Trainers_AddCredits
@TrainerId uniqueidentifier,
@Credits int
AS
BEGIN
SET NOCOUNT ON
UPDATE mc_Trainers SET Credits=(Credits+@Credits) WHERE UserID=@TrainerId
RETURN
END

Can anyone tell me if updating a field in a single UPDATE statement this way
eliminates concurrency issues?

Thanks.

 

[JIGNESH]

I don;t think that eliminates concurrency issues.

By the time your update is fired Credits value can change by another user
and you update will never be able to know that.

Regards
JIGNESH.

 

[Jonathan Wood]

Thanks. There's nothing to prevent credits being added by different users
and that's okay. The important thing is that it doesn't change between the
time I read the original value and the time I write the updated value.

For example, if the value is 10 and I add 5, the result must be 15. If I
read the value 10, and another users adds 5, and then I write 15, we just
lost 5.

 

[Cowboy \(Gregory A. Beamer\)]

Concurrency is about two people altering the same data at once. If two
pepole can open the record at the same time, change data and then update,
you have a concurrency issue. This is true whether you are changing one
field or many.

The harder the lock you use, the less likely you have concurrency, but the
more likely you make data unavailable. In most apps, I tend to be optimistic
and then handle exceptions on postback.Then again, most apps do not end up
with multiple people editing the same data at the same time. I would still
code a concurrency check, as it is good practice.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************

 

[Jonathan Wood]

The question for me is if the single line UPDATE command is atomic. That is,
could the result be unexpected if two users executed it at once. Put another
way, does that UPDATE statement I have eliminate the possibility that the
Credits field will be changed between the time it is read and the time it is
written.

And I'm open to any suggestions as to what kind of "concurrency check" I
might code. Note that I don't see any problem if two users update the field
almost at the same time, but in both cases the expected number is added to
the field.

Thanks.