Pessimistic Locking in EF Core C# with PostgreSQL FOR UPDATE

// Pessimistic Locking in EF Core C# with PostgreSQL FOR UPDATE
//
// PURPOSE
// A pessimistic reservation acquires a database lock before checking and changing stock. This
// prevents two writers from both approving a reservation from the same pre-update quantity.
//
// EF Core tracks entities and saves changes, but tracking alone is not a concurrency lock. This
// PostgreSQL example explicitly combines a transaction with a parameterized SELECT FOR UPDATE.
//
// METHOD
// Create a fresh DbContext, begin a READ COMMITTED transaction, acquire the row lock, check
// availability, update the tracked entity, save, and commit.
//
// FromSql receives an interpolated FormattableString, so the ID is sent as a database parameter.
// Never build a user-controlled SQL string and pass it to FromSqlRaw.
//
// The locking query is enumerated with ToListAsync before further LINQ is applied. The primary
// key guarantees at most one row. AsTracking makes the intended update behavior explicit.
//
// A second conflicting reservation waits, then checks the committed row. Normal PostgreSQL
// snapshot reads can continue. Exceptions or cancellation dispose the uncommitted transaction
// and release its locks.
//
// COMPLEXITY
// Use short transactions and an indexed lookup. Do not perform network calls while holding the
// lock. Consistent lock ordering reduces deadlock risk when an operation touches several
// records.
//
// For a single arithmetic stock decrement, a conditional update can require fewer round trips.
// Pessimistic locking is most useful when several business checks or related writes must be
// coordinated.
//
// The sample does not enable automatic provider retries. If an application enables an execution
// strategy with retries, the whole explicit transaction must be executed as one strategy unit
// and its business effects must be safe to repeat.
//
// CONTRACT
// Runnable provider: PostgreSQL with Npgsql.EntityFrameworkCore.PostgreSQL 10.x and a net10.0
// application. The SQL is intentionally provider-specific. EF Core does not expose a
// provider-neutral LINQ FOR UPDATE operation.
//
// quantity must be positive. A missing ID raises KeyNotFoundException, insufficient stock raises
// a domain exception, and a database check constraint prevents negative values.
//
// The PostgreSQL lock timeout is local to the transaction. Lock waits and deadlocks must be
// classified using the provider exception, not treated as optimistic
// DbUpdateConcurrencyException conflicts.
//
// SQL Server uses different mechanisms, such as UPDLOCK held in an explicit transaction.
// HOLDLOCK adds serializable semantics for the referenced table; ROWLOCK is a hint, not a
// guarantee against escalation. Do not copy PostgreSQL FOR UPDATE SQL into SQL Server.
//
// Do not use EF InMemory or SQLite tests as evidence that PostgreSQL row locking works. Tests
// need separate contexts and connections against the actual provider.
//
// LANGUAGE DETAILS
// IDbContextFactory gives each attempt its own context. A DbContext cannot be shared across
// concurrent operations. Await every EF operation and propagate CancellationToken throughout the
// database work.
//
// The model maps an explicit table name and nonnegative-stock constraint. In a real application
// generate and apply EF migrations; EnsureCreated is used only by the accompanying disposable
// test databases.
//
// EXAMPLE
// Create a net10.0 project, add Npgsql.EntityFrameworkCore.PostgreSQL 10.x, and copy the
// implementation below into a C# source file. Configure AddDbContextFactory<InventoryDb>(options
// => options.UseNpgsql(connectionString)) and inject Reservations.
//
// Usage: remaining = await reservations.ReserveAsync(id: 1, quantity: 1, ct). From available=2
// the result is 1. Keep credentials in configuration rather than source control.
//
// The real-database tests verify an actual lock wait, serialization of two concurrent
// reservations, rollback after insufficient stock, missing IDs, invalid quantities,
// cancellation, and enforcement of the database constraint.
//
// Download the complete examples, dependency lock file, and integration tests:
// https://www.dimkoug.com/static/downloads/locking-examples-tested.zip
//
// SOURCES
// EF Core SQL queries and parameterization:
// https://learn.microsoft.com/en-us/ef/core/querying/sql-queries
//
// EF Core transactions: https://learn.microsoft.com/en-us/ef/core/saving/transactions
//
// PostgreSQL explicit locking: https://www.postgresql.org/docs/current/explicit-locking.html
//
// SQL Server table hints:
// https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table
//
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace LockingPosts.Pessimistic;

public sealed class Stock
{
    public int Id { get; set; }
    public int Available { get; set; }
}

public sealed class InventoryDb : DbContext
{
    public InventoryDb(DbContextOptions<InventoryDb> options) : base(options) { }
    public DbSet<Stock> Stocks => Set<Stock>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Stock>().ToTable("LockingPessimisticStocks", table =>
            table.HasCheckConstraint("ck_pess_stock_nonnegative", "\"Available\" >= 0"));
    }
}

public sealed class InsufficientStockException : InvalidOperationException
{
    public InsufficientStockException() : base("Not enough stock.") { }
}

public sealed class Reservations
{
    private readonly IDbContextFactory<InventoryDb> factory;
    public Reservations(IDbContextFactory<InventoryDb> factory) => this.factory = factory;

    public async Task<int> ReserveAsync(int id, int quantity, CancellationToken ct = default)
    {
        ArgumentOutOfRangeException.ThrowIfNegativeOrZero(quantity);
        await using var db = await factory.CreateDbContextAsync(ct);
        await using var tx = await db.Database.BeginTransactionAsync(
            IsolationLevel.ReadCommitted, ct);

        await db.Database.ExecuteSqlRawAsync("SET LOCAL lock_timeout = '3s'", ct);

        // FromSql receives a FormattableString: id becomes a SQL parameter.
        // Enumerate before doing further LINQ so FOR UPDATE stays explicit.
        var rows = await db.Stocks.FromSql($"""
            SELECT "Id", "Available"
            FROM "LockingPessimisticStocks"
            WHERE "Id" = {id}
            FOR UPDATE
            """).AsTracking().ToListAsync(ct);

        if (rows.Count == 0)
            throw new KeyNotFoundException("SKU not found.");

        var stock = rows[0];
        if (stock.Available < quantity)
            throw new InsufficientStockException();

        stock.Available -= quantity;
        await db.SaveChangesAsync(ct);
        await tx.CommitAsync(ct);
        return stock.Available;
    }
}