EF Core Concurrency — Optimistic Locking and Conflict Handling

Two clinicians load Patient #1 at the same time:
  Clinician A reads: Warfarin dose = 5mg
  Clinician B reads: Warfarin dose = 5mg

Clinician A changes to 7mg, saves → database: 7mg
Clinician B changes to 3mg, saves → database: 3mg (overwrites A's change)

Result: Clinician A's update is silently lost.
In a clinical context, this is a patient safety issue.

Optimistic concurrency: detect the conflict and let the application decide.
Pessimistic concurrency: lock the row while one user has it (rarely needed).

// Domain entity: row version column
public class Prescription
{
    public PrescriptionId Id     { get; private set; }
    public DosageValue    Dose   { get; private set; } = default!;
    public byte[]         RowVersion { get; private set; } = default!;  // EF manages this
}

// Configuration
public sealed class PrescriptionConfiguration : IEntityTypeConfiguration<Prescription>
{
    public void Configure(EntityTypeBuilder<Prescription> builder)
    {
        builder.Property(p => p.RowVersion)
            .IsRowVersion()
            .IsConcurrencyToken();
        // SQL Server: rowversion (timestamp) column — auto-incremented on every update
    }
}

// EF Core generates:
// UPDATE prescriptions SET dose = @dose
// WHERE id = @id AND row_version = @originalRowVersion
// If 0 rows affected → RowVersion changed → conflict → DbUpdateConcurrencyException

// Application service: handle the conflict explicitly
public async Task<Result> UpdateWarfarinDoseAsync(
    PrescriptionId id, DosageValue newDose, byte[] expectedVersion, CancellationToken ct)
{
    var prescription = await _repo.GetByIdAsync(id, ct);
    if (prescription is null)
        return Result.Failure(DomainErrors.Prescription.NotFound);

    prescription.UpdateDose(newDose);

    try
    {
        await _uow.SaveChangesAsync(ct);
        return Result.Success();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        // Another user modified this prescription between our read and write
        var entry   = ex.Entries.First();
        var dbValues  = await entry.GetDatabaseValuesAsync(ct);

        if (dbValues is null)
            return Result.Failure(DomainErrors.Prescription.Deleted);

        // Return current database values so the client can show a conflict UI
        var currentDose = dbValues.GetValue<decimal>("dose_amount");
        return Result.Failure(DomainErrors.Prescription.ConcurrencyConflict(currentDose));
    }
}

// Use a specific property as a concurrency token (not a DB-generated rowversion)
public class Patient
{
    public Guid Id          { get; private set; }
    public string Mrn       { get; private set; } = default!;
    public DateTime UpdatedAt { get; private set; }
}

// Configuration: UpdatedAt as concurrency token
builder.Property(p => p.UpdatedAt)
    .IsConcurrencyToken();

// Before saving: update the token
public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    foreach (var entry in ChangeTracker.Entries<Patient>()
        .Where(e => e.State == EntityState.Modified))
    {
        entry.Entity.SetUpdatedAt(DateTime.UtcNow);
    }
    return await base.SaveChangesAsync(ct);
}

// EF generates: WHERE id = @id AND updated_at = @originalUpdatedAt

// For critical sections where optimistic conflict handling is not acceptable:
// Lock the row at read time so no other transaction can modify it

await using var transaction = await _db.Database.BeginTransactionAsync(
    IsolationLevel.ReadCommitted, ct);

// SELECT ... WITH (UPDLOCK, ROWLOCK) — locks the row until transaction commits
var prescription = await _db.Prescriptions
    .FromSqlRaw(
        "SELECT * FROM prescriptions WITH (UPDLOCK, ROWLOCK) WHERE id = @id",
        new SqlParameter("@id", id.Value))
    .FirstOrDefaultAsync(ct);

// At this point, no other transaction can update this row.
// Make changes:
prescription!.DispenseMedication();
await _db.SaveChangesAsync(ct);

await transaction.CommitAsync(ct);

// UPDLOCK is appropriate for: dispensing medications, decreasing inventory,
// any operation where two concurrent saves would create an inconsistent result.

// REST API: client sends the RowVersion it received with the GET
// PUT /api/prescriptions/{id}
// Body: { "newDose": 7.5, "rowVersion": "AAAAAAAAB9E=" }

[HttpPut("{id}")]
public async Task<IActionResult> UpdateDose(
    Guid id,
    [FromBody] UpdateDoseRequest request)
{
    var rowVersion = Convert.FromBase64String(request.RowVersion);

    var result = await _service.UpdateWarfarinDoseAsync(
        new PrescriptionId(id),
        new DosageValue(request.NewDose, request.Unit),
        rowVersion,
        HttpContext.RequestAborted);

    return result.IsSuccess
        ? NoContent()
        : result.Error == DomainErrors.Prescription.ConcurrencyConflict
            ? Conflict(new { message = "This prescription was modified by someone else.", currentDose = result.Error.Detail })
            : BadRequest();
}