EntityFrameworkCore.Locking.PostgreSQL 0.1.0

EntityFrameworkCore.Locking

Pessimistic locking for EF Core. Supports PostgreSQL, MySQL, and SQL Server.

• Row-level locks — ForUpdate() / ForShare() LINQ extension methods scoped to a transaction
• Distributed locks — AcquireDistributedLockAsync() session-scoped advisory locks, no transaction required

Installation

dotnet add package EntityFrameworkCore.Locking.PostgreSQL  # Npgsql
dotnet add package EntityFrameworkCore.Locking.MySql       # Pomelo
dotnet add package EntityFrameworkCore.Locking.SqlServer   # Microsoft.Data.SqlClient

Setup

Call .UseLocking() after your provider's Use*() call:

// PostgreSQL
services.AddDbContext<AppDbContext>(o =>
    o.UseNpgsql(connectionString)
     .UseLocking());

// MySQL
services.AddDbContext<AppDbContext>(o =>
    o.UseMySql(connectionString, serverVersion)
     .UseLocking());

// SQL Server
services.AddDbContext<AppDbContext>(o =>
    o.UseSqlServer(connectionString)
     .UseLocking());

Usage

All locking queries require an active transaction.

await using var tx = await ctx.Database.BeginTransactionAsync();

// Basic exclusive lock (FOR UPDATE / WITH (UPDLOCK, HOLDLOCK, ROWLOCK))
var product = await ctx.Products
    .Where(p => p.Id == id)
    .ForUpdate()
    .FirstOrDefaultAsync();

// Skip rows already locked by another transaction
var available = await ctx.Products
    .Where(p => p.Status == "pending")
    .ForUpdate(LockBehavior.SkipLocked)
    .ToListAsync();

// Fail immediately if lock cannot be acquired
var row = await ctx.Products
    .Where(p => p.Id == id)
    .ForUpdate(LockBehavior.NoWait)
    .FirstOrDefaultAsync();

// Wait up to 500ms for the lock
var row = await ctx.Products
    .Where(p => p.Id == id)
    .ForUpdate(LockBehavior.Wait, TimeSpan.FromMilliseconds(500))
    .FirstOrDefaultAsync();

// Shared lock (PostgreSQL and MySQL only)
var row = await ctx.Products
    .Where(p => p.Id == id)
    .ForShare()
    .FirstOrDefaultAsync();

await tx.CommitAsync();

PostgreSQL-only: ForNoKeyUpdate and ForKeyShare

These modes are available when using the EntityFrameworkCore.Locking.PostgreSQL package:

// FOR NO KEY UPDATE — blocks writers but allows FOR KEY SHARE (FK lookups)
var row = await ctx.Products
    .Where(p => p.Id == id)
    .ForNoKeyUpdate()
    .FirstOrDefaultAsync();

// FOR KEY SHARE — minimal shared lock, only blocks FOR UPDATE
// Useful for FK-referencing queries that should not block non-key updates
var row = await ctx.Products
    .Where(p => p.Id == id)
    .ForKeyShare()
    .FirstOrDefaultAsync();

Include with locking (PostgreSQL)

PostgreSQL automatically scopes the lock to the root table when a collection Include is present (emits FOR UPDATE OF "t"), so you can use Include directly without AsSplitQuery():

// Works — FOR UPDATE OF "p" is emitted automatically
var product = await ctx.Products
    .Include(p => p.OrderLines)
    .Where(p => p.Id == id)
    .ForUpdate()
    .FirstOrDefaultAsync();

Queue processing pattern

A common use of ForUpdate(LockBehavior.SkipLocked) is a worker queue where multiple consumers race to claim items:

await using var tx = await ctx.Database.BeginTransactionAsync();

var item = await ctx.Jobs
    .Where(j => j.Status == "pending")
    .OrderBy(j => j.CreatedAt)
    .ForUpdate(LockBehavior.SkipLocked)
    .FirstOrDefaultAsync();

if (item is null)
    return; // all items claimed by other workers

item.Status = "processing";
await ctx.SaveChangesAsync();
await tx.CommitAsync();

Distributed locks

Distributed (advisory) locks let you coordinate across processes without tying the lock to a database row or transaction. They are session-scoped — the lock is held until you dispose the handle, or until the connection drops.

No transaction is required.

// Acquire — blocks until available (optional timeout)
await using var handle = await ctx.AcquireDistributedLockAsync("invoice:generate");
// ... critical section ...
// lock released automatically on dispose

