What causes lost updates being observed in my SQL Azure database? I have a SQL Azure database with the following tables:

CREATE TABLE [dbo].[UserBalances]
  (
     [UserId]         UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(),
     [AvailableMoney] INT NOT NULL DEFAULT(0)
  );

GO

CREATE UNIQUE CLUSTERED INDEX [UserBalancesIndex]
  ON [dbo].[UserBalances]([UserId] ASC);

GO

CREATE TABLE [dbo].[UserBalanceChanges]
  (
     [EntryId]      UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
     [UserId]       UNIQUEIDENTIFIER NOT NULL,
     [PriorBalance] INT NOT NULL,
     [NewBalance]   INT NOT NULL,
     [Time]         DATETIME DEFAULT(GETUTCDATE())
  )

GO

CREATE CLUSTERED INDEX UserBalanceChangesIndex
  ON [UserBalanceChanges]( EntryId )

GO 


and the following code being concurrently executed via several connections the database:

CREATE PROCEDURE [dbo].[usp_ChangeUserBalance] @userId UNIQUEIDENTIFIER,
                                               @change INT
AS
    BEGIN TRANSACTION

    DECLARE @priorBalance INT;
    DECLARE @newBalance INT;
    DECLARE @updateTime DATETIME;

    UPDATE dbo.UserBalances
    SET    @updateTime = GETUTCDATE(),
           @priorBalance = AvailableMoney,
           @newBalance = AvailableMoney = AvailableMoney + @change
    WHERE  UserId = @userId;

    INSERT INTO UserBalanceChanges
                (UserId,
                 PriorBalance,
                 NewBalance,
                 Time)
    VALUES     ( @userId,
                 @priorBalance,
                 @newBalance,
                 @updateTime );

    COMMIT TRANSACTION

    RETURN 0 


and then I run the following query:

SELECT TOP(1000) PriorBalance,
                 NewBalance
FROM   UserBalanceChanges
WHERE  UserId = SomeSpecificId
ORDER  BY Time DESC 


and I often see something like this:

1000 995
1005 1000 &lt;&lt;identical
1005 1000 &lt;&lt;changes
1010 1005
1015 1010


which looks like the two concurrently running updates read the same initial value and then one update is effectively lost.

Lost updates are a known anomaly but lost updates are impossible in SQL Server and presumably in SQL Azure too (at least with a single "SELECT from UPDATE" statement that I have.

Yet it looks like I actually see lost updates.

Why do I observe "impossible" lost updates?
The problem was because of "other interpretation" lost update - when data is first read into local memory and then the table is updated with that data. There was another code that was intended for resetting balances of some users very rarely. It was something like this:

DECLARE @BalancesToReset TABLE
(
   UserId uniqueidentifier,
   PriorBalance int,
   NewBalance int
)

INSERT INTO @BalancesToReset( UserId, PriorBalance, NewBalance )
    SELECT UserId, AvailableMoney, dbo.ufn_ComputeNewBalance(some params)
    FROM UserBalances WHERE SomeIncorrectConditionHere

UPDATE UserBalances SET 
    AvailableMoney = BR.NewBalance FROM
       @BalancesToReset BR INNER JOIN UserBalances
       ON UserBalances.UserId = BR.UserId;

 INSERT INTO UserBalanceChanges( PriorBalance, NewBalance )
    SELECT PriorBalance, NewBalance
    FROM @BalancesToReset WHERE NewBalance &lt; PriorBalance;


and this code would run concurrently with the code in question. The code was intended to INSERT from SELECT only a small fraction of user balances and only occasionally but because of an incorrect condition in the SELECT part it would select a huge portion of balances and very often. The balances that were unintentionally selected had NewBalance equal to PriorBalance and so the final INSERT from SELECT would not log the changes.

The UPDATE in the middle of this code would forcibly reset the AvailableMoney for many users to a precomputed value and this would introduce a "second interpretation" lost update which is possible in SQL Server.