-2

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?

closed as unclear what you're asking by ypercubeᵀᴹ, Erik Darling, LowlyDBA, Philᵀᴹ, Gaius Feb 3 at 14:32

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

3

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
0

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.

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