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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

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" wrote:

> 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.
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>
>

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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com


"JIGNESH" <(E-Mail Removed)> wrote in message
news:1632C7D1-D949-4D24-9643-(E-Mail Removed)...
>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" wrote:
>
>> 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.
>>
>> --
>> Jonathan Wood
>> SoftCircuits Programming
>> http://www.softcircuits.com
>>
>>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>

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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"Cowboy (Gregory A. Beamer)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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.
>>
>> --
>> Jonathan Wood
>> SoftCircuits Programming
>> http://www.softcircuits.com
>>
>
>