Coordinated Migration

How BackWave serializes concurrent migrators so a fleet cold-booting with auto-migrate cannot race when it applies the schema, on by default, with one opt-out.

A single process applying the schema is a solved problem. The migrator runs its idempotent scripts and the tables appear. The hard case is a fleet. When several application instances cold-boot at the same moment and every one of them is allowed to migrate, they all reach for the same schema at once. BackWave closes that window automatically: a genuine first-boot migration is serialized so exactly one instance applies the schema and the rest wait and no-op. This page covers the race it prevents, the one option that controls it, and what to expect operationally. It is the companion to Schema & Migrations, which covers how the schema gets provisioned in the first place.

The race this closes#

The schema scripts are idempotent. They guard their creates and inserts so re-applying a current schema changes nothing. That is enough for "run it on every deploy," but it is not enough for two instances applying a fresh schema at the exact same instant. IF NOT EXISTS reads the catalog and then creates, and those two steps are not atomic against a concurrent session doing the same thing. In that narrow window Postgres can still throw a duplicate-type error and SQL Server a duplicate-object error, even though both statements were written to be idempotent.

So the assumption that "idempotent scripts are enough" was true for sequential re-runs and false for a genuine concurrent first migration. Before coordination, nothing serialized the migrators, so a fleet booting together with AutoMigrate on had every instance racing into that window. The scope of this feature is exactly that window (the concurrent first-migration case) and nothing wider. Once the schema is current, coordination has no work to do.

Because the coordination lives inside the migrator itself, every path that migrates is covered: the lazy auto-migrate path the store takes on first use, and the explicit MigrateAsync call an operator runs from a deploy pipeline. No instance is elected to migrate; whichever one gets there first does, and the others fall in behind it.

On by default, and atomic#

Coordination is automatic. A user who does nothing gets it. There is one thing to know that pairs with it: the whole migration now runs as a single transaction, so it is all-or-nothing. A fault partway through rolls back every statement and leaves no half-applied schema behind. A waiting instance that then takes over finds either a complete schema or none, never a partial one.

The one option#

Every adapter's options record carries a single bool CoordinateMigration. The name and the default are identical across all three:

It only has an effect when AutoMigrate is on. If the store is not migrating for you, there is nothing to coordinate, and this option does nothing. Recall that AutoMigrate itself defaults to false on all three adapters, since production is expected to migrate as a deliberate deploy step. So the pairing that matters is AutoMigrate = true with CoordinateMigration left at its default: turn on auto-migrate for a fleet and it is coordinated for you without a second knob.

services.AddBackWave(b => b
    .UseStore(new PostgresJobStore(new PostgresStoreOptions
    {
        ConnectionString = "Host=localhost;Database=app;Username=app;Password=secret",
        AutoMigrate = true,          // let the store apply the schema on first use
        // CoordinateMigration = true is the default; a whole fleet can boot at once
    }))
    .UseJobs(BackWaveJobs.Module));

The explicit deploy path is coordinated too#

Running the migrator yourself from a pipeline is also safe by default. Each static MigrateAsync takes a bool coordinate = true parameter, so the deploy-step path carries the same guarantee as the auto-migrate path. You can run it from more than one job concurrently and exactly one will apply the schema.

await using var dataSource = NpgsqlDataSource.Create(connectionString);
await PostgresMigrator.MigrateAsync(dataSource);        // coordinated by default
// SQL Server: await SqlServerMigrator.MigrateAsync(connectionString);
// SQLite:     await SqliteMigrator.MigrateAsync("Data Source=app.db");

If you name the schema explicitly and also pass a cancellation token, pass the token by name (MigrateAsync(dataSource, "jobs", cancellationToken: token)). The coordinate parameter sits between the schema name and the token, so a token supplied positionally as the third argument would bind to the wrong parameter.

How each adapter coordinates#

All three follow the same shape and differ only in the primitive they use to hold the line. For the networked adapters that primitive is a database-backed distributed lock, so the serialization holds across every instance in the fleet, not just within one process. First, an unlocked pre-check reads the deployed schema version without taking any lock; if the schema is already at the current version, the migrator returns immediately and never contends for the lock at all. Only a missing or stale schema goes further: it takes the lock, re-reads the version inside the lock, and if some other instance migrated while it was waiting, it finds the schema current and no-ops. If it is still not current, it applies the scripts and commits, which releases the lock. That double check is what lets a queued instance wake up and do nothing rather than re-run a migration that already happened.

