Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 41 ยท Performance and Concurrency

Optimistic Concurrency

Handle concurrent edits in EF Core with concurrency tokens, rowversion and Timestamp, catch DbUpdateConcurrencyException and resolve conflicts (client wins, database wins, merge), with runnable stand-ins.

Two people open the same product page. Alice sets the stock to 40 and saves. Bob, who loaded the page before that, sets the price to 9.99 and saves. If the application writes Bob's whole record, Alice's stock change is silently lost. This is the lost update problem, and it happens in any multi-user application. EF Core's answer is optimistic concurrency: assume conflicts are rare, do not lock anything, and detect a conflict at save time.

EF Core code is read-only on this site. The runnable blocks are stand-ins for the version-check mechanism and the resolution strategies.

Optimistic versus pessimistic

  • Pessimistic: lock the row when someone starts editing (SELECT ... FOR UPDATE, UPDLOCK). Safe but blocks others, holds locks across user think time, and EF Core has no built-in API for it (you use raw SQL).
  • Optimistic: no locks. Remember a version you read; when saving, update only if the row still has that version. If not, someone else changed it: fail loudly.

Optimistic concurrency is the default recommendation for web apps, because edits are separated by network round trips and human delays.

Concurrency tokens

A concurrency token is a property EF Core includes in the WHERE clause of UPDATE and DELETE:

C#
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = null!;      // SQL Server rowversion
}

SQL Server's rowversion changes automatically on every update, so you never maintain it. EF Core generates:

SQL
UPDATE Products SET Price = @p0
WHERE Id = @p1 AND RowVersion = @p2;
-- then checks: how many rows were affected?

If the row changed, the WHERE no longer matches, zero rows are affected, and EF Core throws DbUpdateConcurrencyException instead of overwriting.

Other ways to declare a token:

C#
modelBuilder.Entity<Product>().Property(p => p.RowVersion).IsRowVersion();       // same as [Timestamp]
modelBuilder.Entity<Product>().Property(p => p.Price).IsConcurrencyToken();       // any property, compared by value
modelBuilder.Entity<Product>().UseXminAsConcurrencyToken();                       // PostgreSQL (Npgsql): hidden xmin column

Databases without an automatic row version (SQLite, MySQL variants) use a regular Guid or int token that you change in code (or an UpdatedAt timestamp) on every save. Prefer a database-maintained version where available.

Here is the check implemented in plain C#, with the same "affected rows = 0 means conflict" logic:

C# Stand-in: version check on update

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Catching the conflict

C#
try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    foreach (var entry in ex.Entries)
    {
        var proposed = entry.CurrentValues;                  // what this user tried to save
        var original = entry.OriginalValues;                 // what this user originally read
        var database = await entry.GetDatabaseValuesAsync(); // what the row looks like NOW (null if deleted)

        if (database is null)
        {
            // someone deleted the row
        }
        else
        {
            // decide how to resolve (below)
        }
    }
}

GetDatabaseValuesAsync() runs a SELECT and returns the current row; null means the row was deleted meanwhile.

Resolution strategies

You have three classic choices:

  1. Database wins: discard the user's changes, show the current values, let the user try again. await entry.ReloadAsync();
  2. Client wins: keep the user's values and overwrite. Set the original values to the database's, then save again:
    C#
    entry.OriginalValues.SetValues(database);   // pretend we read the latest version
    await db.SaveChangesAsync();                // now the version check passes
    
  3. Merge: keep each property from whichever side changed it. Typically property by property:
    C#
    foreach (var prop in proposed.Properties)
    {
        var originalValue = original[prop];
        var databaseValue = database[prop];
        var proposedValue = proposed[prop];
    
        // If the user did not change it, take the database value
        if (Equals(proposedValue, originalValue)) proposed[prop] = databaseValue;
    }
    entry.OriginalValues.SetValues(database);
    

Which is right depends on the data. For a user-edited document, show a conflict screen. For counters, do not use optimistic concurrency at all: use an atomic update (bulk operations: SET Stock = Stock - 1 WHERE Stock > 0).

Let us run the merge strategy: Alice changed the stock, Bob the price, and the merge keeps both.

C# Stand-in: merging two users' changes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Retry loops

A common pattern for small conflicts is a bounded retry:

C#
for (int attempt = 1; ; attempt++)
{
    try
    {
        var product = await db.Products.FindAsync(id);
        product!.Stock -= qty;
        await db.SaveChangesAsync();
        break;
    }
    catch (DbUpdateConcurrencyException) when (attempt < 3)
    {
        db.ChangeTracker.Clear();      // forget stale state, reload on the next loop
    }
}

Clearing the tracker matters: otherwise FindAsync returns the stale tracked instance and you retry the same failing update.

Web applications and the version on the form

In a typical MVC/API flow the entity is disconnected (add, update, remove): the version must travel with the form (a hidden field, an ETag/If-Match header) and be restored as the original value before saving:

C#
var product = await db.Products.FindAsync(dto.Id);
db.Entry(product!).Property(p => p.RowVersion).OriginalValue = dto.RowVersion;   // value the user saw
product.Price = dto.Price;
await db.SaveChangesAsync();      // WHERE RowVersion = (what the user saw)

Without this step the check would compare against the freshly loaded version and never detect the conflict. In APIs, return 409 Conflict (or 412 Precondition Failed for If-Match) when the exception is thrown.

sequenceDiagram
    participant A as Alice
    participant B as Bob
    participant D as Database
    A->>D: read row (v1)
    B->>D: read row (v1)
    A->>D: UPDATE ... WHERE v=1
    D-->>A: 1 row, now v2
    B->>D: UPDATE ... WHERE v=1
    D-->>B: 0 rows -> DbUpdateConcurrencyException

Bob's update matches no row because the version moved on.

Common mistakes

  • No token at all. Without a concurrency token, last write wins silently.
  • Not round-tripping the version in disconnected scenarios.
  • Retrying without clearing stale state, so every retry fails the same way.
  • Using optimistic concurrency for hot counters. Use atomic SQL or a queue.
  • Swallowing the exception. Always tell the user (or retry deliberately).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How does optimistic concurrency detect a conflict?

    If the version in the WHERE clause no longer matches, no row is updated, and EF Core throws DbUpdateConcurrencyException.
  2. 2. Which attribute marks a SQL Server rowversion property?

    [Timestamp] (or IsRowVersion in the Fluent API) maps to a database-maintained row version.
  3. 3. In a web form scenario, what must you do with the posted version?

    The check must compare against the version the user saw, not the freshly loaded one.
  4. 4. What does entry.GetDatabaseValuesAsync() return when the row was deleted?

    null signals that the row no longer exists.
  5. 5. Which resolution keeps the other user's changes and discards yours?

    Database wins reloads the current values and drops your proposed changes.

Summary

  • Optimistic concurrency detects lost updates without locks, using a version or token in the WHERE clause.
  • Use [Timestamp]/IsRowVersion where the database maintains a version; otherwise a Guid or other token you change yourself.
  • Catch DbUpdateConcurrencyException and choose database wins, client wins or merge, using GetDatabaseValuesAsync.
  • In web apps round-trip the version and use it as the original value; return 409 from APIs.
  • Atomic SQL updates suit counters better than optimistic checks.

Next: Pessimistic locking and isolation levels