0

If I have concurrent transactions both using read committed isolation level and body of the transaction look like this:

DECLARE @value..

SELECT @value = balance
FROM MyTable
WHERE Account = 1

UPDATE MyTable
SET balance = @value+@pAmount
WHERE Account = 1 AND Balance = @value

IF @@COUNT == 0 
    THROW 'another transaction changed'

When UPDATE's WHERE reads Balance column, is it guaranteed to read committed or does it read dirty write of another open transaction? In other words, can I detect lost update using READ COMMITTED and checking if my update had any effect or not.

user21479
  • 1,179
  • 2
  • 13
  • 21

1 Answers1

1

Since @value seems to be a scalar variable, I reckon that you can do your entire logic with just a single update:

UPDATE MyTable
SET balance = balance + 1
WHERE Account = 1;

The reason for that is that the @value should always just be the current balance of the first account.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • thanks, i updated the example. instead of always adding 1, different transaction could add different amount. but even in this case, i think the answer is the same, i could use single update. thanks – user21479 Mar 05 '19 at 05:11