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?