Neon.EntityFrameworkCore.Npgsql 4.0.20-preview.2

Neon.EntityFrameworkCore.Npgsql

Entity Framework Core migration locking that works on YugabyteDB and other Postgres wire compatible databases that don't implement LOCK TABLE.

You can get started here: Neon.EntityFrameworkCore.Npgsql

dotnet add package Neon.EntityFrameworkCore.Npgsql

Targets net10.0, Entity Framework Core 10 and Npgsql.EntityFrameworkCore.PostgreSQL 10.

The problem this solves

Any Database.Migrate() / Database.MigrateAsync() against YugabyteDB fails immediately after you upgrade to Entity Framework Core 9 or later:

Npgsql.PostgresException: 0A000: ACCESS EXCLUSIVE not supported yet

0A000 is feature_not_supported. Entity Framework Core 8 and earlier were fine.

Why. EF Core 9 added a database lock held across a migration run (IHistoryRepository.AcquireDatabaseLock), so two processes can't apply the same migration concurrently. The Npgsql provider implements it as a table lock:

LOCK TABLE "__EFMigrationsHistory" IN ACCESS EXCLUSIVE MODE

YugabyteDB doesn't implement explicit table locks and rejects the statement outright. There is no EF Core switch to disable the lock, so replacing IHistoryRepository is the only intervention point.

The fix

using Neon.EntityFrameworkCore.Npgsql;

services.AddDbContext<MyContext>(options =>
{
    options
        .UseNpgsql(connectionString)
        .UseAdvisoryLockMigrationHistory();     // <-- after UseNpgsql()
});

That's it. AdvisoryLockHistoryRepository takes a PostgreSQL transaction scoped advisory lock (pg_advisory_xact_lock) instead of locking the table. Everything else about the migrations history table — its schema, its name, how rows are read and written — is left to the Npgsql provider, so this doesn't disturb your model or your existing history rows. (dotnet ef migrations has-pending-model-changes reports no drift after applying it.)

YugabyteDB cluster prerequisite

Advisory locks are enabled by default from YugabyteDB 2025.0 onward — nothing to configure.

They arrived in 2.25 as a preview feature that was off by default. On those older releases both flags are required, on the master and the tserver:

--allowed_preview_flags_csv=ysql_yb_enable_advisory_locks
--ysql_yb_enable_advisory_locks=true

Without them pg_advisory_xact_lock() fails rather than doing nothing, so the symptom changes from "ACCESS EXCLUSIVE not supported yet" to "advisory locks not yet implemented". Check that before concluding this package is broken.

YugabyteDB also has a yb_silence_advisory_locks_not_supported_error setting that downgrades that failure to a silent no-op. Don't use it here. It converts a loud misconfiguration into a silent loss of mutual exclusion, which is the one outcome this package exists to avoid.

Requires YugabyteDB 2.25 or later.

Keep it opt-in

On real PostgreSQL the provider's own LOCK TABLE implementation works and is stricter, so leave it in place there. Don't fold UseAdvisoryLockMigrationHistory() into a shared "apply our defaults" helper — apply it only to contexts that actually target a database lacking explicit table locks.

Configuration

The defaults mirror the LOCK TABLE statement being replaced: a derived lock key, and an indefinite wait. Override them when you have a reason to.

options
    .UseNpgsql(connectionString)
    .UseAdvisoryLockMigrationHistory(lock =>
    {
        lock.Timeout      = TimeSpan.FromMinutes(5);
        lock.PollInterval = TimeSpan.FromSeconds(2);
        lock.LockKey      = 0x4d494752_41544521;
    });

When to set Timeout. pg_advisory_xact_lock() blocks in the server indefinitely, which is the conservative default but a poor experience in CI: a migrator stuck behind a stalled peer looks exactly like a migrator stuck for any other reason. A timeout turns that into a diagnosable MigrationsLockTimeoutException. In an orchestrated deployment the platform will restart the job anyway, and by then the other migrator has usually finished and this one finds nothing to do.

MigrationsLockTimeoutException derives from Exception, not TimeoutException — deliberately. Npgsql's transient error detector classifies every TimeoutException as retryable, so EF Core's execution strategy re-threw it as InvalidOperationException and left catch (MigrationsLockTimeoutException) silently dead. Deriving from Exception keeps it non-transient, so it reaches you intact.

When to set LockKey. Advisory locks live in a single cluster wide 64-bit key space that isn't tied to any table, so exclusion depends entirely on independent processes computing the same number. Set it explicitly to make two contexts with different history tables serialize against each other, or to move off a key that collides with an advisory lock your application already uses. A collision only ever causes excess serialization, never lost exclusion.

Whatever key you choose must be stable across processes, machines and releases. Never derive one from string.GetHashCode() — .NET randomizes string hashing per process, so two migrators would compute different keys, fail to exclude each other, and never tell you. That's why the built-in derivation is a hand rolled FNV-1a hash.

Design notes

These are the decisions that are easy to get wrong, recorded because getting them wrong fails silently.

