Efcore: Migrations do not support multiple workers

[jblantonhiperos]

I'm building an application using dotnet core 2.1 and efcore. The application runs migrations on startup. When running in a kubernetes cluster, multiple workers often start in quick succession, especially after a deployment. Do migrations support multiple workers trying to run migrations simultaneously? Should we be preventing multiple workers from calling Migrate() on startup? We often see the following errors in this scenario.

Exception message:  
Application startup exception: System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK___EFMigrationsHistory'. Cannot insert duplicate key in object 'dbo.__EFMigrationsHistory'. The duplicate key value is (20181005185221_AddIndecies)
Stack trace:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)

Steps to reproduce

I am working on putting together a project with many long running migrations that I can then run side by side which contains no business sensitive code.

Further technical details

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Ubuntu (microsoft/dotnet:2.1-aspnetcore-runtime via docker), RHEL host
IDE: Visual Studio 2017 15.8.3

[ajcvickers]

@jblantonhiperos We generally recommend against running migrations on application startup. One of the reasons for this is that it is important that one and only one process/thread attempts to migrate the database, and that this happens before any other process/thread attempts to use the database. This is increasingly hard to coordinate when going beyond the simple single process, single database model. It also requires that the running application has permission to change the database structure, which is usually not recommended.

Instead, the database migration should be considered a deployment task. Usually this just means generating SQL scripts from the migrations and executing those against the database as part of application deployment.