Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 <<identical
1005 1000 <<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?

share|improve this question
    
Could you give your table definition and explain the functional difference between "AvailableMoney" vs "PriorBalance and NewBalance"? –  Edward Dortland Jan 16 at 8:12
    
@EdwardDortland: I added the table definitions. The AvailableMoney thing is "current state" and the two others are there for historical purposes so that I can look back and find what the balance was at some specific time. –  sharptooth Jan 16 at 8:34
    
Not sure this is your issue but you seem to be relying on left to right order of variable assignment. Pretty sure this is not guaranteed. –  Martin Smith Jan 16 at 10:29
    
@MartinSmith: Yes, that's what I'm relying on. If this doesn't work how do I get the right "SELECT from UPDATE" effect? –  sharptooth Jan 16 at 10:30
    
@sharptooth - Does Azure support the OUTPUT clause? OUTPUT GETUTCDATE(), DELETED.AvailableMoney, INSERTED.AvailableMoney? Though I don't know that this is the reason anyway. –  Martin Smith Jan 16 at 10:33

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
Thanks for posting the update!. Be careful that since SQL Azure runs in read commited snapshot your SELECT UserId, AvailableMoney, dbo.ufn_ComputeNewBalance(some params) FROM UserBalances WHERE SomeIncorrectConditionHere might select unintended 'old' data from UserBalances that is in the middle of a transaction from '[dbo].[usp_ChangeUserBalance]' –  Edward Dortland Jan 24 at 8:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.