Concurrency in EF Core: Tokens, rowversion and DbUpdateConcurrencyException

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: How Many Books Are Left in Stock?

We start with the plainest possible Book entity and context. There is no concurrency configuration yet:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }
}

public class BookstoreContext : DbContext
{
    public DbSet<Book> Books => Set<Book>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=bookstore.db");
}

There are 5 copies of Dune in stock. Alice and Bob open the product page at the same time; two separate requests, two separate contexts. Alice buys 2, Bob buys 1:

using var alice = new BookstoreContext();
using var bob = new BookstoreContext();

var aliceBook = await alice.Books.SingleAsync(b => b.Title == "Dune");
var bobBook = await bob.Books.SingleAsync(b => b.Title == "Dune"); // both saw Stock = 5

aliceBook.Stock -= 2; // Alice buys 2
await alice.SaveChangesAsync();

bobBook.Stock -= 1; // Bob buys 1
await bob.SaveChangesAsync();

using var check = new BookstoreContext();
Console.WriteLine($"Stock: {(await check.Books.SingleAsync()).Stock}");

A total of 3 books were sold, so 2 should be left in stock. What does it print? Make your guess, then read on.

Lost Update: The Last Writer Wins

The output is the same on EF Core 6 and EF Core 10:

Stock: 4

Both SaveChanges calls ran without errors and Alice's sale disappeared. Bob's context had read the book while the stock was 5. When Bob subtracted 1 and wrote 4, he overwrote the 3 that Alice had written in the meantime. This bug is called a lost update. The transactions from the previous post do not help here: each SaveChanges is perfectly atomic on its own; the problem lies between the two. From the database's point of view nothing is wrong; UPDATE "Books" SET "Stock" = 4 WHERE "Id" = 1 is a perfectly valid command.

There are two classic answers to this. Pessimistic concurrency locks the row while reading; Bob waits until Alice is done. Locks are expensive, they can freeze the application if held too long, and EF Core has no ready-made API for them. Optimistic concurrency assumes "conflicts are rare": nobody gets locked, but at write time it checks "has this row changed since I read it?". If it has, the write is rejected and the application decides what to do. This is the approach EF Core supports.

Optimistic Concurrency and Concurrency Tokens

All you need to do is tell EF Core which property to check for changes. Such a property is called a concurrency token. With a data annotation:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }

    [ConcurrencyCheck]
    public int Stock { get; set; }
}

The same setting with the Fluent API:

modelBuilder.Entity<Book>()
    .Property(b => b.Stock)
    .IsConcurrencyToken();

We run the same Alice and Bob scenario with this model. Bob's SaveChanges is now inside a try block:

try
{
    await bob.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    Console.WriteLine(ex.Message);
}

The EF Core 10 log contains two UPDATEs, and they are identical. Here is one of them:

Executed DbCommand (0ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (DbType = Int32), @p2='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
UPDATE "Books" SET "Stock" = @p0
WHERE "Id" = @p1 AND "Stock" = @p2
RETURNING 1;

The difference is in the WHERE line. EF Core no longer looks only at Id; with "Stock" = @p2 it also requires the token's value from the moment it was read, its original value. Alice's command ran while the stock was 5 and updated one row. Bob's command also went out with the condition Stock = 5, but the value in the row is now 3. No row matched, the affected row count came back as 0, and EF Core threw this exception:

The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See https://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

On EF Core 6 the message is the same; only the link starts with http, and the SQL has SELECT changes(); instead of RETURNING 1. The heart of the mechanism is this piece of information: "fewer rows were affected than I expected". If the row had been deleted, you would get the same exception.

Making a single property such as stock the token only catches conflicts on that property. If Bob had changed the price instead of the stock, his save would have succeeded despite Alice's stock update. Most of the time what you want is "tell me if anything in the row changed". For that, the row needs a version column that changes on every update.

rowversion on SQL Server

SQL Server does this on its own. A column of type rowversion is incremented automatically by the database every time the row is updated. In EF Core you mark it with the [Timestamp] attribute; the Fluent API equivalent is .IsRowVersion():

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public int Stock { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = null!;
}

We looked at the DDL the SQL Server provider generates with Database.GenerateCreateScript(). No running SQL Server is needed for that; the model produces the script:

CREATE TABLE [Books] (
    [Id] int NOT NULL IDENTITY,
    [Title] nvarchar(max) NOT NULL,
    [Stock] int NOT NULL,
    [RowVersion] rowversion NOT NULL,
    CONSTRAINT [PK_Books] PRIMARY KEY ([Id])
);

This column is both a concurrency token and generated by the database. EF Core adds RowVersion to the WHERE clause of every UPDATE and reads the new value back in the same command. Your own code never needs to touch this property. If you want to try SQL Server, you can use the mcr.microsoft.com/mssql/server image with Docker.

What happens if you move the same model to SQLite? We tried it:

SQLite Error 19: 'NOT NULL constraint failed: Books.RowVersion'.

SQLite has no type like rowversion. EF Core creates the column as a plain BLOB NOT NULL and, assuming the database will generate the value, leaves it out of the INSERT. Nobody generates it, so the very first insert fails. On SQLite, the version has to be generated by the application.

A Version Column on SQLite: A Guid Token

The simplest approach is to make a Guid property the concurrency token and assign a new value on every save. To make sure we never forget the assignment, we override SaveChangesAsync:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }
    public Guid Version { get; set; }
}

