Migrate inside a Transaction throws InvalidOperationException

[MarkusBrgr]

I am trying to use context.Database.Migrate() inside a Transaction, to rollback if anything fails. As seen in the issue #6322
Here is the code i have tried to execute in the Startup:

IServiceProvider provider = services.BuildServiceProvider();   // Building the ServiceProvider in the Startup
using (IServiceScope scope = provider.CreateScope()) {
    SyncDbContext context = scope.ServiceProvider.GetService<SyncDbContext>();
    using (IDbContextTransaction transaction = context.Database.BeginTransaction()) {
        try {
            context.Database.Migrate();   // Throws InvalidoperationException
            transaction.Commit();
        }
        catch (Exception e) {
            transaction.Rollback();
            throw new Exception("Migration failed...");
        }
    }
}

When Executing this, it throws an InvalidOperationException:

Exception message: "The connection is already in a transaction and cannot participate in another transaction."
Stack trace:
"   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)\r\n   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction()\r\n   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)\r\n   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)\r\n   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)\r\n   at PFH.Sync.Agent.DuZu.Web.Util.DatabaseMigrator.Migrate(IServiceProvider services) in D:\\Projekte\\PFH.k5Sync.Dev\\Sources\\Dev\\Dev.Sprint009\\Agent.DuZu.Web\\Util\\DatabaseMigrator.cs:line 83"

When trying to get the transaction like this there is no transaction:

    using (IDbContextTransaction transaction = context.Database.CurrentTransaction){
        //here the Transaction is null
        ...
    }

Even setting the AutoTransactionEnabled to false won`t do the trick.

Further technical details

EF Core version: 2.0.3
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 7
IDE: Visual Studio 2017 15.7

[ajcvickers]

Note for triage: was able to reproduce this. Logs:

dbug: Microsoft.EntityFrameworkCore.Infrastructure[10401]
      An 'IServiceProvider' was created for internal use by Entity Framework.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.1.0-rtm-30799 initialized 'BloggingContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20200]
      Beginning transaction with isolation level 'ReadCommitted'.
dbug: Microsoft.EntityFrameworkCore.Migrations[20400]
      Migrating using database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (54ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [MigrationId], [ProductVersion]
      FROM [__EFMigrationsHistory]
      ORDER BY [MigrationId];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [MigrationId], [ProductVersion]
      FROM [__EFMigrationsHistory]
      ORDER BY [MigrationId];
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
info: Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20180613002445_Two'.
dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20203]
      Rolling back transaction.

Unhandled Exception: System.Exception: Migration failed... ---> System.InvalidOperationException: The connection is already in a transaction and cannot participate in another transaction.
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.EnsureNoTransactions()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Program.Main() in C:\Stuff\TwoOneCore\TwoOneCore\Program.cs:line 60
   --- End of inner exception stack trace ---
   at Program.Main() in C:\Stuff\TwoOneCore\TwoOneCore\Program.cs:line 66
dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20204]
      Disposing transaction.
dbug: Microsoft.EntityFrameworkCore.Infrastructure[10407]
      'BloggingContext' disposed.

[MarkusBrgr]

Hello,

since .net Core 2.2 will be released in Q4 2018, is there a temporary fix or workaround to use migrations inside transactions?
Something like Script migrations inside a transaction?

[AndriySvyryd]

@MarkusBrgr If you call Migrate() without a transaction we will create a transaction for you, this is the reason this exception is thrown. The only operations executed outside of a transaction are the ones that can't be inside of one.

[dkutetsky]

As a workaround, you could use manual way to run migrations:
#6322 (comment)

[jlenormand]

@MarkusBrgr If you call Migrate() without a transaction we will create a transaction for you, this is the reason this exception is thrown. The only operations executed outside of a transaction are the ones that can't be inside of one.

We are using .Net Core 2.2, mysql DB with pomelo.efcore.mysql library to access it
We had more than once, a migration that would fail on our server when being applied. It is usually in the end due to some FK issue when we were updating.
The thing is that our DB then will be in a bad intermediate state since the migration started but got not completed. If the migration was run inside a transaction like you said, the db in the end should be back to the state before starting to apply the migration.

My latest migration that failed was pretty simple, 3 type change on column, 3 index added and 2 FK added. I am using it to try to make the migration run inside a transaction because, it is a huge issue for us. With this one, the DB end up with the 3 column updates and the new index but no FKs

[vasicvuk]

We are also facing this issue. The migrations is failing on creation of 10'th table leaves first 9 created. Next time when the migrations is fixed it crashes because some tables already exist

[AndriySvyryd]

Related to #7681

[bricelam]

Triage: We should allow this unless suppressTransaction: true is used inside one of the migrations.

[schmitch]

btw. I've run into this today aswell, since I wanted to combine Marten and EFCore by using transaction to migrate code over to marten.

first I created a transaction that adds columns to the database BlablaMigration than I created code like that:

var service = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
var transaction = await service.Database.BeginTransactionAsync();
var pendingMigrations = await service.Database.GetPendingMigrationsAsync();

await service.Database.MigrateAsync();
if (pendingMigrations.Contains("20201203165751_BlablaMigration"))
{
using var martenSession = _store.SessionWithTransaction(transaction);
await foreach (var table in context.Tables.AsAsyncEnumerable())
{
  martenSession.Events.StartStream(table.Id, dataEvent);
}
await martenSession.SaveChangesAsync();
}
await transaction.CommitAsync();

I think it would be great if that would be possible.

[suadev]

Same here +1

[Pasukaru]

Also running into this issue. My case is schema-based multi tenancy.
I would like to setup some general data in a meta schema, then create the tenant schema and run migrations to create tables, etc.

Are there any workarounds to run migrations in an existing transaction?

[dariooo512]

Any workarounds on this one?

[nbon12]

At least for MySQL users: I think I found the issue, MySQL (as of version 8) does not support rolling back DDL:
https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html

[moander]

@MarkusBrgr If you call Migrate() without a transaction we will create a transaction for you, this is the reason this exception is thrown. The only operations executed outside of a transaction are the ones that can't be inside of one.

As far as I can see in v8.0.2, no transaction is created for SQL server. If you throw something in the second migration you see that the first migration got applied.

I have found that .BeginTransaction() before .Migrate() works fine if the database already exist on the server.