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