Optimistic vs. Pessimistic Concurrency in EF Core (with Table Hints)

Question

Many experienced developers are familiar with database transactions but often overlook the key differences between optimistic and pessimistic concurrency control in Entity Framework Core (EF Core).

Understanding both approaches and how EF Core handles concurrency tokens is essential for building scalable and safe data operations, especially in systems with high transaction rates.

Concurrency control determines how multiple users or processes interact with the same data simultaneously. While optimistic concurrency is the default in EF Core, pessimistic concurrency is often misunderstood, misused, or completely ignored even by seniors engineers.

Related Read: Understanding and Using Table Hints in SQL Server

You can find the full code example at:
https://github.com/stevsharp/EfCoreExamples/tree/Efcore-Concurrency

What Is a Concurrency Token?

A concurrency token is a property that EF Core watches to detect whether a row has been modified since it was loaded. It's typically used in optimistic concurrency scenarios.

EF Core uses this token during SaveChangesAsync() to generate a WHERE clause that checks if the value is unchanged , if it has changed, a DbUpdateConcurrencyException is thrown.

How to Mark a Property as a Concurrency Token

✅ Using [ConcurrencyCheck] Attribute:

public class Product
{
    public int Id { get; set; }

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

✅ Using [Timestamp] (for SQL Server rowversion column):

[Timestamp]
public byte[] RowVersion { get; set; }

✅ Fluent Configuration:

modelBuilder.Entity<Product>()
    .Property(p => p.Stock)
    .IsConcurrencyToken();

You can use any column as a concurrency token — even logical fields like LastModified, but (aka ) is the most reliable for SQL Server.

1. Optimistic Concurrency Control

Optimistic concurrency assumes conflicts are rare, so it doesn’t lock any data during updates. Instead, it tracks changes using concurrency tokens and throws an exception if a conflict is detected during SaveChangesAsync().

✅ When to Use

Read-heavy apps with occasional writes (e.g., blog, e-commerce frontend)

Systems where retries are acceptable

How to Implement in EF Core with a Concurrency Token

Entity:

public class Product
{
    public int Id { get; set; }
    public int Stock { get; set; }

    [Timestamp] // This is the concurrency token
    public byte[] RowVersion { get; set; }
}

Save and Handle Concurrency:

var product = await context.Products.FindAsync(1);

if (product.Stock > 0)
{
    product.Stock -= 1;

    try
    {
        await context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        var databaseValues = await ex.Entries.Single().GetDatabaseValuesAsync();
        ex.Entries.Single().OriginalValues.SetValues(databaseValues);
    }
}

1. Pessimistic Concurrency Control

Pessimistic concurrency prevents conflicts altogether by locking the rows being read or modified. This ensures no one else can change the data until the transaction is complete.

❗ Important: EF Core does not support table hints like `` in LINQ. These must be applied using raw SQL.

Related Read: Understanding and Using Table Hints in SQL Server

Example: Locking Bank Account Row

using (var context = new AppDbContext())
{
    using var transaction = await context.Database.BeginTransactionAsync();

    var account = await context.Accounts
        .FromSqlRaw("SELECT * FROM Accounts WITH (UPDLOCK, ROWLOCK) WHERE Id = {0}", 1)
        .AsTracking() // Ensures the entity is tracked for updates
        .FirstOrDefaultAsync();

    if (account != null)
    {
        account.Balance -= 100;

        await context.SaveChangesAsync();
    }

    await transaction.CommitAsync();

}

Why SaveChangesAsync() Alone Is Not Always Enough

When using FromSqlRaw(), EF Core may not track the entity correctly for updates unless explicitly instructed.

Using .AsTracking() ensures EF will detect changes.

Without it, you would need to manually mark the entity as modified:

context.Entry(account).State = EntityState.Modified;

✅ Final Thoughts

Concurrency tokens (like RowVersion) play a central role in optimistic concurrency, enabling EF Core to detect conflicts without using locks.

Optimistic concurrency is ideal for most applications where data collisions are rare and performance is key.

Pessimistic concurrency is essential in financial or high-risk systems, where consistency outweighs performance.

EF Core does not support table hints like ` in LINQ, but you can **leverage them via **`, with .AsTracking() to ensure updates are recognized.

Many developers — even experienced ones — underestimate how concurrency should be managed in EF Core. The wrong approach can result in subtle bugs, lost data, or race conditions in production.

References & Further Reading

Microsoft Docs – Handling Concurrency Conflicts in EF Core

Microsoft Docs – Concurrency Tokens

Dev.to – Understanding and Using Table Hints in SQL Server

Microsoft Docs – Raw SQL Queries in EF Core

SQL Server – Table Hints in Transact-SQL

Comments

Great read! How do you handle retries when optimistic concurrency fails—simple re-fetch or a more advanced approach? Also when you post same article on other platforms please give canonical url to original source... thanks...

---

EF Core supports a built-in retry mechanism for transient failures:

options.UseSqlServer(connectionString, sqlOptions =>
{
    sqlOptions.EnableRetryOnFailure(
        maxRetryCount: 5,
        maxRetryDelay: TimeSpan.FromSeconds(10),
        errorNumbersToAdd: null
    );
});

This handles issues like deadlocks, timeouts, or connection drops, but not concurrency conflicts.

Alternatively, you can use Polly to handle custom retry logic, such as optimistic concurrency failures:

var retryPolicy = Policy
    .Handle<DbUpdateConcurrencyException>()
    .Retry(3, (exception, retryCount) =>
    {
        // custom logic like logging or reapplying changes
    });

retryPolicy.Execute(() =>
{
    context.SaveChanges();
});

---

Thanks Spyros for explanation :-)

---

you're welcome .. Let me know if you’d like me to dive deeper into any of these!