Prevent lost edits with SQL Server rowversion

Two editors open the same product description. One fixes the price label and saves. The other corrects punctuation in an older copy and saves the entire form. Both requests succeed, but the second request removes the first change. A transaction around each individual update does not prevent this lost edit: the stale read happened before either transaction began.

Make the version part of the write

An edit request needs to say which version its author saw. A rowversion column supplies a database-generated, eight-byte token. Return that token with the editable fields, then require it on the next update. Put the primary key and expected token in the same UPDATE predicate. Do not first SELECT the token, compare it in application code, and then run an unconditional update; another writer can intervene between those operations.

The following temporary-table example simulates two editors without requiring two connections. Both capture the initial token. Editor A saves first, so editor B's predicate no longer matches.

CREATE TABLE #Draft
(
    DraftId int NOT NULL PRIMARY KEY,
    Title nvarchar(100) NOT NULL,
    Revision rowversion NOT NULL
);
INSERT #Draft (DraftId, Title) VALUES (1, N'Initial title');

DECLARE @SeenByA binary(8), @SeenByB binary(8);
SELECT @SeenByA = Revision, @SeenByB = Revision
FROM #Draft WHERE DraftId = 1;

UPDATE #Draft SET Title = N'Editor A'
OUTPUT inserted.DraftId, inserted.Title, inserted.Revision
WHERE DraftId = 1 AND Revision = @SeenByA;

UPDATE #Draft SET Title = N'Editor B'
WHERE DraftId = 1 AND Revision = @SeenByB;
DECLARE @Changed int = @@ROWCOUNT;

SELECT @Changed AS RowsChanged;
SELECT DraftId, Title, Revision FROM #Draft;
DROP TABLE #Draft;

The second update reports zero rows, and the title remains Editor A. The first OUTPUT result contains the new token that a successful response can return. Capture @@ROWCOUNT immediately when using that mechanism, since later statements can replace its value. A primary key ensures the request can affect at most one document.

Treat the token as opaque binary data. In a JSON API, encode it consistently as base64 or a fixed-format hexadecimal string, decode it to exactly eight bytes, and bind a binary parameter. Do not send it through a JavaScript numeric value. The token is not a timestamp, and clients must not calculate the next value themselves. Keep a separate datetime2 column if the interface needs a human-readable modification time.

Design the conflict response

A zero-row result means the requested key and version combination was not available to update. It can mean a competing edit, a deleted row, or a row outside the caller's authorized scope. The database result alone does not distinguish those cases. Apply authorization in the write predicate or an equally reliable transactional check, and avoid revealing restricted records through a diagnostic follow-up query.

For an authorized editor, a useful conflict screen preserves their submitted text and fetches the current record. Show the original values, their proposal, and the current values when a merge is practical. A conflict response that simply reloads the page and discards the unsaved work solves the database problem while creating a user problem.

Do not automatically retry a stale full-form update with the newest token. That turns optimistic concurrency back into silent last-writer-wins behavior. Some operations have narrower semantics: an atomic increment of a counter can be expressed as an increment rather than replacement of an old total. Choose that contract deliberately instead of making every business command look like a document save.

Keep the protection aligned with the business operation

A row token protects one row. If a screen edits an order header and several lines, checking only the header token will not detect an independent line edit unless every relevant child change also advances a shared aggregate version. Alternatives include checking each changed line's token inside one transaction. Define whether the business operation requires all changes to succeed together, then roll back the whole operation if any required comparison fails.

Triggers deserve an integration test. OUTPUT values describe the statement's result before AFTER triggers execute. A trigger that modifies the same row can advance the token again, so the application may need to read the final token within the transaction after the trigger completes. Do not acknowledge success until the transaction commits; an OUTPUT row by itself is not proof of a committed write.

Test two simultaneous saves, delete-then-save, and a retry after the client loses the response. Log conflict frequency separately from server failures. A high conflict rate can expose a screen that replaces too much data or a background job that updates rows unnecessarily. The protocol should make conflicting intent visible, while allowing ordinary uncontested edits to stay fast.

Technical references: Microsoft Learn: rowversion · Microsoft Learn: OUTPUT clause.