What is the Safest Way to Add to Field?

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

Jonathan Wood

I have a numeric field. Users will modify it by adding a value to it.

But I'm a little concerned about the unlikely event where two users are
incrementing it at the same time. Unless there is some sort of lock between
the time the old value is read and the sum of the two values is written, one
change could override another.

Any suggestions?

Thanks.
 
Assuming you are using some sort of database, mark the transaction as
serializable and include a WHERE clause with the old value.
 
Jonathan,

I don't understand how this particular example would need to be handled any
differently than your normal concurrency handling.

Kerry Moorman
 
If I want to set a field to a specific value, I can just set it. If it
overrides another update, that's fine because someone set the value after
someone else.

On the other hand, if I want to increment a value, say, 10, it's possible
that two users read the old value, the first one writes an 11 and then the
second one writes an 11. In this case, the second update was not an
increment, as expected.
 
Yes, of course it would just fail then if the WHERE didn't match.

Could I trouble you to explain the advantage of marking the transaction as
serializable?

Thanks.
 
Jonathan,

So you are saying that you generally handle concurrency with the "last in
wins" strategy?

Kerry Moorman
 
Kerry,
So you are saying that you generally handle concurrency with the "last in
wins" strategy?
Click to expand...

Why not? If someone wants to set a field to one value, and someone later
wants to set it to another (even if only milliseconds later), why should I
prevent the field from being changed just because it had been changed
earlier?

Jonathan
 
Jonathan,

The safest, create an extra (child) table where in you keep the mutations.
The sum of those is than the value of the field.
AFAIK you have In fact than never a concurrency problem because it are all
new inserted rows.

Cor
 
Jonathan,

Of course "last in wins" is a valid strategy for some applications. I am
always interested in understanding just how that strategy is actually used.

When a user retrieves a row from a database table and changes one or two
columns of data in that row, does your app track which columns have been
changed and only update those columns in that row in the table? Or does your
app, in the process of updating the row in the table, re-write the unchanged
columns to the table in addition to updating the changed columns?

Kerry Moorman
 
Yes, I agree that would be the safest way, at the expense of some
efficiency.

Thanks.
 
Kerry,
Of course "last in wins" is a valid strategy for some applications. I am
always interested in understanding just how that strategy is actually
used.
Click to expand...

Well, isn't the alternative that "first in wins?" For me, I don't want to
prevent an update or raise an error unless I have a specific reason for
doing so.
When a user retrieves a row from a database table and changes one or two
columns of data in that row, does your app track which columns have been
changed and only update those columns in that row in the table? Or does
your
app, in the process of updating the row in the table, re-write the
unchanged
columns to the table in addition to updating the changed columns?
Click to expand...

Well, for the issue that raised this question, I'm only updating a single
field.

For cases where I'm updating entire records, I can see it's a little more
difficult. I'd need to think about it when I get there as I'm pretty new to
this.
 
Jonathan,

Be aware that this does not safe you for fraud by instance by banking where
money is automaticly payed out, in that case you would have first to check
if not somebody has in the mean time not empyed his account twice before you
pay out. (Or to lock the transaction).

However I understood from the other conversation that the meaning of safe
was concurrency.

Cor
 
I was looking into serializable transactions but ran across a simpler syntax
for this. Does anyone know what the concurrency issues would be with the
following?

ALTER 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

Thanks!
 
Back
Top