Prevent Lost Updates — Optimistic Concurrency in Practice

The Lost Update Problem

Two users load the same Product record. Both read Stock = 10. User A subtracts 3 and saves Stock = 7. User B (who still has the stale Stock = 10) subtracts 5 and saves Stock = 5. User A's update is silently gone. You sold 8 units but the DB shows 5.

Time  User A                User B
 1    READ  stock=10        READ  stock=10
 2    stock = 10 - 3 = 7
 3    WRITE stock=7
 4                          stock = 10 - 5 = 5   ← stale read!
 5                          WRITE stock=5         ← A's update LOST

This happens whenever you have a read-modify-write cycle without coordination.

Optimistic vs Pessimistic Concurrency

Pessimistic: Lock the row when you read it (SELECT ... FOR UPDATE). Nobody else can touch it until you commit. Safe but kills throughput — locks held across a web request are a scalability disaster.

Optimistic: Don't lock. Instead, record a version stamp when you read. On write, assert "the row still has this version." If it doesn't, someone changed it — fail fast.

EF Core supports optimistic concurrency natively.

Adding a RowVersion Token

The simplest approach: a [Timestamp] byte array column. SQL Server auto-increments it on every write.

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public int Stock { get; set; }
    public decimal Price { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = Array.Empty<byte>();
}

EF Core sees [Timestamp] and automatically includes RowVersion in the WHERE clause of every UPDATE and DELETE:

SQL

UPDATE Products
SET Stock = 7
WHERE Id = 1 AND RowVersion = 0x00000000000007D2   -- version we read

If zero rows are affected (version changed), EF throws DbUpdateConcurrencyException.

Fluent API Configuration (no annotations)

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.Property(p => p.RowVersion)
               .IsRowVersion()           // maps to rowversion / timestamp in SQL Server
               .IsConcurrencyToken();    // tells EF to include in WHERE clause
    }
}

For non-SQL Server databases or custom concurrency tokens (e.g., a LastModified DateTime):

builder.Property(p => p.LastModified)
       .IsConcurrencyToken();

You must update LastModified manually before saving. IsRowVersion() is SQL Server-specific and auto-managed.

Handling DbUpdateConcurrencyException

EF throws this when the affected row count doesn't match what was expected.

public async Task<IActionResult> DecrementStock(int productId, int quantity)
{
    var product = await _db.Products.FindAsync(productId);
    if (product is null) return NotFound();

    product.Stock -= quantity;

    try
    {
        await _db.SaveChangesAsync();
        return Ok(new { product.Stock });
    }
    catch (DbUpdateConcurrencyException ex)
    {
        // The entry that caused the conflict
        var entry = ex.Entries.Single();

        // Current DB values (what another request wrote)
        var dbValues = await entry.GetDatabaseValuesAsync();

        if (dbValues is null)
        {
            // Row was deleted by the other request
            return Conflict(new { error = "Product was deleted by another operation." });
        }

        // Reload and reject — tell the caller to retry with fresh data
        await entry.ReloadAsync();
        return Conflict(new
        {
            error = "Concurrent modification detected. Reload and try again.",
            currentStock = ((Product)entry.Entity).Stock
        });
    }
}

Retry Strategy

For background processing (not user-facing requests), auto-retry is often appropriate:

public async Task DecrementStockWithRetry(int productId, int quantity, int maxRetries = 3)
{
    for (int attempt = 0; attempt < maxRetries; attempt++)
    {
        // Always use a fresh DbContext per attempt — don't reuse the stale one
        await using var db = _dbContextFactory.CreateDbContext();

        var product = await db.Products.FindAsync(productId);
        if (product is null) throw new InvalidOperationException("Product not found.");

        product.Stock -= quantity;

        try
        {
            await db.SaveChangesAsync();
            return; // success
        }
        catch (DbUpdateConcurrencyException)
        {
            if (attempt == maxRetries - 1) throw; // exhausted retries
            // Brief pause before retry — optional, reduces thundering herd
            await Task.Delay(TimeSpan.FromMilliseconds(50 * (attempt + 1)));
        }
    }
}

Key insight: create a new DbContext per retry. Reusing the same context after a concurrency exception leaves it in an inconsistent tracked state.

Testing Concurrency With Two DbContext Instances

You don't need multiple threads to reproduce a conflict in a test:

[Fact]
public async Task SaveChanges_ThrowsConcurrencyException_WhenRowVersionStale()
{
    // Arrange — seed a product
    await using var seedDb = CreateDb();
    seedDb.Products.Add(new Product { Name = "Widget", Stock = 10 });
    await seedDb.SaveChangesAsync();
    int productId = seedDb.Products.First().Id;

    // Simulate two concurrent reads
    await using var dbA = CreateDb();
    await using var dbB = CreateDb();

    var productA = await dbA.Products.FindAsync(productId);
    var productB = await dbB.Products.FindAsync(productId);

    // User A saves first
    productA!.Stock -= 3;
    await dbA.SaveChangesAsync(); // succeeds, increments RowVersion

    // User B tries to save with a stale RowVersion
    productB!.Stock -= 5;

    await Assert.ThrowsAsync<DbUpdateConcurrencyException>(
        () => dbB.SaveChangesAsync()
    );
}

Concurrency Token on a Specific Property

Sometimes you only want to detect conflicts on a subset of columns:

public class Order
{
    public int Id { get; set; }
    public string Status { get; set; } = "Pending";  // concurrency-sensitive
    public string Notes { get; set; } = string.Empty; // not sensitive

    [ConcurrencyCheck]
    public string Status { get; set; } = "Pending";
}

Or with Fluent API:

builder.Property(o => o.Status).IsConcurrencyToken();

EF now only conflicts if Status changed between your read and write — not on any column change.

Quick Reference

| Scenario | Approach | |---|---| | SQL Server, auto-managed version | [Timestamp] / IsRowVersion() | | Other DBs or custom token | [ConcurrencyCheck] / IsConcurrencyToken() | | User-facing conflict | Return 409, let client reload | | Background job conflict | Retry with fresh DbContext | | Deleted-row conflict | Check GetDatabaseValuesAsync() == null |