efcore: SaveChanges fails for SQL Server because of savepoints are not supported when MultipleActiveResultSets is enabled

File a bug

After upgrading from 3.1.9 to 5.0.0, calling SaveChangesAsync inside foreach throws SqlException, even when MultipleActiveResultSets is set to True

The transaction operation cannot be performed because there are pending requests working on this transaction.

On 3.1.9 this code works without exceptions.

Include your code

using Microsoft.EntityFrameworkCore;

using var db = new SampleContext();
await db.Database.EnsureDeletedAsync();
await db.Database.EnsureCreatedAsync();

var transaction = await db.Database.BeginTransactionAsync();

db.Add(new Table1());
await db.SaveChangesAsync();

foreach (var table1 in db.Table1)
{
    db.Add(new Table2());
    await db.SaveChangesAsync(); //exception here
}

public class SampleContext : DbContext
{
    const string connectionString =
        @"Server={server};Database={database};Trusted_Connection=True;MultipleActiveResultSets=true";

    public DbSet<Table1> Table1 { get; set; }
    public DbSet<Table2> Table2 { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => base.OnConfiguring(optionsBuilder.UseSqlServer(connectionString));
}

public class Table1
{
    public int Id { get; set; }
}

public class Table2
{
    public int Id { get; set; }
}

Include stack traces

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): The transaction operation cannot be performed because there are pending requests working on this transaction.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.CreateSavepointAsync(String name, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.CreateSavepointAsync(String name, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)```

### Include provider and version information

EF Core version: 5.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0

About this issue

  • Original URL
  • State: closed
  • Created 6 years ago
  • Reactions: 1
  • Comments: 15 (8 by maintainers)

Commits related to this issue

Most upvoted comments

Here’s a workaround that disables savepoints which can be used with 5.0.0, reverting to the 3.1 behavior:

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(....)
            .ReplaceService<IRelationalTransactionFactory, NoSavepointsTransactionFactory>();
}

public class NoSavepointsTransactionFactory : IRelationalTransactionFactory
{
    public virtual RelationalTransaction Create(
        IRelationalConnection connection,
        DbTransaction transaction,
        Guid transactionId,
        IDiagnosticsLogger<DbLoggerCategory.Database.Transaction> logger,
        bool transactionOwned)
        => new NoSavepointsTransaction(connection, transaction, transactionId, logger, transactionOwned);

    class NoSavepointsTransaction : RelationalTransaction
    {
        public NoSavepointsTransaction(
            IRelationalConnection connection,
            DbTransaction transaction,
            Guid transactionId,
            IDiagnosticsLogger<DbLoggerCategory.Database.Transaction> logger,
            bool transactionOwned)
            : base(connection, transaction, transactionId, logger, transactionOwned)
        {
        }

        public override bool SupportsSavepoints => false;
    }
}

@changhuixu whether or not MultipleActiveResultSets=True is needed on the connection string is a question that’s unrelated to savepoints; some applications rely on having multiple command resultsets at the same time. It’s generally recommended to avoid MARS as there are various known issues and performance problems with it.

However, if you do enable MARS, and you use user transactions, then EF won’t be able to create a savepoint at the start of SaveChanges. That means that if there’s a failure in the middle, things will be left in an inconsistent state without the possibility to undo the SaveChanges and try it again. If that’s OK with you, suppress the warning.

Thank you @roji !

From the explanation above. disabling this warning is what I need. Hope this warning message can be updated. And I just found SavepointsDisabledBecauseOfMARS is a member of SqlServerEventId not RelationalEventId as in the warning message.

Yes, that’s correct; if upon failure of any specific SaveChanges, you want to roll back the entire transaction, then savepoints indeed aren’t helpful and can be disabled. Savepoints can help in case you want to retry individual SaveChanges within a transaction.

Specifically regarding the code above… If you want to have a maximum saving batch size, you can simply configure EF Core to do that for you: see the docs on batching. In other words, you can simply add all instances to the context and call SaveChanges once, and the batching would be handled for you under the hood.

(You may still want to batch yourself if you don’t want to wait to get all the results from DegreeDaysParser.ParseAsync before sending data to the database).

https://github.com/dotnet/efcore/issues/23269#issuecomment-726587495

Here’s a workaround that disables savepoints which can be used with 5.0.0, reverting to the 3.1 behavior:

Since v6.0.0-preview.1.21102.2 the ctor of RelationalTransaction added a new param ISqlGenerationHelper sqlGenerationHelper: https://github.com/dotnet/efcore/pull/23036/files#diff-360dcdb9e8ca48b34987b29265a194977f7eedf1e1d8c7c4a597141df6b0b443R69, so we need something like https://github.com/dotnet/efcore/pull/9792/files#diff-e50873e4aeea395fe6aa9df7d1aed8b9e2e699a96fdfbc7dc3bceedd6f488b56R25:

protected class NoSavePointTransactionFactory(RelationalTransactionFactoryDependencies dependencies)
    : IRelationalTransactionFactory
{
    protected virtual RelationalTransactionFactoryDependencies Dependencies { get; } = dependencies;

    public virtual RelationalTransaction Create(
        IRelationalConnection connection,
        DbTransaction transaction,
        Guid transactionId,
        IDiagnosticsLogger<DbLoggerCategory.Database.Transaction> logger,
        bool transactionOwned)
        => new NoSavePointTransaction(
            connection, transaction, transactionId, logger, transactionOwned, Dependencies.SqlGenerationHelper);

    private sealed class NoSavePointTransaction(IRelationalConnection connection,
        DbTransaction transaction,
        Guid transactionId,
        IDiagnosticsLogger<DbLoggerCategory.Database.Transaction> logger,
        bool transactionOwned,
        ISqlGenerationHelper sqlGenerationHelper)
        : RelationalTransaction(
            connection, transaction, transactionId, logger, transactionOwned, sqlGenerationHelper)
    {
        public override bool SupportsSavepoints => false;
    }
}

@roji Thank you. Yes, I know the use cases for Savepoints that allows us to stage one or more points and then we can conditionally restore to a specific point if needed: transaction.CreateSavepoints("GoodState") and transaction.RollbackToSavepoint("GoodState")

Now, facing the warning message of Savepoints are disabled because Multiple Active Result Sets (MARS) is enabled., I am wondering if the key/value MultipleActiveResultSets=True is needed in the SQL connection string. But without MultipleActiveResultSets=True, will the transaction behave differently?

Savepoints are important in order to roll back the partial effects of SaveChanges in case an error occurs in it - when a user transaction is in progress. If you don’t use user transactions, then that warning can be safely disabled, but otherwise this could affect program behavior; so the warning is there to make people aware of that.

Thanks @roji,

so in my case I have to disable the warning.