SQLite migration lock `__EFMigrationsLock` table row not cleared when migration throws or is cancelled at certain times

[rowan-walsh]

Bug description

Migrate and MigrateAsync in src/EFCore.Relational/Migrations/Internal/Migrator.cs don't seem to release their database lock if an exception or cancellation happens outside of MigrateImplementation or MigrateImplementationAsync, like during creation of the history repository.

I observed this with SQLite, I suspect it only applies for providers with LockReleaseBehavior.Explicit.

With SQLite a failure to release the lock means later attempts to migrate see an existing row in the __EFMigrationsLock table and hang waiting indefinitely.

A potential fix could be a check to dispose state.DatabaseLock in the outer finally blocks of Migrate and MigrateAsync.

Your code

#:property PublishAot=false
#:package Microsoft.EntityFrameworkCore.Sqlite@10.0.12

using System.Data.Common;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;

const string databasePath = "ef-sqlite-migration-lock-repro.db";
File.Delete(databasePath);

using var cancellation = new CancellationTokenSource();
var options = new DbContextOptionsBuilder<ReproContext>()
    .UseSqlite($"Data Source={databasePath}")
    .AddInterceptors(new CancelAfterLockAcquiredInterceptor(cancellation))
    .Options;

try
{
    await using var context = new ReproContext(options);
    await context.Database.MigrateAsync(cancellation.Token);
    Console.Error.WriteLine("Migration unexpectedly completed without cancellation.");
    return 1;
}
catch (OperationCanceledException) when (cancellation.IsCancellationRequested)
{
    Console.WriteLine("MigrateAsync was cancelled immediately after acquiring the lock.");
}

await using var connection = new SqliteConnection($"Data Source={databasePath}");
await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandText = "SELECT COUNT(*) FROM \"__EFMigrationsLock\";";
var lockRowCount = (long)(await command.ExecuteScalarAsync())!;

Console.WriteLine($"Rows remaining in __EFMigrationsLock: {lockRowCount}");
return lockRowCount == 1 ? 0 : 1;

sealed class ReproContext(DbContextOptions<ReproContext> options) : DbContext(options);

sealed class CancelAfterLockAcquiredInterceptor(CancellationTokenSource cancellation)
    : DbCommandInterceptor
{
    public override ValueTask<object?> ScalarExecutedAsync(
        DbCommand command,
        CommandExecutedEventData eventData,
        object? result,
        CancellationToken cancellationToken = default
    )
    {
        if (command.CommandText.Contains("INSERT OR IGNORE INTO \"__EFMigrationsLock\""))
            cancellation.Cancel();

        return ValueTask.FromResult(result);
    }
}

[DbContext(typeof(ReproContext))]
[Migration("20260910000000_Repro")]
sealed class ReproMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder) =>
        migrationBuilder.CreateTable(
            name: "Repro",
            columns: table => new { Id = table.Column<int>(nullable: false) },
            constraints: table => table.PrimaryKey("PK_Repro", row => row.Id)
        );

    protected override void Down(MigrationBuilder migrationBuilder) =>
        migrationBuilder.DropTable(name: "Repro");
}

Stack traces

Verbose output

EF Core version

10.0.12

Database provider

Microsoft.EntityFrameworkCore.Sqlite

Target framework

.NET 10

Operating system

Windows 11

IDE

No response