Optimistic Locking in EF Core C# with Concurrency Tokens

// Optimistic Locking in EF Core C# with Concurrency Tokens
//
// PURPOSE
// Optimistic concurrency detects whether a row changed between reading it and saving an edit. EF
// Core adds the original concurrency-token value to the UPDATE or DELETE predicate.
//
// The example uses an application-managed GUID token so the pattern can be demonstrated on
// PostgreSQL without pretending that SQL Server rowversion is a portable database feature.
//
// METHOD
// Configure Version with IsConcurrencyToken and ValueGeneratedNever. Return the version to the
// client when reading stock and require it on the next command.
//
// The service uses a fresh tracked context, rejects a token already known to be stale, checks
// stock, assigns a new GUID, and calls SaveChangesAsync.
//
// Another writer may still commit between the in-memory check and SaveChanges. EF includes the
// originally read Version in its SQL predicate, so that race results in zero affected rows and
// DbUpdateConcurrencyException rather than a lost update.
//
// The service wraps that database exception in a domain conflict. If the row is deleted after it
// was read, the same concurrency mechanism detects the missing write target.
//
// COMPLEXITY
// No row lock is held during a user's editing period. The database still takes normal locks
// while executing UPDATE; optimistic concurrency is not a lock-free write protocol.
//
// Low-conflict editing is a good fit. If a command is safe to re-evaluate, use a bounded retry
// with a fresh context and freshly read business state. Do not retry stale field assignments
// indefinitely.
//
// Do not merely overwrite OriginalValues with current database values and save stale
// CurrentValues. That bypasses detection without deciding which edit should win. Merge, reject,
// or re-run the domain action deliberately.
//
// CONTRACT
// Runnable sample: net10.0, EF Core 10.x, and Npgsql/PostgreSQL. quantity must be positive and
// expectedVersion must be a nonempty GUID. The stock check constraint remains a final database
// safeguard.
//
// All relevant writers must assign a new Version when changing the protected state.
// ExecuteUpdate, raw SQL, bulk imports, and external writers do not automatically follow the
// tracked SaveChanges concurrency protocol.
//
// A stale submitted token is rejected before the edit. A race at SaveChanges is detected by the
// database predicate. Keep the client's token rather than silently replacing it with a freshly
// loaded one.
//
// SQL Server can instead map a byte-array property with IsRowVersion for a database-generated
// rowversion token. rowversion is a change token, not a wall-clock timestamp, and its automatic
// behavior is SQL Server-specific.
//
// For an HTTP API, return a conflict response with refresh guidance, commonly 409; a failed
// If-Match precondition is 412. Do not leak database exception details to the client.
//
// LANGUAGE DETAILS
// Each attempt owns and disposes its context. After an unsuccessful save, do not accidentally
// reuse tracked modified entities for a later unrelated operation.
//
// The example explicitly changes Version. IsConcurrencyToken marks the comparison property but
// does not generate a new application-managed token for you. A SaveChanges interceptor can
// centralize token generation in a larger application.
//
// EXAMPLE
// Create a net10.0 project and install Npgsql.EntityFrameworkCore.PostgreSQL 10.x. Copy the
// implementation below, register a DbContext factory, and generate/apply EF migrations for the
// model.
//
// Read a stock row and return its Version with the response. Usage: nextVersion = await
// reservations.ReserveAsync(1, 1, originalVersion, ct). The successful response returns the
// replacement token.
//
// The test project forces two independent contexts to reach SaveChanges with the same original
// token. Exactly one succeeds, and the loser carries a real DbUpdateConcurrencyException. Tests
// also cover an already stale client token, deleted rows, insufficient stock, invalid input,
// cancellation, and the database constraint.
//
// Download the complete examples, dependency lock file, and integration tests:
// https://www.dimkoug.com/static/downloads/locking-examples-tested.zip
//
// SOURCES
// EF Core concurrency handling: https://learn.microsoft.com/en-us/ef/core/saving/concurrency
//
// EF Core ExecuteUpdate concurrency behavior:
// https://learn.microsoft.com/en-us/ef/core/saving/execute-insert-update-delete#concurrency-control-and-rows-affected
//
// Npgsql concurrency tokens: https://www.npgsql.org/efcore/modeling/concurrency.html
//
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace LockingPosts.Optimistic;

public sealed class Stock
{
    public int Id { get; set; }
    public int Available { get; set; }
    public Guid Version { get; set; } = Guid.NewGuid();
}

public sealed class InventoryDb : DbContext
{
    public InventoryDb(DbContextOptions<InventoryDb> options) : base(options) { }
    public DbSet<Stock> Stocks => Set<Stock>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var entity = modelBuilder.Entity<Stock>();
        entity.ToTable("LockingOptimisticStocks", table =>
            table.HasCheckConstraint("ck_opt_stock_nonnegative", "\"Available\" >= 0"));
        entity.Property(stock => stock.Version)
            .IsConcurrencyToken()
            .ValueGeneratedNever();
    }
}

public sealed class ReservationConflictException : Exception
{
    public ReservationConflictException(string message, Exception? inner = null)
        : base(message, inner) { }
}

public sealed class InsufficientStockException : InvalidOperationException
{
    public InsufficientStockException() : base("Not enough stock.") { }
}

public sealed class Reservations
{
    private readonly IDbContextFactory<InventoryDb> factory;
    public Reservations(IDbContextFactory<InventoryDb> factory) => this.factory = factory;

    public async Task<Guid> ReserveAsync(
        int id, int quantity, Guid expectedVersion, CancellationToken ct = default)
    {
        ArgumentOutOfRangeException.ThrowIfNegativeOrZero(quantity);
        if (expectedVersion == Guid.Empty)
            throw new ArgumentException("A client version is required.", nameof(expectedVersion));

        // A fresh context for each logical attempt; contexts are not thread-safe.
        await using var db = await factory.CreateDbContextAsync(ct);
        var stock = await db.Stocks.SingleOrDefaultAsync(item => item.Id == id, ct)
            ?? throw new KeyNotFoundException("SKU not found.");

        // Preserve the version supplied by the client; do not silently overwrite it.
        if (stock.Version != expectedVersion)
            throw new ReservationConflictException("The submitted version is stale.");
        if (stock.Available < quantity)
            throw new InsufficientStockException();

        stock.Available -= quantity;
        stock.Version = Guid.NewGuid();
        try
        {
            // EF compares the ORIGINAL Version in the UPDATE's WHERE clause.
            await db.SaveChangesAsync(ct);
        }
        catch (DbUpdateConcurrencyException error)
        {
            throw new ReservationConflictException(
                "Stock changed or was deleted. Refresh before trying again.", error);
        }
        return stock.Version;
    }
}