EntityFrameworkCore.Locking.MySql 0.4.0

EntityFrameworkCore.Locking

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

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

Installation

dotnet add package EntityFrameworkCore.Locking.PostgreSQL
dotnet add package EntityFrameworkCore.Locking.MySql
dotnet add package EntityFrameworkCore.Locking.SqlServer

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());

Row-level locks

All row-level locking queries require an active transaction.

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

// 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 the 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 modes

Available when referencing EntityFrameworkCore.Locking.PostgreSQL:

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

// FOR KEY SHARE — minimal shared lock, only blocks FOR UPDATE
await ctx.Products.Where(p => p.Id == id).ForKeyShare().FirstOrDefaultAsync();

Include with locking

On PostgreSQL, collection Include emits FOR UPDATE OF "t" automatically to handle the outer join, so you can use Include directly without AsSplitQuery():

var product = await ctx.Products
    .Include(p => p.OrderLines)
    .Where(p => p.Id == id)
    .ForUpdate()
    .FirstOrDefaultAsync();

Queue processing

ForUpdate(LockBehavior.SkipLocked) is the standard building block for 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();

Lock modes and behaviors

Distributed locks

Advisory locks coordinate across processes without tying the lock to a row or transaction. They are session-scoped — held until the handle is disposed or the connection drops.

// Blocks until available
await using var handle = await ctx.Database.AcquireDistributedLockAsync("invoice:generate");

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

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

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

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

// Runtime check
if (ctx.Database.SupportsDistributedLocks()) { ... }

Lock keys

Keys are plain strings, up to 255 characters. Provider-specific encoding is handled internally:

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

Provider-specific behavior

Advisory lock SQL is a blocking database call. Cancellation is a best-effort signal to the driver; if the driver does not honor it before the timeout fires, the call completes via timeout. Combine a timeout with the CancellationToken for bounded waits.

Exceptions

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

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.

Exception hierarchy:

• LockingException
  • LockAcquisitionFailedException
    • LockTimeoutException
    • DeadlockException
    • LockAlreadyHeldException
  • LockingConfigurationException

Provider support

SQL Server uses WITH (UPDLOCK, ROWLOCK, READPAST) for SkipLocked — READPAST skips rows held under row-level or page-level locks, but rows under a table-level lock are blocked rather than skipped.

MySQL's innodb_lock_wait_timeout is in whole seconds, so sub-second timeouts are rounded up to 1 second.

Unsupported query shapes

The following query shapes throw LockingConfigurationException at execution time:

• UNION / EXCEPT / INTERSECT / CONCAT — lock individual queries before combining results
• AsSplitQuery() — use regular Include() instead (PostgreSQL emits FOR UPDATE OF automatically for outer joins)
• Distinct() — not compatible with row-level locking on any supported database
• CountAsync() / LongCountAsync() / SumAsync() / MaxAsync() / MinAsync() — aggregate terminal operations are rejected because the result is a scalar, not a set of lockable rows; use AnyAsync() if you want to test for row existence with a lock

Explicit joins (LINQ join syntax, SelectMany), correlated subqueries (Any, Contains), Where+OrderBy+Take pagination, and all Include / ThenInclude shapes work correctly across all providers.

Limitations

The following scenarios are not detected at build or execution time:

Supported database versions

Target frameworks

net8.0, net9.0, net10.0

Benchmarks

The benchmarks/ directory contains BenchmarkDotNet benchmarks measuring the overhead added by the SQL generator and interceptor.

dotnet run -c Release --project benchmarks/EntityFrameworkCore.Locking.Benchmarks -- --version=<x.y.z>

--version is required and labels the results folder (results/v<x.y.z>/). Additional BenchmarkDotNet arguments (e.g. --filter '*SqlGeneration*') can be appended.

License

MIT