// With a timeout — throws LockTimeoutException if not acquired within 5 s
await using var handle = await ctx.AcquireDistributedLockAsync(
    "report:daily", TimeSpan.FromSeconds(5));

// With ASP.NET request cancellation
await using var handle = await ctx.AcquireDistributedLockAsync(
    "report:daily", timeout: null, cancellationToken: ct);

// TryAcquire — returns null immediately if already held
var handle = await ctx.TryAcquireDistributedLockAsync("invoice:generate");
if (handle is null)
    return Results.Conflict("Another process is generating the invoice.");
await using (handle) { /* critical section */ }

// Synchronous variants are also available
using var handle = ctx.AcquireDistributedLock("report:daily");
var handle = ctx.TryAcquireDistributedLock("report:daily");

// Check support at runtime
if (ctx.SupportsDistributedLocks()) { ... }

Lock keys

Keys are plain strings, up to 255 characters. The library handles provider-specific encoding internally:

• PostgreSQL — hashed to a bigint via XxHash32 with a namespace prefix ("EFLK"); the hash is computed in-process so no extra round-trip is needed.
• MySQL — passed as-is for keys ≤ 64 UTF-8 bytes; longer keys are SHA-256 hashed to lock:<hex58> (64 chars). The lock: prefix is reserved.
• SQL Server — passed as-is (max 255 chars, enforced upstream).

Provider-specific behavior

MySQL timeout precision: GET_LOCK timeout is in whole seconds. Sub-second timeouts are rounded up to 1 second.

Cancellation caveat: advisory lock SQL is a blocking database call. Cancellation sends a cancel signal to the driver; if the driver does not honor it before the timeout fires, the call completes via timeout. Always combine a timeout with the CancellationToken for bounded waits.

Exception handling

try
{
    await using var handle = await ctx.AcquireDistributedLockAsync(
        "report:daily", TimeSpan.FromSeconds(5));
}
catch (LockTimeoutException)
{
    // Not acquired within the timeout
}
catch (LockAlreadyHeldException ex)
{
    // Same DbContext + connection attempted to acquire the same key twice
    // ex.Key contains the key name
}
catch (LockingConfigurationException)
{
    // Provider does not support distributed locks, or UseLocking() was not called
}

LockAlreadyHeldException is thrown synchronously before any database call when the same (DbContext, connection, key) triple is already registered. Acquiring the same key from two different DbContext instances on different connections will block (or return null for TryAcquire) as expected.

Lock modes and behaviors

Exception handling

Lock failures throw typed exceptions from EntityFrameworkCore.Locking.Exceptions:

try
{
    var row = await ctx.Products
        .Where(p => p.Id == id)
        .ForUpdate(LockBehavior.NoWait)
        .FirstOrDefaultAsync();
}
catch (LockTimeoutException ex)
{
    // Lock could not be acquired (NOWAIT or timeout exceeded)
}
catch (DeadlockException ex)
{
    // Deadlock detected — retry the transaction
}
catch (LockingConfigurationException ex)
{
    // Programmer error: missing transaction, unsupported query shape,
    // or unsupported lock mode for this provider
}

Exception hierarchy:

• LockingException (base)
  • LockAcquisitionFailedException
    • LockTimeoutException — timeout or NOWAIT failure
    • DeadlockException — deadlock victim
    • LockAlreadyHeldException — same key acquired twice on the same connection (distributed locks)
  • LockingConfigurationException — programmer error (missing transaction, unsupported query shape, provider not configured)

Provider limitations

ForNoKeyUpdate and ForKeyShare are PostgreSQL-only extension methods available when the EntityFrameworkCore.Locking.PostgreSQL package is installed. Using ForShare on SQL Server throws LockingConfigurationException.

SQL Server SkipLocked limitation: SQL Server uses WITH (UPDLOCK, ROWLOCK, READPAST) instead of SKIP LOCKED. READPAST only skips rows held under row-level or page-level locks — rows under a table-level lock are blocked rather than skipped. For typical queue-processing workloads this behaves identically to SKIP LOCKED on PostgreSQL/MySQL.

MySQL timeout precision: MySQL's innodb_lock_wait_timeout is in whole seconds. Sub-second timeouts are rounded up to 1 second.

Unsupported query shapes

UNION, EXCEPT, INTERSECT with locking throw LockingConfigurationException at query execution time. Use per-query locks on individual queries before combining results.

AsSplitQuery() combined with locking throws LockingConfigurationException — use regular Include() instead (on PostgreSQL, FOR UPDATE OF is emitted automatically to handle outer joins).

Supported database versions

Target frameworks

net8.0, net9.0, net10.0

License

MIT