Reyrb.Database.ForUpdate 10.0.0

Reyrb.Database.ForUpdate

PostgreSQL row locking for EF Core 10 / .NET 10, adapted from the tag-based PostgresQueryExtensions and SelectForUpdateCommandInterceptor in lootbucks-backend.

Register once when configuring your context:

using Microsoft.EntityFrameworkCore;
using Reyrb.Database.ForUpdate;

services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(connectionString).UseForUpdate());

Alternatively, register new SelectForUpdateCommandInterceptor() with EF Core's AddInterceptors. The interceptor is stateless and supports pooled contexts.

Keep the read and subsequent update in the same explicit transaction:

await using var transaction = await context.Database.BeginTransactionAsync(cancellationToken);

var wallet = await context.Wallets
    .Where(x => x.Id == walletId)
    .ForUpdate()
    .SingleAsync(cancellationToken);

wallet.Balance -= amount;
await context.SaveChangesAsync(cancellationToken);
await transaction.CommitAsync(cancellationToken);

Use .ForUpdate(skipLocked: true) (or .ForUpdate(true)) to omit rows locked by another transaction, for example when claiming queued work. Locks last until commit or rollback; without an explicit transaction they end with the statement.

Both synchronous and asynchronous reads are supported. The extension adds the FOR UPDATE or FOR UPDATE SKIP LOCKED tag; the interceptor appends the SQL clause when executing the command. Other TagWith tags can appear before or after it. If both lock tags occur, SKIP LOCKED takes precedence. Repeated interception does not duplicate the clause. ToQueryString() shows the tag, not the final clause.

Include(...).ForUpdate().ThenInclude(...) chaining is supported, but PostgreSQL's SQL restrictions still apply. This is a command-level suffix, with no OF table targeting: joins may lock multiple tables, and nullable outer joins, aggregates, DISTINCT, grouping, and set operations may reject FOR UPDATE. For complex loads, lock the root rows with a simple query first, then load related data in the same transaction. It is not a general SQL rewriter for raw multi-statement batches. Do not use these tags with ExecuteUpdate or ExecuteDelete.

Use a PostgreSQL provider such as Npgsql.EntityFrameworkCore.PostgreSQL. SQL Server, SQLite, and the EF in-memory provider are not supported for row locking. The library depends only on EF Core Relational; install your provider separately.

Tests require Docker and run against an isolated PostgreSQL 16.3 container:

dotnet test Reyrb.Database.ForUpdate.Tests --configuration Release