Optimistic Concurrency

Detect lost updates with rowversion or xmin concurrency tokens, carry the version through ETags, and answer conflicts with 412 or 409.

Syntax

modelBuilder.Entity<Product>().Property(p => p.Version).IsRowVersion();
db.Entry(product).Property(p => p.Version).OriginalValue = versionFromIfMatch;

Two support agents open product 42. One changes the price, the other the description, and both send the whole product back. Without a check the second save silently overwrites the first: a lost update. Optimistic concurrency detects it at write time without holding any lock while a person edits.

The mechanism

A property configured as a concurrency token is added to the WHERE clause of every UPDATE and DELETE that SaveChanges generates for the entity: UPDATE "Products" SET "Price" = @p0 WHERE "Id" = @p1 AND xmin = @p2. EF uses the token's original value, the one recorded when the entity was loaded or attached. If another writer changed the row, the token no longer matches, the database reports 0 rows affected and SaveChanges throws DbUpdateConcurrencyException. The whole SaveChanges is rolled back with it.

Token	Configuration	Who changes it
SQL Server `rowversion`	`[Timestamp] byte[] RowVersion` or `IsRowVersion()`	The database, on every write to the row
PostgreSQL `xmin`	`uint Version` with `IsRowVersion()`; Npgsql maps it to the system column, so no column is added	The database: `xmin` is the id of the transaction that wrote the row version
Any column	`[ConcurrencyCheck]` or `IsConcurrencyToken()`, for example a `Guid`	Your code, on every save; forget it once and the check is useless

Choose the database-managed token. App-managed tokens are for engines without one, such as SQLite.

APIs are disconnected

In a web API the entity is read in one request and written in a later one. If the PUT handler loads the product and saves it, the token it compares against was read a millisecond ago, so the check always passes. The client must carry the version it saw: return it as an ETag, require it in If-Match on PUT, PATCH and DELETE, and make it the original value before saving: db.Entry(product).Property(p => p.Version).OriginalValue = expected. That one line is what most broken implementations miss.

Status codes

Missing If-Match on an endpoint that requires it: 428 Precondition Required.
Stale If-Match, found by comparing up front or by the exception: 412 Precondition Failed.
Version sent in the body instead of a header: 409 Conflict is the usual answer.

Put the current ETag or state in the problem response so the client can reload and merge.

Resolving on the server

For human edits, report the conflict; retrying blindly recreates the lost update. For commutative changes such as adjusting stock by a delta, catch the exception, call entry.GetDatabaseValuesAsync() (null means the row was deleted), copy those values into the original and current values, reapply the delta and save again with a small retry limit. An atomic ExecuteUpdate of Stock = Stock - qty is often simpler.

Production traps

A row version changes on every write to the row, including a background job bumping a LastViewedAt column, and each of those makes open edit forms fail with 412. Move hot counters to their own table. ExecuteUpdate does not check tokens unless you add them to the Where.

Example

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<CatalogDb>(o => o.UseNpgsql(builder.Configuration.GetConnectionString("Catalog")));
builder.Services.AddProblemDetails();
var app = builder.Build();

app.MapGet("/products/{id:int}", async (int id, CatalogDb db, HttpContext http, CancellationToken ct) =>
{
    var product = await db.Products.AsNoTracking().SingleOrDefaultAsync(p => p.Id == id, ct);
    if (product is null) return Results.NotFound();

    http.Response.Headers.ETag = ETag.From(product.Version);
    return Results.Ok(new ProductBody(product.Name, product.Price));
});

app.MapPut("/products/{id:int}", async (int id, ProductBody body, CatalogDb db, HttpContext http, CancellationToken ct) =>
{
    if (!ETag.TryParse(http.Request.Headers.IfMatch.ToString(), out var expected))
    {
        return Results.Problem(statusCode: StatusCodes.Status428PreconditionRequired,
            title: "Send the ETag from GET /products/{id} in an If-Match header.");
    }

    var product = await db.Products.SingleOrDefaultAsync(p => p.Id == id, ct);
    if (product is null) return Results.NotFound();

    // Compare against the version the client edited, not the one loaded a moment ago.
    db.Entry(product).Property(p => p.Version).OriginalValue = expected;
    product.Name = body.Name;
    product.Price = body.Price;

    try
    {
        await db.SaveChangesAsync(ct);
    }
    catch (DbUpdateConcurrencyException)
    {
        var current = await db.Products.AsNoTracking().Where(p => p.Id == id)
            .Select(p => (uint?)p.Version).FirstOrDefaultAsync(ct);
        if (current is null) return Results.NotFound(); // deleted after the client's GET

        http.Response.Headers.ETag = ETag.From(current.Value);
        return Results.Problem(statusCode: StatusCodes.Status412PreconditionFailed,
            title: "The product changed after you loaded it. Reload it and apply your change again.");
    }

    // Npgsql reads the new xmin back after the UPDATE.
    http.Response.Headers.ETag = ETag.From(product.Version);
    return Results.NoContent();
});

app.Run();

public record ProductBody(string Name, decimal Price);

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public uint Version { get; set; }
}

public class CatalogDb(DbContextOptions<CatalogDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Maps to PostgreSQL's xmin system column (type xid); no migration column is created.
        modelBuilder.Entity<Product>().Property(p => p.Version).IsRowVersion();
    }
}

