Optimistic Concurrency — rowversion, Timestamp and Application Patterns

Optimistic concurrency assumes conflicts are rare — it allows concurrent reads without locking, but detects conflicts at write time. SQL Server’s rowversion column automatically increments when a row is modified. EF Core uses it as a concurrency token: when updating, it includes WHERE RowVersion = @originalValue. If another session modified the row since it was read, the RowVersion will have changed and zero rows update — EF Core throws DbUpdateConcurrencyException, which the application handles.

Optimistic Concurrency with rowversion #

-- ── Add rowversion to Posts ────────────────────────────────────────────────
ALTER TABLE dbo.Posts ADD RowVersion ROWVERSION NOT NULL;
-- ROWVERSION: 8-byte binary, auto-updated on every modification
-- Equivalent name: TIMESTAMP (deprecated but still works)
-- Value is unique within the database (monotonically increasing)

-- ── The optimistic update pattern ────────────────────────────────────────
DECLARE @OriginalRowVersion BINARY(8) = 0x0000000000000215;  -- client sends this back

UPDATE dbo.Posts
SET    Title     = N'Updated Title',
       Body      = N'Updated content...',
       UpdatedAt = SYSUTCDATETIME()
WHERE  Id        = 42
  AND  RowVersion = @OriginalRowVersion;   -- concurrency check

-- If @@ROWCOUNT = 0: another session modified the row — conflict detected
-- If @@ROWCOUNT = 1: update succeeded

IF @@ROWCOUNT = 0
    RAISERROR('Concurrency conflict: post was modified by another user.', 16, 1);

// ── EF Core concurrency token setup ──────────────────────────────────────
public class Post
{
    public int    Id         { get; set; }
    public string Title      { get; set; } = "";
    // rowversion column — auto-managed by SQL Server
    [Timestamp]
    public byte[] RowVersion { get; set; } = [];
}

// In DbContext (or Fluent API):
modelBuilder.Entity<Post>()
    .Property(p => p.RowVersion)
    .IsRowVersion();   // tells EF Core to include this in WHERE clause on UPDATE

// ── Service method with conflict handling ──────────────────────────────────
public async Task<UpdateResult> UpdatePostAsync(
    UpdatePostRequest request, byte[] originalRowVersion, CancellationToken ct)
{
    var post = await _db.Posts.FindAsync([request.Id], ct);
    if (post is null) return UpdateResult.NotFound;

    // Check that client has the latest version
    if (!post.RowVersion.SequenceEqual(originalRowVersion))
        return UpdateResult.Conflict;

    post.Title     = request.Title;
    post.Body      = request.Body;
    post.UpdatedAt = DateTime.UtcNow;
    // EF Core generates: UPDATE Posts SET ... WHERE Id=@id AND RowVersion=@original

    try
    {
        await _db.SaveChangesAsync(ct);
        return UpdateResult.Success;
    }
    catch (DbUpdateConcurrencyException)
    {
        // Another user saved between our read and our write
        return UpdateResult.Conflict;
    }
}

// ── REST API — return RowVersion as ETag ──────────────────────────────────
// GET /api/posts/42
// Response: { "id": 42, "title": "...", "rowVersion": "AAAAAAAAIFI=" }
// Response Header: ETag: "AAAAAAAAIFI="

// PUT /api/posts/42
// Request Header: If-Match: "AAAAAAAAIFI="
// If RowVersion doesn't match: 412 Precondition Failed
// If RowVersion matches and update succeeds: 200 OK with new RowVersion

Common Mistakes #

Mistake 1 — Not handling DbUpdateConcurrencyException (user sees 500 error on conflict) #

❌ Wrong — concurrent update causes DbUpdateConcurrencyException; unhandled exception returns 500.

✅ Correct — catch DbUpdateConcurrencyException; return 409 Conflict with a meaningful message to the client.

Mistake 2 — Retrying after conflict without reloading the entity (retry fails again) #

❌ Wrong — catch exception, retry SaveChanges immediately; EF Core still has stale RowVersion; fails again.

✅ Correct — call entry.Reload() after the exception to get fresh data, then re-apply changes and retry.