public class BookstoreContext : DbContext
{
    public DbSet<Book> Books => Set<Book>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=bookstore.db");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>()
            .Property(b => b.Version)
            .IsConcurrencyToken();
    }

    public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        foreach (var entry in ChangeTracker.Entries<Book>())
        {
            if (entry.State is EntityState.Added or EntityState.Modified)
                entry.Entity.Version = Guid.NewGuid(); // a new version on every write
        }

        return base.SaveChangesAsync(cancellationToken);
    }
}

With this model, a SaveChanges that updates the stock produces this SQL on EF Core 10:

Executed DbCommand (0ms) [Parameters=[@p2='?' (DbType = Int32), @p0='?' (DbType = Int32), @p1='?' (DbType = Guid), @p3='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
UPDATE "Books" SET "Stock" = @p0, "Version" = @p1
WHERE "Id" = @p2 AND "Version" = @p3
RETURNING 1;

The new version goes into SET, the old one into WHERE. Now, whichever column of the row changes, the version changes too, and anyone holding the old version gets their write rejected. Instead of a Guid you can also use an int incremented by one each time; the idea is the same. Here we did the assignment in an override; in the interceptors post we will do the same job in a cleaner place. One warning: this override only covers SaveChangesAsync. If the code also uses the synchronous SaveChanges, you have to override that as well.

Resolving DbUpdateConcurrencyException

Catching the exception is half the job. The real question is: what now? ex.Entries gives you the EntityEntry objects of the conflicting entities. Each entry has three sets of values: CurrentValues (what you want to write), OriginalValues (what you read) and the current state in the database, which you can fetch with GetDatabaseValuesAsync(). The resolution strategies differ in how they combine these three.

Store wins: you give up your own changes and load the current state.

// store wins: drop your changes, load the current state
await entry.ReloadAsync();

Client wins: you set the original values to the database state. Since the token is now current, the second SaveChanges goes through and overwrites with your values. This means choosing the lost update on purpose; use it only if you know what you are doing.

// client wins: refresh the token, write your own values again
var databaseValues = await entry.GetDatabaseValuesAsync();
entry.OriginalValues.SetValues(databaseValues!);
await bob.SaveChangesAsync();

For a business rule like stock, both are wrong. The right thing is to run the rule again with fresh data: if the stock is still enough, subtract again; if not, reject the sale. Let's do that with a small retry loop:

public static async Task BuyAsync(BookstoreContext db, Book book, int quantity)
{
    for (var attempt = 1; ; attempt++)
    {
        if (book.Stock < quantity)
            throw new InvalidOperationException($"Not enough stock for {book.Title}");

        book.Stock -= quantity;
        try
        {
            await db.SaveChangesAsync();
            return;
        }
        catch (DbUpdateConcurrencyException ex) when (attempt < 3)
        {
            var entry = ex.Entries.Single();
            var databaseValues = await entry.GetDatabaseValuesAsync();
            if (databaseValues is null)
                throw new InvalidOperationException($"{book.Title} was deleted");

            Console.WriteLine($"Conflict on attempt {attempt}, stock in database: {databaseValues["Stock"]}");

            // make the database state both the original and current values, then apply the rule again
            entry.OriginalValues.SetValues(databaseValues);
            entry.CurrentValues.SetValues(databaseValues);
        }
    }
}

We run Alice and Bob again with this method. Bob still starts with stale data:

using var alice = new BookstoreContext();
using var bob = new BookstoreContext();

var aliceBook = await alice.Books.SingleAsync(b => b.Title == "Dune");
var bobBook = await bob.Books.SingleAsync(b => b.Title == "Dune");

await BuyAsync(alice, aliceBook, 2);
await BuyAsync(bob, bobBook, 1); // starts with stale data

using var check = new BookstoreContext();
Console.WriteLine($"Stock: {(await check.Books.SingleAsync()).Stock}");

Conflict on attempt 1, stock in database: 3
Stock: 2

Bob's first attempt conflicted, the loop took the 3 from the database, applied the rule again and wrote 2 on the second attempt. If databaseValues comes back null, the row was deleted in the meantime; retrying makes no sense then. The when (attempt < 3) filter is insurance against an endless loop. If all three attempts conflict on a very busy product, the exception bubbles up and the user has to be told to try again.

The API Scenario: The Token Comes from the Client

In a Web API things are a little different. When the user opens the form, the book arrives with a GET; five minutes later it is sent back with a PUT. Between these two requests the context has long been disposed. If you read the book again in the PUT request, the token's original value is the freshly read value, and you never notice the change in between. The fix is for the client to send back the version it saw, and for you to set it as the original value:

public record UpdatePriceRequest(decimal Price, Guid Version);

public static async Task UpdatePriceAsync(BookstoreContext db, int id, UpdatePriceRequest request)
{
    var book = await db.Books.SingleAsync(b => b.Id == id);

    book.Price = request.Price;
    db.Entry(book).Property(b => b.Version).OriginalValue = request.Version; // the version the client saw

    await db.SaveChangesAsync();
}

If someone buys the book after the user has opened the form:

Guid versionSeenByClient;
using (var db = new BookstoreContext())
    versionSeenByClient = (await db.Books.SingleAsync()).Version; // GET /books/1

using (var db = new BookstoreContext())
{
    var book = await db.Books.SingleAsync();
    book.Stock -= 1; // meanwhile someone buys a copy
    await db.SaveChangesAsync();
}

try
{
    using var db = new BookstoreContext();
    await UpdatePriceAsync(db, 1, new UpdatePriceRequest(14.9m, versionSeenByClient)); // PUT /books/1
}
catch (DbUpdateConcurrencyException)
{
    Console.WriteLine("409 Conflict: the book was changed by someone else");
}

409 Conflict: the book was changed by someone else

The critical line is OriginalValue = request.Version. Remove it and the same flow runs without error, silently saving a decision the user made while looking at an outdated screen. In the language of the ASP.NET Core Web API series, turning this exception into a 409 Conflict is the job of the controller or the global error handling. The version travels to the client in the DTO; on the HTTP side you can build the same idea with the ETag and If-Match headers.

Common Pitfalls

Catch order. DbUpdateConcurrencyException derives from DbUpdateException. If you write a catch for DbUpdateException first, the concurrency block is never reached. Order them from specific to general.

Raw SQL skips the token. An UPDATE written with ExecuteSqlRaw does not change the Guid version, whereas a SQL Server rowversion changes in every case. If you generate the token in the application, you have to update it yourself on every write path that bypasses the change tracker.

DateTime as a token. Making a "last updated at" column the token looks tempting, but two writes arriving in the same millisecond, or the precision of the database, can make it miss a conflict. A Guid or an incrementing number is safer.

Using the context as is after the exception. The conflicting entry is still Modified and still carries the old token. If you call SaveChanges again without resolving it, you get the same exception again. Either fix the values as shown above or drop the context and start over with a new one.

A Small Experiment

★☆☆ In the Guid-versioned model, have Alice delete the book right before Bob's BuyAsync call. What message does Bob stop with? Which line in the code catches this path?

★★☆ In the Guid-versioned model, have Bob change the price instead of the stock. Is the conflict still caught? Repeat the same experiment with the model where only Stock is the token and explain the difference.

★★★ Rewrite BuyAsync for a cart with several books: ex.Entries can return more than one entry. Produce an error message containing the title of whichever book ran out of stock, and retry for the books that still have enough.

So far we have done all our reads and writes through LINQ and the change tracker. But sometimes you have SQL that EF Core did not generate: an existing view, a database-specific function or a stored procedure that has been running for years. In the next post we will look at FromSqlRaw, FromSqlInterpolated, ExecuteSqlRaw and SQL injection.