Lost Updates: Optimistic Concurrency With a rowversion Column

Two users edit the same customer row, and the last save silently erases the first. Optimistic concurrency adds a version check so the second save becomes a visible conflict.

Reproduce the Lost Update

Create a test row and open it from two sessions before either saves. Each application holds the original values in memory. The first session updates the row and commits. The second session then updates using only the primary key. Its change succeeds and overwrites the first user's work. I show this with two query windows because the final row alone does not reveal the conflict. The database did exactly what the second UPDATE asked; the application forgot to say which version it had read.

Do not confuse this with a dirty read. Both users can read committed data and still lose an update later. The gap between read and save is the problem.

Add a rowversion Token for Optimistic Concurrency

A rowversion column changes whenever the row is updated. It is a binary token, not a timestamp or wall-clock date. Return it with the row to the client. The client must send back the exact token it received when saving. Do not convert it to text and back through a lossy format. I keep it in a parameter with the same binary length and treat it as opaque. The token does not say who changed the row or why; audit data is a separate requirement.

DROP TABLE IF EXISTS dbo.CustomerEdit;
CREATE TABLE dbo.CustomerEdit
(
    CustomerID int NOT NULL PRIMARY KEY,
    DisplayName nvarchar(100) NOT NULL,
    VersionToken rowversion
);
INSERT dbo.CustomerEdit(CustomerID, DisplayName) VALUES (1,N'Original');
SELECT CustomerID, DisplayName, VersionToken
FROM dbo.CustomerEdit WHERE CustomerID = 1;

Match the Version in the UPDATE

Put both the key and original version in the WHERE clause. Immediately inspect @@ROWCOUNT. One row means the value matched and the update occurred. Zero means the row was deleted or changed since the client read it. Return a conflict response and reload current values so the user can choose how to proceed. I do not retry the second save automatically with the new token; that would restore the silent overwrite under a fancier name.

The second UPDATE below reuses the old token, so it plays the second user. In my run, the first save returned 1, the second returned 0, and the row still read First edit.

DECLARE @original_version binary(8);
SELECT @original_version = VersionToken
FROM dbo.CustomerEdit WHERE CustomerID = 1;
UPDATE dbo.CustomerEdit
SET DisplayName = N'First edit'
WHERE CustomerID = 1 AND VersionToken = @original_version;
SELECT @@ROWCOUNT AS first_save_rows;
UPDATE dbo.CustomerEdit
SET DisplayName = N'Second edit'
WHERE CustomerID = 1 AND VersionToken = @original_version;
SELECT @@ROWCOUNT AS second_save_rows;
SELECT DisplayName FROM dbo.CustomerEdit WHERE CustomerID = 1;

Test the Optimistic Concurrency Conflict Path

Use two sessions against a permanent test table or simulate with two captured tokens. Both read the original version. Commit the first edit, then submit the second using the old token. Verify its UPDATE affects zero rows and the first value remains. Test delete and insert scenarios separately. A zero-row result can mean the key no longer exists, so the user message should be accurate. I ask product owners whether to show a merge screen, a reload button, or a specific conflict message. The database supplies the signal; the application decides the recovery flow.

Keep unrelated columns in mind. A rowversion detects any update to that row, even a background change to a field the user did not edit. That can be conservative, but it protects against silent loss.

Compare Optimistic Concurrency With Locking

For a short server-side read-modify-write transaction, UPDLOCK can reserve the row for a subsequent update. Keep the transaction short and commit before any user interaction. HOLDLOCK can extend the isolation guarantee when a range or existence check matters. This pattern works when the complete decision happens inside one database transaction. It does not work for a form left open for ten minutes; holding locks through that wait would block everyone else.

BEGIN TRANSACTION;
SELECT DisplayName FROM dbo.CustomerEdit WITH (UPDLOCK)
WHERE CustomerID = 1;
UPDATE dbo.CustomerEdit SET DisplayName = N'Server-side edit'
WHERE CustomerID = 1;
COMMIT TRANSACTION;

Decide What a Conflict Means

The version predicate turns a silent overwrite into a zero-row update. The application must distinguish that from "customer not found" and from a validation failure. Read the current row after a conflict and show the user both the latest value and the edit they tried to save. I avoid merging fields automatically unless the product has an explicit merge rule. Two users can change the same field for different reasons. A blind retry with the new token simply overwrites the first change after a brief delay.

Keep an audit record for significant fields. rowversion tells you the row changed, but it does not say who changed it or what changed. The audit trail and version check serve different needs. I log conflict counts and the API operation, but not private field values in a general application log.

Test Token Handling Through Every Layer

Send a rowversion value through the actual API serializer, client state, and stored procedure call. A byte array can become a hex string, base64 text, or an incorrectly truncated value depending on the framework. Test round-tripping without altering any byte. I also check that caches return the token with the data; a cached record without its version invites an unconditional save. If an ORM provides concurrency tokens, inspect its generated UPDATE to confirm both key and version appear in the WHERE clause.

What happens after a background process updates only a bookkeeping column? The rowversion still changes, so a user edit can conflict even when the visible fields did not. That conservative behavior is usually safer than silent loss, but the user flow should handle it gracefully. For a very hot record, shorter edits or server-side commands can reduce conflicts. I measure the conflict rate before choosing a more complex merge design.

Do not expose rowversion as a date in the interface. It is an opaque binary token that changes with row updates, not a wall-clock value. I label it as a version in the API contract and keep it out of user-facing copy. For audit questions, store a separate changed-at timestamp and actor under an explicit policy.

Keep the Contract End to End

Return the version token from every read path that supports editing. Include it in API requests and stored procedure parameters. Do not drop it in a mapping layer or cache. I test conflict handling through the actual application, not only in SSMS. A procedure that returns zero rows is useless if the API reports success anyway. Log conflict rates without logging private field values. A rise in conflicts can reveal a workflow that keeps forms open too long.

What should happen after a conflict? Make that answer explicit. Optimistic concurrency protects data by refusing an outdated save. It becomes a usable feature only when the user can see the current record and make a fresh decision.

Related reading on this blog: Concurrency Problems and their Relationship with Isolation Level and Locking, Blocking, and Deadlocking: Differences, Similarities, and Best Practices.

A lost update is not a database error, it is a save that never checked the version.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.