static class ETag
{
    public static string From(uint version) => $"\"{version}\"";

    public static bool TryParse(string header, out uint version)
    {
        version = 0;
        return header.Length > 2 && header[0] == '"' && header[^1] == '"'
            && uint.TryParse(header.AsSpan(1, header.Length - 2), out version);
    }
}

When to use it

Reject a PUT /products/42 with 412 when another agent changed the product after this agent loaded the edit form.
Return 428 for a DELETE /invoices/{id} that arrives without If-Match, so no client can delete an invoice it has not seen.
Reapply a +5 stock correction on top of the latest quantity when a concurrent picker updated the same row first.

More examples

rowversion on SQL Server

<code>[Timestamp]</code> on a <code>byte[]</code> creates a <code>rowversion</code> column, marks it as a concurrency token and as generated on add and update, so EF reads the new value back after each save. The 8 bytes are base64-encoded into a strong ETag. Never let clients send the version inside the JSON body of a PUT without also treating it as the original value; a body field the handler ignores gives the appearance of protection with none of it.

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

public class Invoice
{
    public int Id { get; set; }
    public string Number { get; set; } = "";
    public decimal Amount { get; set; }
    public string Status { get; set; } = "Draft";

    // SQL Server fills this on insert and changes it on every update of the row.
    [Timestamp]
    public byte[] RowVersion { get; set; } = [];
}

public class InvoiceConfiguration : IEntityTypeConfiguration<Invoice>
{
    public void Configure(EntityTypeBuilder<Invoice> builder)
    {
        builder.Property(i => i.Number).HasMaxLength(20);
        builder.Property(i => i.Amount).HasPrecision(18, 2);
    }
}

public static class InvoiceETag
{
    public static string From(byte[] rowVersion) => $"\"{Convert.ToBase64String(rowVersion)}\"";

    public static byte[]? Parse(string ifMatch)
    {
        if (ifMatch.Length < 3 || ifMatch[0] != '"' || ifMatch[^1] != '"') return null;
        try { return Convert.FromBase64String(ifMatch[1..^1]); }
        catch (FormatException) { return null; }
    }
}

public class BillingDb(DbContextOptions<BillingDb> options) : DbContext(options)
{
    public DbSet<Invoice> Invoices => Set<Invoice>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.ApplyConfiguration(new InvoiceConfiguration());
}

Resolving a commutative change by retrying

A stock adjustment is a delta, so it can safely be reapplied to whatever the latest quantity is. Setting <code>OriginalValues</code> from the database values moves the token forward so the next UPDATE matches, and the quantity is recomputed from the fresh row rather than the stale one. After three conflicts the exception propagates, which under heavy contention is the signal to switch to an atomic <code>ExecuteUpdate</code>. Never apply this loop to a user's form edit: that silently discards the other person's change.

using Microsoft.EntityFrameworkCore;

public class StockLevel
{
    public int Id { get; set; }
    public string Sku { get; set; } = "";
    public int Quantity { get; set; }
    public uint Version { get; set; }
}

public class WarehouseDb(DbContextOptions<WarehouseDb> options) : DbContext(options)
{
    public DbSet<StockLevel> Stock => Set<StockLevel>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<StockLevel>().Property(s => s.Version).IsRowVersion();
}

public class StockAdjuster(WarehouseDb db)
{
    public async Task<bool> AdjustAsync(string sku, int delta, CancellationToken ct)
    {
        var level = await db.Stock.SingleAsync(s => s.Sku == sku, ct);
        level.Quantity += delta;

        for (var attempt = 1; ; attempt++)
        {
            try
            {
                await db.SaveChangesAsync(ct);
                return true;
            }
            catch (DbUpdateConcurrencyException ex) when (attempt < 3)
            {
                var entry = ex.Entries.Single();
                var databaseValues = await entry.GetDatabaseValuesAsync(ct);
                if (databaseValues is null) return false; // deleted meanwhile

                // Rebase the delta on the latest row: new original values, then reapply.
                var latest = (StockLevel)databaseValues.ToObject();
                entry.OriginalValues.SetValues(databaseValues);
                level.Quantity = latest.Quantity + delta;
            }
        }
    }
}

The HTTP exchange

The client stores the ETag from the GET and returns it unchanged in <code>If-Match</code>. Another agent saved in between, so the row's <code>xmin</code> moved from 918274 to 918311 and the UPDATE matched no rows. The 412 carries the current ETag, so a client can fetch the new state, show a merge view and retry with the new value. ETags must be strong (no <code>W/</code> prefix) for <code>If-Match</code>.

GET /products/42 HTTP/1.1
Host: api.example.com

HTTP/1.1 200 OK
ETag: "918274"
Content-Type: application/json

{"name":"Blue kettle","price":39.90}

###
PUT /products/42 HTTP/1.1
Host: api.example.com
If-Match: "918274"
Content-Type: application/json

{"name":"Blue kettle 1.7 l","price":39.90}

HTTP/1.1 412 Precondition Failed
ETag: "918311"
Content-Type: application/problem+json

{"type":"https://tools.ietf.org/html/rfc9110#section-15.5.13","title":"The product changed after you loaded it. Reload it and apply your change again.","status":412}

Discussion

Be the first to comment on this lesson.