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?


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
    , LastName varchar(50) NOT NULL
    , FirstName varchar(50) NOT NULL
    , EMailAddress varchar(255) NOT NULL
    , RowVersion rowversion

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

    RAISERROR ('Customer was updated or deleted by another user', 16, 1);

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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