A few properties hold across all three:

• The lock is transaction-scoped, so a crash cannot leak it. If the instance holding it dies mid-run, its connection drops, the transaction rolls back, the lock releases, and a waiter takes over against a clean, un-migrated schema.
• A second instance blocks, then re-checks and no-ops. It waits on the lock rather than erroring, and when it wakes it finds the schema current and does no work.
• There is no artificial timeout. A waiter blocks until the lock frees or its cancellation token trips. Host shutdown cancels the wait and surfaces an OperationCanceledException. There is deliberately no fixed lock-wait deadline: a bounded timeout would only turn a legitimately slow migration into a spurious fleet-wide startup failure gated on a number no one can pick correctly. SQLite is the one mechanical exception (it waits on the write lock under a 30-second busy timeout), but a real migration is fast, so a co-resident boot blocks briefly, re-checks, and moves on rather than surfacing a busy error.

Because independent schemas key the lock by their own name, two BackWave deployments sharing one database do not contend with each other. Only instances migrating the same schema serialize.

SQLite coordinates per host, not across hosts#

SQLite's write lock is a property of the file, so its coordination reaches every process and connection that shares that file on one host, and no further. There is no distributed SQLite, so there is nothing to coordinate across hosts. CoordinateMigration exists on SqliteStoreOptions for symmetry with the networked adapters, not to imply a cross-host guarantee it cannot make.

Turning it off is a footgun#

Setting CoordinateMigration = false restores the old behavior: the migrator applies the schema with no lock and no pre-check, which reopens the concurrent-first-migration race and the duplicate-object errors that come with it. It is only safe when something else already serializes migration: a single dedicated migration job that runs before the fleet, or one instance that is guaranteed to boot and migrate before the rest come up. If you cannot point to that external serialization, leave the default in place. There is no scenario where turning it off makes a concurrent boot safer.

What to expect operationally#

The library emits nothing new here. There is no log line, no progress output, and no keepalive during a lock wait. A waiting instance simply blocks silently until the lock frees or the host shuts down. In practice, during a genuine first-boot migration a booting instance can appear to pause at startup while it waits its turn. That pause is the lock wait, not a hang.

Steady state is quiet. Once the schema is current, every boot hits only the unlocked pre-check, sees the current version, and returns without ever taking the lock. Rolling deploys and restarts against an already-migrated database add no contention and no latency from coordination.

SQL Server needs ALTER DATABASE rights on first boot#

SQL Server has one first-boot step worth planning for. The very first migration enables read-committed snapshot isolation on the target database, which is a database-level change that briefly needs exclusive access. The login that runs the first migration therefore needs rights to alter the database. This is guarded and idempotent: it runs at most once, and every later boot finds the setting already on and skips it entirely. If your migration runs under a least-privilege login, make sure that first run has the rights to alter the database, or apply the schema once with a login that does.

Under a very large simultaneous cold connect (many instances opening their first connection to a brand-new SQL Server at once) you may also see transient login rejections during the TLS handshake burst. These are safe to retry and do not indicate an auth or coordination problem; a real booting instance retries and proceeds.

Coordination and the version check are separate guards#

Coordination decides who applies the schema safely. It does not replace the startup version check, which is unchanged and still runs. After migration, or instead of it when the store is not migrating, every store calls VerifySchemaVersionAsync and fail-stops with an InvalidOperationException if the schema is missing or carries a version the adapter was not built for. One guard keeps concurrent migrators from corrupting the schema on the way in; the other keeps a skewed schema from ever being trusted. They are independent, and you get both.

Where to go next#

• Schema & Migrations for how the schema is provisioned, the AutoMigrate option, and running the migrator yourself.
• Postgres, SQL Server, and SQLite for the full per-adapter setup, including connection strings and options.
• Fail-stop for the policy behind refusing to run against a skewed schema.

Found a problem on this page? Report an issue