Efcore: DbContext.Database.Migrate() fails when called from second DbContext for the same Database.

[Givko]

I have the following scenario:

I have two projects(microservices) using one Database(MSSQL), however they have two DbContexts, one for each asp.net core project. Both projects have migration which we apply at runtime we have created an extension method for the IWebHost

using System;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace Service.Extensions.WebHost
{
    public static class WebHostExtension
    {
        public static IWebHost MigrateDbContext<TContext>(this IWebHost webHost, Action<TContext, IServiceProvider> seeder) 
            where TContext : DbContext
        {
            using (var scope = webHost.Services.CreateScope())
            {
                var services = scope.ServiceProvider;

                var logger = services.GetRequiredService<ILogger<TContext>>();

                var context = services.GetService<TContext>();

                try
                {
                    logger.LogInformation($"Migrating database associated with context {typeof(TContext).Name}");

                    context.Database.Migrate();

                    seeder(context, services);

                    logger.LogInformation($"Migrated database associated with context {typeof(TContext).Name}");
                }
                catch (Exception ex)
                {
                    logger.LogError(ex, $"An error occurred while migrating the database used on context {typeof(TContext).Name}");
                }
            }

            return webHost;
        }
    }
}

Which we call in the Program.cs:

public class Program
    {
        public static void Main(string[] args)
        {
            CreateHostBuilder(args)
                .MigrateDbContext<AppDbContext>()
                .Run();
        }

        public static IWebHost CreateHostBuilder(string[] args) =>
             WebHost.CreateDefaultBuilder(args)
                .UseUrls("http://*:8108;") //64295            
                .UseStartup<Startup>()
                .Build();
    }

We use it the exact same way in both Program.cs files for both projects.

The problem which we have is the following:

We use docker-compose.yml file to build the docker containers. Everything is fine when we call docker-compose build. When we call docker-compose up everything goes well until the migrations are applied.
When one of the Program.cs files is executed and the migrations start applying the other project gets an " Error: 18456, Severity: 14, State: 38." error, when trying to apply the migrations from the other context, stating that the credentials are valid but the database is unavailable. After everythinh is up only one of the two migrations has been applied, it just depends on which project has started applying first. Sometimes its one, another time is the other.

One thing that comes to my mind is that the Migrate() method locks the database and that is way when the other method tries to execute its migrations it gets the error. If this is the case could you please tell how can I cope with this issue. We want to apply the migrations at runtime, if possible. And cannot change the app to use one DbContext instead of two.

docker-compose.yml

version: '3.4'

services:
  service1:
    container_name: service1
    image: service1
    build:
      context: .
      dockerfile: service1/Dockerfile
    depends_on:
      - .db

  service2:
    container_name: service2
    image: service2     
    environment: 
      - ASPNETCORE_ENVIRONMENT=Development
    build:
      context: .
      dockerfile: service2/Dockerfile
    depends_on:
      - service1
      - db

  db:
    container_name: db
    image: microsoft/mssql-server-linux:latest
    environment:
      SA_PASSWORD: "password"
      ACCEPT_EULA: "Y"
      MSSQL_PID: "Express"
    ports:
      - "port:port"

Connectionstring is 100% correct on both projects as sometimes the migrations from accountsservice get applied and other times the migrations from bankservice are applied.

I forgot to say that running docker-compose up a second time applies the migrations from the project which did not apply its migrations due to the "Error: 18456, Severity: 14, State: 38." error.

Further technical details

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .net core 3.1 and .net core 2.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.3

[ajcvickers]

@Givko We recommend not applying migrations at runtime precisely because of this kind of issue. I would recommend that database deployment be done at application deployment time and coordinated so that it is only performed once.

If you do want to continue with the approach you have, then you'll need to implement some form of locking to ensure that the database is only migrated once. This kind of locking can be complicated and is not something that EF attempts to do automatically.

[Givko]

But what is the problem exactly does EF lock the DB when executing Migrate() so that other contexts can't access it or is something else the issue.

Also what do you recommend to lock in order the migrations to work as they are now, with two DbContexts and one database.

And if not at runtime how do you recommend is best to apply migrations. Via SQL script or via EF somehow or another approach.

[roji]

EF Core doesn't do any locking - the error you are seeing comes from SQL Server itself, since applying multiple migrations concurrently on the same database is brittle and will sometimes fail, based on race conditions between the two application instances.

The recommendation is to not apply the migrations programmatically - generate an SQL script from your migrations (dotnet ef migrations script), and apply that yourself as part of deploying new changes. This allows you to make sure your migration is executed once, and to ensure it works properly. Otherwise, if you insist on continuing to apply migrations programmatically, then as @ajcvickers you will have to implement some mechanism yourself that prevents two application instances from applying migrations concurrently. For example, your application could take a lock in the database before starting to apply migrations, and release it once it's done. However, this would have to be written by you, in your application.