Are lost updates completely handled in SQL Server or is manual intervention still required?

All we need is an easy explanation of the problem, so here it is.

As lost updates occur when 2 different transaction try to update the same thing at the same time – different sessions update the same row; the last one overwrites the changes made by the first.

We can control locking via T-SQL commands, but cannot control the behavior of latching. Is the lost update problem completely handled by SQL Server in every case, or is it possible that in some cases consistency may be not preserved?

If not, what should be done to prevent the lost update occasion?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

One way to avoid the "lost update" problem, where changes are overwritten silently and the last one wins, is with an optimistic concurrency technique. This can be accomplished using a rowversion column and comparing the original and current value during each update. The update can be rejected if any of the row values changed or the row deleted.

CREATE TABLE dbo.Customer(
    CustomerID int NOT NULL IDENTITY
        CONSTRAINT PK_Customer PRIMARY KEY
    , LastName varchar(50) NOT NULL
    , FirstName varchar(50) NOT NULL
    , EMailAddress varchar(255) NOT NULL
    , RowVersion rowversion
);
GO

CREATE PROCEDURE dbo.UpdateCustomer
      @CustomerID int
    , @LastName varchar(50)
    , @FirstName varchar(50)
    , @EMailAddress varchar(255)
    , @OriginalRowVersion rowversion
AS
SET NOCOUNT ON;
UPDATE dbo.Customer
SET 
      LastName =  @LastName
    , FirstName = @FirstName
    , EMailAddress = @EMailAddress
WHERE
    CustomerID = @CustomerID
    AND RowVersion = @OriginalRowVersion;

IF @@ROWCOUNT = 0
BEGIN
    RAISERROR ('Customer was updated or deleted by another user', 16, 1);
END;
GO

SQL Server similarly performs optimistic concurrency checks in the SNAPSHOT transaction isolation level. When a row is modified in a SNAPSHOT session, the database engine internally checks the row’s current transaction sequence number to determine if it was modified outside the current snapshot transaction and raises error "Snapshot isolation transaction aborted due to update conflict" when the row has been changed or deleted. This provides the same protection against lost updates without the rowversion column, value check, and RAISERROR in the above example.

Method 2

Where you want to block the second update, because the first may render it inappropriate, then something like Dan’s suggestion (hold the value that is modified on every change like a Timestamp, or sysStartTime for a temporal table) is needed.

If you don’t want to block the second update because “newest wins” is OK overall but just need to preserve the first because it might contain useful history, then standard history recording methods should capture the data from the first update. This can be done in recent SQL Server versions with system managed temporal tables, in older SQL Server or other databases that don’t support temporal tables or an equivalent you can create your own variant using triggers.

If you might want to try merge the two updates this gets more complex and is very application specific.

Another complication is where an update contains one or more relative changes (SET someColumn = someColumn + 1) because even though the first update is “lost” it has an effect on the second that wins. With that sort of update you almost certainly want the detect-end-error method suggested by Dan.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply