Optimistic Locking vs Pessimistic Locking in .NET

Since the invention of database systems, they have become the primary source of truth for almost every web application’s data. As user demands grow, applications scale, and so does the concurrency within the system. Ensuring data integrity and consistency along with effective locking mechanisms, becomes a major concern when multiple users attempt to access and modify the same resource simultaneously, potentially causing conflicts and overwriting each other’s data.

To handle these conflicts, developers have two main concurrency control mechanisms: pessimistic locking and optimistic locking. Each approach has advantages and disadvantages, and we will explore them in this article.

Without further ado, let’s dive in!

Prerequisites for Running the Demonstration

In this article, we’ll use Entity Framework Core as an abstraction over our data access and Testcontainers NuGet package to programmatically create an SQL Server database container. This setup allows us to demonstrate pessimistic and optimistic locking strategies in a realistic environment.

After configuring a SQL Server Test Container, we can then configure Entity Framework Core to connect to this SQL Server instance:

builder.Services
    .AddDbContext<ApplicationDbContext>((sp, contextBuilder) => 
    contextBuilder.UseSqlServer(connectionString), 
    contextLifetime: ServiceLifetime.Scoped);

With the DbContext configured, we need a simple DTO to handle requests to our API endpoints:

public record AssignWorkItemRequest(long Id, string AssignedTo, bool ForceConflict = false);

Finally, we need a tool such as Swagger, Postman, etc., to send requests to our API.

With our setup ready, we are now ready to demonstrate both pessimistic and optimistic locking.

Let’s begin!

What Is Pessimistic Locking?

Pessimistic locking ensures exclusive access to a resource by locking it so that only one process can modify it at a time, putting others in a halted state. This approach is useful when we expect multiple processes to modify the same resource concurrently. By locking the resource, we prevent other processes from accessing or modifying it until the initial process is completed. However, we must carefully manage these locks to avoid potential slowdowns or deadlocks in the system.

To demonstrate pessimistic locking in EF Core, we’ll use a simple WorkItem entity:

public class WorkItem
{
    public long Id { get; set; }

    public string Title { get; set; }

    public string Description { get; set; }

    public string Status { get; set; }

    public DateTime DueDate { get; set; }

    public string AssignedTo { get; set; }
}

The WorkItem class contains properties to represent a task, including an Id, a Title and Description for summarizing and detailing the task, a Status to indicate its current state, a DueDate for deadline tracking, and an AssignedTo field to identify the responsible person for it.

We can achieve pessimistic locking in EF Core by using an explicit transaction:

[HttpPost("/workItem/assign-pessimistic")]
public async Task<IActionResult> AssignWorkItemWithPessimisticLockAsync(
    AssignWorkItemRequest assignWorkItemRequest, 
    CancellationToken cancellationToken)
{
    using var transaction = await DbContext.Database
        .BeginTransactionAsync(System.Data.IsolationLevel.Serializable, cancellationToken);

    var workItem = await DbContext.WorkItems.
        FirstOrDefaultAsync(x => x.Id == assignWorkItemRequest.Id, cancellationToken);

    if (workItem is null)
        return NotFound($"Work Item with Id {assignWorkItemRequest.Id} was not found!");

    workItem.AssignedTo = assignWorkItemRequest.AssignedTo;

    try
    {
        await DbContext.SaveChangesAsync(cancellationToken);
        await transaction.CommitAsync(cancellationToken);

        return Ok("Work item updated successfully with pessimistic locking.");
    }
    catch (Exception ex)
    {
        await transaction.RollbackAsync(cancellationToken);

        Logger.LogError(ex, "Error while saving changes");

        return Problem("An error occurred while updating the resource.");
    }
}

We use a serializable transaction isolation level to ensure that no other processes can modify our entity while the current process accesses it. However, we should keep in mind that serializable transaction level can lock more rows than necessary, potentially impairing performance and increasing the risk of deadlocks.

To test this endpoint, let’s send a request to /workItem/assign-pessimistic with a valid payload:

{
  "id": 1,
  "assignedTo": "John Doe"
}

As expected we received a result to show pessimistic locking has been used:

Work item updated successfully with pessimistic locking.

These results indicate that the transaction updated our entity, ensuring no concurrent modifications occurred.

What Is Optimistic Locking?

Optimistic locking assumes multiple processes can access and modify a resource simultaneously without conflicts. Instead of placing an actual lock on the resource, it checks for changes only when saving the update. If another process modifies the resource, the application detects and handles the conflict, though this may result in data loss.

The concept is simple: in addition to the row’s unique ID, we include a version column – typically a number, GUID, or timestamp – that tracks the initial state of the record when we pull it from the database. Then, when we persist our changes, the update happens based on both the ID and the version. If the save does not update any rows, another process has modified the row and updated the version.

Optimistic Locking With RowVersion in EF Core

