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
- Disable savepoints on SQL Server when MARS is enabled Fixes #23269 — committed to dotnet/efcore by roji 6 years ago
- Disable savepoints on SQL Server when MARS is enabled Fixes #23269 — committed to dotnet/efcore by roji 6 years ago
- Add warning about savepoints not working on SQL Server with MARS See https://github.com/dotnet/efcore/issues/23269 — committed to dotnet/EntityFramework.Docs by roji 6 years ago
- Add warning about savepoints not working on SQL Server with MARS (#2869) See https://github.com/dotnet/efcore/issues/23269 — committed to dotnet/EntityFramework.Docs by roji 6 years ago
- Disable savepoints on SQL Server when MARS is enabled Fixes #23269 — committed to dotnet/efcore by roji 6 years ago
- Disable savepoints on SQL Server when MARS is enabled Fixes #23269 — committed to dotnet/efcore by roji 6 years ago
- Disable savepoints on SQL Server when MARS is enabled Fixes #23269 — committed to dotnet/efcore by roji 6 years ago
- * prevent using save point in transaction while `SaveChanges()`: https://github.com/dotnet/efcore/issues/23269#issuecomment-2095902588 to fix performance issue with postgres subtransaction @ TbmDbCont... — committed to n0099/open-tbm by n0099 3 years ago
Here’s a workaround that disables savepoints which can be used with 5.0.0, reverting to the 3.1 behavior:
@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
SavepointsDisabledBecauseOfMARSis a member ofSqlServerEventIdnotRelationalEventIdas 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
Since
v6.0.0-preview.1.21102.2the ctor ofRelationalTransactionadded a new paramISqlGenerationHelper 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:@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")andtransaction.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/valueMultipleActiveResultSets=Trueis needed in the SQL connection string. But withoutMultipleActiveResultSets=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.