Search code examples
sql-servertransaction-isolation

SQL Server: atomic update in read committed, can detect concurrent write?

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.

Solution

  • 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.