Advisory lock, not a no-op. A no-op IMigrationsDatabaseLock is a two line fix and restores the EF Core 8 behaviour. But it discards the protection the lock exists for: nothing stops two migrator pods applying the same migration concurrently. Yugabyte supports advisory locks, so there's no reason to give that up.

pg_advisory_xact_lock, not pg_advisory_lock. A transaction scoped lock only works if a transaction is already open when it's taken — and one always is. EF Core's migrator runs Open() → BeginTransaction() → then AcquireDatabaseLock(). (The LOCK TABLE statement being replaced depends on the same thing: PostgreSQL rejects LOCK TABLE outside a transaction block.) Given that, transaction scope is strictly better: EF Core owns the lifetime, so there's no pg_advisory_unlock() to get wrong, no connection pinned open, and nothing leaked if the process is killed mid-migration.

The reacquire condition is load bearing. A session lock survives a commit and only needs retaking on a new connection; a transaction lock dies at every commit and needs retaking on every new transaction. EF Core calls ReacquireIfNeeded(connectionReopened, transactionRestarted) once per migration, so keying only on connectionReopened would leave a multi-migration run silently unprotected after the first commit — and would still pass every test that applies a single migration.

Known limitations

• Transaction-suppressing migrations get no protection. With no transaction to own the lock, it releases as soon as the acquiring statement autocommits. This isn't a regression: the LOCK TABLE statement being replaced would have failed outright with 25P01 in the same situation.
• The key derives from the configured schema, defaulting to public. A deployment that relies on search_path to resolve the history table into another schema will have two sessions serialize on the same key while using different tables. That over-serializes (safe) rather than under-serializing (not safe). Set LockKey if you'd rather they didn't.

Maintenance

AdvisoryLockHistoryRepository derives from NpgsqlHistoryRepository, an internal provider type in a .Internal namespace (hence the EF1001 suppression). Its shape isn't covered by semantic versioning, which couples this package to the Npgsql provider's major version. Two things contain that cost:

• The provider package is pinned to a single major version in Directory.Packages.props.
• Test.Neon.EntityFrameworkCore carries guard tests that fail loudly if any of the overridden members change shape, so a provider upgrade surfaces as a red test rather than a runtime surprise.

OpenTelemetry tracing

Lock acquisition is traced, which is how you confirm the mechanism is actually doing its job — particularly that two migrators computed the same key and that one of them queued.

builder.Services.AddOpenTelemetry()
    .WithTracing(tracing =>
    {
        tracing.AddNeonEntityFrameworkCore()          // Neon.EntityFrameworkCore
               .AddNeonEntityFrameworkCoreNpgsql()    // this package
               .AddNpgsql()                           // Npgsql client spans (optional)
               .AddOtlpExporter();
    });

Each package has its own ActivitySource, per the OpenTelemetry convention of one source per instrumentation library, so you subscribe to the ones you use and each reports its own version.

Migrations usually run in a short lived job rather than in your service, so remember to configure a tracer provider in the migrator too — otherwise the spans this exists to give you are never collected. Short lived processes should also dispose or flush the provider before exit so the final batch is exported.

Activity emitted

AcquireDatabaseLock (Client), displayed as LOCK <history table>.

Names are exposed as constants on NpgsqlTraceTags and, for the convention tags, Neon.EntityFrameworkCore.TraceTags. Failures are recorded with Activity.AddException() and the span status is set to Error.

Verification

Test.Neon.EntityFrameworkCore runs migrations against a live YugabyteDB instance (via YugabyteFixture), so the claims above are tested rather than asserted:

• The problem is real. Without this package, MigrateAsync() fails with 0A000 on current Yugabyte — so the passing tests below can't be passing vacuously.
• Migrations apply, and re-running is a no-op that records each migration exactly once.
• A migrator waits for a held lock. The test takes the lock itself, and a migrator with a Timeout set fails with MigrationsLockTimeoutException rather than proceeding — then succeeds once the lock is released, which is what proves the lock caused the failure.
• It's the derived key that's actually taken. Holding a different key blocks nothing.
• The lock is released when the run ends — no pg_advisory_unlock() needed, because the transaction owns it.
• The lock survives the commit boundary. With two pending migrations, a competitor is still locked out while the second one runs. This is the case that catches a broken reacquire condition; every other test here applies migrations from a standing start and would pass without it.
• An explicit LockKey excludes just as the derived one does.

The concurrency tests contain no timed waits — the migrator is parked on a second advisory lock the test controls, and every wait is on observable state bounded by a CancellationTokenSource. The commit-boundary test has been checked in both directions: breaking the reacquire condition to connectionReopened alone makes it, and only it, fail.

Documentation

Full API documentation: https://sdk.neonforge.com

Further reading

• YugabyteDB explicit locking
• Enabling advisory locks in YugabyteDB (forum)
• PostgreSQL advisory lock functions
• EF Core Migrator — lock/transaction ordering: dotnet/efcore, src/EFCore.Relational/Migrations/Internal/Migrator.cs
• Npgsql history repository — the LOCK TABLE: npgsql/efcore.pg, src/EFCore.PG/Migrations/Internal/NpgsqlHistoryRepository.cs

Copyright © 2005-2024 by NEONFORGE LLC. All rights reserved.