To demonstrate this, let’s extend our WorkItem entity to WorkItemAutoVersion:

public class WorkItemAutoVersion
{
    public long Id { get; set; }

    public string Title { get; set; }

    public string Description { get; set; }

    public string Status { get; set; }

    public DateTime DueDate { get; set; }

    public string AssignedTo { get; set; }

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

Here, we add a RowVersion property. This property is marked with the [Timestamp] attribute, enabling EF Core to handle concurrency checks automatically.

Alternatively, we can configure the RowVersion with EF Core’s Fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<WorkItemAutoVersion>().Property(p => p.RowVersion).IsRowVersion();
}

We can use either approach depending on our application’s configuration style.

To test our optimistic locking, let’s create a new endpoint that retrieves the WorkItemAutoVersion entity, updates the AssignedTo field, and saves the changes. If ForceConflict is set to true, a direct SQL update simulates a concurrency conflict by modifying the AssignedTo field in the database:

[HttpPost("/workItem/assign-optimistic-row-version")]
public async Task<IActionResult> AssignWorkItemWithAutomaticOptimicsticLockAsync(
    AssignWorkItemRequest assignWorkItemRequest,
    CancellationToken cancellationToken)
{
    var workItem = await DbContext.WorkItemsWithRowVersion
        .FirstOrDefaultAsync(x => x.Id == assignWorkItemRequest.Id, cancellationToken);

    if (workItem is null)
        return NotFound($"Work Item with Id {assignWorkItemRequest.Id} was not found!");

    workItem.AssignedTo = assignWorkItemRequest.AssignedTo;

    if (assignWorkItemRequest.ForceConflict)
    {
        _ = await DbContext.Database.ExecuteSqlInterpolatedAsync(
            $@"UPDATE dbo.WorkItemsWithRowVersion
               SET AssignedTo = 'John Stevens'
               WHERE Id = {assignWorkItemRequest.Id}", cancellationToken);
    }

    try
    {
        await DbContext.SaveChangesAsync(cancellationToken);

        return Ok("Work item updated successfully with optimistic locking.");
    }
    catch (DbUpdateConcurrencyException ex)
    {
        Logger.LogError(ex, "Error while saving changes");

        return Conflict("Resource was already modified. Please retry");
    }
}

Notice that we do not explicitly update the RowVersion property here – EF Core manages this automatically, thanks to the [Timestamp] attribute. This setup allows the database to handle versioning, which is efficient for most use cases.

Also, notice the DbUpdateConcurrencyException catch block. EF Core raises this exception when it detects a conflict, indicating that another process has modified the data.

To test this, let’s send a request to the /workItem/assign-optimistic-row-version with a valid payload for a successful update:

{
  "id": 2,
  "assignedTo": "John Newman",
  "forceConflict": false
}

As expected, the result shows that we have updated our record:

Work item updated successfully with optimistic locking.

Next, we simulate a concurrency conflict with a new payload:

{
  "id": 2,
  "assignedTo": "John Oldman",
  "forceConflict": true
}

Here we modify the assignedTo value and set forceConflict to true. This time, the response indicates a conflict:

Resource was already modified. Please retry

Additionally, we can also see a message logged in the console:

{"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."}

This message confirms that our optimistic locking is working as intended.

Optimistic Locking With ConcurrencyToken in EF Core

In some scenarios, we might want more control over the version control and manage it manually within our application. The ConcurrencyToken approach allows for this flexibility.

To demonstrate this we can modify our WorkItem entity further:

public class WorkItemWithConcurrencyToken
{
    public long Id { get; set; }

    public string Title { get; set; }

    public string Description { get; set; }

    public string Status { get; set; }

    public DateTime DueDate { get; set; }

    public string AssignedTo { get; set; }

    [ConcurrencyCheck]
    public long Version { get; set; }
}

Here, we add a Version property marked with [ConcurrencyCheck], which could also be an integer, GUID, or other unique value.

Alternatively, we can configure Version as a concurrency token using EF Core’s Fluent API:

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

Now, let’s create a new endpoint that retrieves the WorkItemWithConcurrencyToken entity, updates the AssignedTo field, and manually increments the Version property. If ForceConflict is set to true, a direct SQL update simulates a concurrency conflict:

[HttpPost("/workItem/assign-manual-optimistic-concurrency-token")]
public async Task<IActionResult> AssignWorkItemWithManualOptimicsticLockAsync(
    AssignWorkItemRequest assignWorkItemRequest,
    CancellationToken cancellationToken)
{
    var workItem = await DbContext.WorkItemsWithConcurrencyToken
        .FirstOrDefaultAsync(x => x.Id == assignWorkItemRequest.Id, cancellationToken);

    if (workItem is null)
        return NotFound($"Work Item with Id {assignWorkItemRequest.Id} was not found!");

    workItem.AssignedTo = assignWorkItemRequest.AssignedTo;
    workItem.Version++;

    if (assignWorkItemRequest.ForceConflict)
    {
        _ = await DbContext.Database.ExecuteSqlInterpolatedAsync(
            $@"UPDATE dbo.WorkItemsWithConcurrencyToken
               SET AssignedTo = 'John Stevens', Version = Version + 1
               WHERE Id = {assignWorkItemRequest.Id}", cancellationToken);
    }

    try
    {
        await DbContext.SaveChangesAsync(cancellationToken);

        return Ok("Work item updated successfully with optimistic locking.");
    }
    catch (DbUpdateConcurrencyException ex)
    {
        Logger.LogError(ex, "Error while saving changes");

        return Conflict("Resource was already modified. Please retry");
    }
}

This endpoint is similar to the previous one, with one key difference – we manually update the property here.

Similar to before, let’s first send a request to /workItem/assign-manual-optimistic-concurrency-tokenwith a valid payload:

{
  "id": 3,
  "assignedTo": "John Doe",
  "forceConflict": false
}

As expected, the result shows our request modifies the resource:

Work item updated successfully with optimistic locking.

To simulate concurrency conflict, let’s change the payload’s assignedTo and forceConflict to true and send a new request: 

Resource was already modified. Please retry

As before, EF Core detects the conflict and throws a DbUpdateConcurrencyException, which we handle in the catch block and the result confirms that our concurrency token works.

Additionally, with EF Core logging enabled, we’ll see details of the conflict in the logs, indicating that the Version column is checked for concurrency:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[@__assignWorkItemRequest_Id_0='?' (DbType = Int64)], CommandType='Text', Com
      SELECT TOP(1) [w].[Id], [w].[AssignedTo], [w].[Description], [w].[DueDate], [w].[Status], [w].[Title], [w].[Versio
      FROM [WorkItemsWithConcurrencyToken] AS [w]
      WHERE [w].[Id] = @__assignWorkItemRequest_Id_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (10ms) [Parameters=[@p0='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30']
      UPDATE dbo.WorkItemsWithConcurrencyToken
                         SET AssignedTo = 'John Stevens', Version = Version + 1
                         WHERE Id = @p0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[@p2='?' (DbType = Int64), @p0='?' (Size = 4000), @p1='?' (DbType = Int64), @
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      UPDATE [WorkItemsWithConcurrencyToken] SET [AssignedTo] = @p0, [Version] = @p1
      OUTPUT 1
      WHERE [Id] = @p2 AND [Version] = @p3;

The last log message indicates that both Id and Version are checked during the update, validating that our manual concurrency token is functioning correctly.

Optimistic vs Pessimistic Locking: Which One to Use?

Choosing the right locking mechanism depends on different factors – the specific needs of our application, how often conflicts occur, the level of concurrency, and the application’s performance. Let’s take a look at a comparison that helps us make an informed decision for the right locking strategy for our application:

Locking Strategy	Description	Best Use Case	Pros	Cons
Pessimistic Locking	Locks a resource immediately to prevent other processes from modifying it until the current transaction completes	High-conflict scenarios, e.g., financial systems, inventory management, booking systems, etc., where simultaneous updates are common	Ensures data integrity, consistency and prevents conflicts during transaction	Can cause performance bottlenecks, deadlocks and overall system slowness
Optimistic Locking with RowVersion	Adds a RowVersion column (usually a byte[] with a [Timestamp] attribute) that the database auto-increments on each update, allowing EF Core to detect conflicts	Low-conflict scenarios, where concurrent edits are infrequent, e.g. social media and CMS platforms, user profile updates, etc.	No blocking; high concurrency; database manages versioning	Requires handling conflicts in the application; Potential data loss on conflict
Optimistic Locking with ConcurrencyToken	Adds a custom version column (like int, GUID, etc.) managed in the application. Increments manually to detect conflicts and allows control over versioning	Scenarios needing more control over when versioning occurs, e.g., systems with collaborative editing, custom e-comerce platform, workflow systems, etc.	Customizable versioning control; no blocking	Requires manual version and conflic management in the application. Potential data loss on conflict

Conclusion

In this article, we explored pessimistic and optimistic locking mechanisms in EF Core, showing practical examples and scenarios where each is most suitable. Implementing these strategies helps maintain data consistency and prevents concurrency issues, allowing applications to handle multiple users and high data traffic without compromising data integrity. Choosing the appropriate locking mechanism ensures that, even under heavy load, our systems remain reliable, responsive, and resilient to conflicts.

So, is your application facing data conflicts, consistency issues, or performance bottlenecks? Are you using the correct locking strategies? Let’s take a fresh glimpse at our applications with what we’ve learned today to see if there’s something to improve. And don’t forget to come back for our next article!