SQL Server Interview Question #244

How do you implement optimistic concurrency with rowversion?

Advanced Transactions & Concurrency Senior Advanced

Detailed Explanation

First read the row together with its rowversion value. When updating, include the original rowversion in the WHERE predicate.

If another transaction changed the row after it was read, the stored rowversion will be different and the UPDATE affects zero rows. The application can then reload the current data, report a conflict, merge changes, or retry according to business rules.

This pattern prevents silently overwriting another user's changes.

Code Example

UPDATE dbo.Products
SET Name = @Name,
    Price = @Price
WHERE Id = @Id
  AND RowVersion = @OriginalRowVersion;

IF @@ROWCOUNT = 0
    THROW 50001, 'The record was changed by another user.', 1;