Skip to content

Concurrency Pattern

Optimistic concurrency control using PostgreSQL's xmin system column.

Overview

Prevents lost updates when multiple users edit the same entity:

sequenceDiagram
    participant U1 as User 1
    participant U2 as User 2
    participant DB as Database

    U1->>DB: GET marker (version: 100)
    U2->>DB: GET marker (version: 100)

    U1->>DB: UPDATE (version: 100)
    DB-->>U1: Success (version: 101)

    U2->>DB: UPDATE (version: 100)
    DB-->>U2: 409 Conflict (version mismatch)

PostgreSQL xmin

xmin is a system column that changes on every row update. We use it as a version token.

Implementation

Entity Configuration

public void Configure(EntityTypeBuilder<Marker> builder)
{
    builder.Property<uint>("Version")
        .HasColumnName("xmin")
        .HasColumnType("xid")
        .ValueGeneratedOnAddOrUpdate()
        .IsConcurrencyToken();
}

Request Model

public sealed class UpdateMarkerRequest
{
    public required string Title { get; init; }
    public required uint Version { get; init; }  // From previous GET
}

Handler

public async Task<MarkerResponse> Handle(UpdateMarkerCommand request, CancellationToken ct)
{
    var marker = await context.Markers.FindAsync(request.MarkerId);

    // Set the expected version
    context.Entry(marker).Property("Version").OriginalValue = request.Request.Version;

    marker.Title = request.Request.Title;

    try
    {
        await context.SaveChangesAsync(ct);
    }
    catch (DbUpdateConcurrencyException)
    {
        throw new ConcurrencyException("Marker was modified by another user");
    }

    return marker.ToResponse();
}

Frontend Handling

const updateMarker = useMutation({
  mutationFn: (data) => api.updateMarker(data),
  onError: (error) => {
    if (error.code === 'CONCURRENCY_CONFLICT') {
      // Refresh data and show conflict message
      queryClient.invalidateQueries(['markers']);
      toast.error('Data was modified. Please review and try again.');
    }
  },
  meta: { invalidateOnConflict: [markerKeys.all] }
});

Entities Using Concurrency

Entity Concurrency
Marker Yes
Note Yes
NoteItem Yes
Transaction Yes
RecurringTransaction Yes
Category Yes