Handle concurrency conflicts with EF Core using application-managed tokens

[nanotaboada]

Description

To ensure data integrity and prevent lost updates when multiple clients modify the same player record, we need to implement optimistic concurrency control.

Since our app currently uses SQLite, which lacks automatic concurrency support (e.g., rowversion/timestamp), we’ll manage a concurrency token (GUID) ourselves in the application layer.

This prevents unintentional overwrites and aligns with best practices for API design in concurrent environments.

Proposed Solution

Introduce application-managed concurrency using a Version property (Guid) in the Player entity, marked as a concurrency token.

The client must supply the current version when issuing an update. If the version in the request does not match the database, a 409 Conflict response is returned, signaling the record has been modified elsewhere.

Suggested Approach

1. Add Version to the Player Model

public Guid Version { get; set; } = Guid.NewGuid();

2. Configure as a Concurrency Token

In PlayerDbContext.OnModelCreating():

modelBuilder.Entity<Player>()
    .Property(p => p.Version)
    .IsConcurrencyToken();

3. Add Version to the PlayerRequestModel

public class PlayerRequestModel
{
    public Guid Version { get; set; }
}

4. Update PlayerService.UpdateAsync

public async Task<bool> UpdateAsync(PlayerRequestModel playerRequestModel)
{
    var player = await playerRepository.FindBySquadNumberAsync(playerRequestModel.SquadNumber);
    if (player is null) return false;

    if (player.Version != playerRequestModel.Version)
        throw new ConcurrencyException("The player has been modified by another process.");

    mapper.Map(playerRequestModel, player);
    player.Version = Guid.NewGuid();

    try
    {
        await playerRepository.UpdateAsync(player);
        memoryCache.Remove(CacheKey_RetrieveAsync);
        return true;
    }
    catch (DbUpdateConcurrencyException)
    {
        throw new ConcurrencyException("A concurrency conflict occurred during update.");
    }
}

5. Add a Domain Exception

public class ConcurrencyException : Exception
{
    public ConcurrencyException(string message) : base(message) { }
}

6. Handle Conflict in PlayerController

[HttpPut("{squadNumber:int}")]
[ProducesResponseType(StatusCodes.Status204NoContent)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status409Conflict)]
public async Task<IResult> PutAsync(int squadNumber, PlayerRequestModel player)
{
    var validation = await validator.ValidateAsync(player);
    if (!validation.IsValid)
        return TypedResults.BadRequest(validation.Errors.Select(e => new { e.PropertyName, e.ErrorMessage }));

    if (await playerService.RetrieveBySquadNumberAsync(squadNumber) is null)
        return TypedResults.NotFound();

    try
    {
        var updated = await playerService.UpdateAsync(player);
        return updated ? TypedResults.NoContent() : TypedResults.NotFound();
    }
    catch (ConcurrencyException ex)
    {
        return TypedResults.Conflict(new { error = ex.Message });
    }
}

7. Update Repository

Update remains unchanged, but should optionally catch and wrap concurrency:

public async Task UpdateAsync(T entity)
{
    try
    {
        _dbSet.Update(entity);
        await dbContext.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        throw new ConcurrencyException("A concurrency conflict occurred.", ex);
    }
}

Acceptance Criteria

• Player entity includes a Version field (Guid)
• Version is marked as a concurrency token in EF Core
• PlayerRequestModel includes Version
• PUT /players/{squadNumber} fails with 409 Conflict if version mismatch
• New version is generated on successful update
• Concurrency errors are logged and handled cleanly
• Unit and/or integration tests verify concurrency logic