Handle concurrent updates with concurrency tokens

Approach

Add a concurrency token (a rowversion on SQL Server, PostgreSQL's xmin, or a version column), send it to clients (an ETag), require it on updates, and return 412/409 when DbUpdateConcurrencyException occurs.

Solution

using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<CmsDb>(o => o.UseSqlite("Data Source=cms.db"));
var app = builder.Build();

app.MapGet("/api/pages/{id:int}", async (int id, CmsDb db, HttpContext http) =>
{
    var page = await db.Pages.AsNoTracking().FirstOrDefaultAsync(p => p.Id == id);
    if (page is null) return Results.NotFound();
    http.Response.Headers.ETag = $"\"{page.Version}\"";
    return Results.Ok(page);
});

app.MapPut("/api/pages/{id:int}", async (int id, PageUpdate update, CmsDb db, HttpContext http) =>
{
    if (!int.TryParse(http.Request.Headers.IfMatch.ToString().Trim('"'), out var expectedVersion))
        return Results.StatusCode(StatusCodes.Status428PreconditionRequired);
    var page = await db.Pages.FindAsync(id);
    if (page is null) return Results.NotFound();
    db.Entry(page).Property(p => p.Version).OriginalValue = expectedVersion;   // compare with the client's version
    page.Title = update.Title;
    page.Body = update.Body;
    page.Version++;
    try
    {
        await db.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        return Results.Problem("The page was changed by someone else. Reload and try again.", statusCode: 412);
    }
    return Results.NoContent();
});
app.Run();

public record PageUpdate(string Title, string Body);

public class Page
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public string Body { get; set; } = "";
    [ConcurrencyCheck] public int Version { get; set; }
}

public class CmsDb(DbContextOptions<CmsDb> options) : DbContext(options)
{
    public DbSet<Page> Pages => Set<Page>();
}

Pitfalls

• Loading the entity, applying the client's changes, and saving without checking the version the client saw.
• Retrying concurrency failures automatically for user edits; users must see and merge the conflict.
• Forgetting to bump a manual version column.

Variations

• On SQL Server use [Timestamp] public byte[] RowVersion; on PostgreSQL map xmin with IsRowVersion().
• For counters and stock, use atomic ExecuteUpdateAsync(s => s.SetProperty(p => p.Stock, p => p.Stock - 1)).

Further reading: Handling concurrency conflicts