Addressing Concurrency Conflicts in Entity Framework Core

You may have encountered a scenario where multiple users are trying to modify the same item at the same time, which will cause one of the two updates to be lost. 

In this post, we are going to see how we can address this scenario and look at the options available to us. 

0 - Preparing the system

Before we begin, I want to make a quick note. Throughout this course I have been using MySQL, but for this particular post I am going to use PostgreSQL. The reason is that we are going to look at concurrency conflicts, and for some reason I could not set it up correctly in MySQL.

The change I made was very simple: I modified the dockerfile, so that instead of downloading MySQL, it downloads Postgres. Both the user, password and the database have the same name. 

### code 0 
FROM postgres:latest

ENV POSTGRES_USER=cursoEFuser
ENV POSTGRES_PASSWORD cursoEFpass
ENV POSTGRES_DB cursoEF

• Note: for simplicity, I left the file paths in tools/myslq.

In the code I added the connection string, and as we saw in the post about connecting to multiple databases I only changed the statement in the startup file and the connection string in appsettings.

"PostgreSQLConnection":  "Server=127.0.0.1;Port=5432;Userid=cursoEFuser;Password=cursoEFpass;Database=cursoEF",

builder.Services.AddPostgreSql(builder.Configuration);

And finally, I deleted the migrations and ran it again. This is necessary because some data types are different between MySQL and Postgres.

1 - What is a concurrency conflict?

The first thing we need to understand is what a concurrency conflict is. Basically it happens when two operations try to modify the same data, and this is very common within databases. 

For example, if two users have permission to change the "email" value and one of them does, the second will either fail, or overwrite the change made by the first; but it will overwrite it without knowing it’s doing so. 

1.1 - Techniques for handling concurrency conflicts

1. Pessimistic Concurrency: Assumes that conflicts are probably the rule, not the exception. It locks the record as soon as a user starts editing it, preventing other users from modifying it.

2. Optimistic Concurrency: Assumes that conflicts are rare. It allows users to edit records without locking them and checks if there were conflicts when saving changes.

2 - Solution to concurrency problems with Entity Framework Core

Entity Framework Core provides a mechanism within its own library to very easily implement optimistic concurrency, which assumes that conflicts happen occasionally (which, in the majority of cases, is true).

This type of concurrency management does not lock the record, but instead checks whether the information has changed from the time you read the original data being modified to the time you save it, and if it's different, it fails.

This check happens during SaveChanges in the DbContext.

2.1 - Implementing concurrency validation in Entity Framework Core

Practical example, we have the following endpoint, where we update the email:

[HttpPut("concurrency/update-email/{id}")]
public async Task<bool> UpdateEmail(int id, string newEmail)
{
    User? user = await _unitOfWork.UserRepository.GetById(id);
    if (user != null)
    {
        //Sleep 10 seconds to be able to test the concurrency issue
        Thread.Sleep(10000);
        user.Email = newEmail;
        _unitOfWork.UserRepository.Update(user);
        await _unitOfWork.Save();
    }

    return true;
}

When we execute two calls at the same time, both results will be "correct," as both versions are saved. 

To avoid this situation, we must first specify in the entity which fields we want to check for changes. We do this using the attribute ConcurrencyCheck inside the entity to check, in our case, only for email. 

public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }
    
    [ConcurrencyCheck]
    [MaxLength(50)]
    public string Email { get; set; }
    
    public ICollection<Wokringexperience> Wokringexperiences { get; set; }
}

With this change, we indicate to the database that it should add the old email to the where clause of the update statement.

And if there are no records to update, it throws an exception.

• Note: This did not work as expected in MySQL.

Previously or alternatively, we could use the RowVersion column, which many of you may have seen, especially in older code; its effect was the same, and this column was either of type byte[] or timestamp; it had to be updated with each change.

In fact, our change could also be applied to that additional column, because with the ConcurrencyCheck attribute, we are only checking that the email doesn't change (you can put concurrency check on every column, it always depends on your use case).

If we decide to use ConcurrencyCheck on the RowVersion column, we must remember to update this column with each update, just as was done in the past with byte[] or timestamp.

In fact, I'm going to make that change. In our particular case, the property will go in the CursoEFBaseEntity<TId> class because we have centralized certain actions. 

public abstract class CursoEFBaseEntity<TId>
{
    public TId Id { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? DeletedTimeUtc { get; set; }
    [ConcurrencyCheck]
    public DateTime LastUpdateUtc { get; set; }
}

In my case, I set it as a DateTime, but as I said, it can be any type: long, Guid, etc.

And we modify the update method to update the column:

public abstract class GenericRepository<T, TId> : IGenericRepository<T, TId>
    where T : CursoEFBaseEntity<TId>
    where TId : IEquatable<TId>
{
    private readonly CursoEfContext _context;
    protected DbSet<T> Entities => _context.Set<T>();

    protected GenericRepository(CursoEfContext context)
    {
        _context = context;
    }

    public void Update(T entity)
    {
        entity.LastUpdateUtc = DateTime.UtcNow;
        _context.Set<T>().Update(entity);
    }

}

Now we can remove the ConcurrencyCheck from email and run migrations again.

Finally, if we test it, we see that the second time we make the call, we get an exception.

This is what is called a ConcurrencyException, and now it is up to you how you want to handle it. 

2.2 - Dealing with concurrency exceptions in Entity Framework Core

There are several ways to deal with concurrency conflicts: you either return an error, or retry a few times and then return an error.

To do this, we must handle the exception which in this case is DbUpdateConcurrencyException, and we need to do the try-catch in SaveChangesAsync

of the DbContext, which in our case is inside UnitOfWork.

public class UnitOfWork : IUnitOfWork
{
    public IUserRepository UserRepository { get; }
    public IWorkingExperienceRepository WorkingExperienceRepository { get; }
    private readonly CursoEfContext _context;

    public UnitOfWork(CursoEfContext context, IUserRepository userRepository, 
        IWorkingExperienceRepository workingExperienceRepository)
    {
        _context = context;
        UserRepository = userRepository;
        WorkingExperienceRepository = workingExperienceRepository;
    }

    public async Task<int> Save()
    {
        try
        {
            await _context.SaveChangesAsync();
        }
        catch (DbUpdateConcurrencyException concurrencyException)
        {
            Console.WriteLine("concurrency error");
            //Handle the conflict exception

        }
        return 0;
    }
 

    public void Dispose()
    {
        _context.Dispose();
    }
}

In this case, we simply ignore the error and return 0; it is up to the consumer of our interface to perform the necessary actions in the code. In some cases it will be to retry, in others to return an error. The important thing is that data is not unintentionally overwritten.