Efcore: Connection Resiliency does not support transactions?

[chassq]

Looking at the docs we tried to add Connection Resiliency to our asp net core 1.1 application using EF 7 1.1 (e.g. options.EnableRetryOnFailure()). We got an error message stating that the execution strategy does not support database transactions (e.g. using (var tx = await db.Database.BeginTransactionAsync()). It stated we need to provide an execution strategy? We read the docs at https://docs.microsoft.com/en-us/ef/core/miscellaneous/connection-resiliency but it was not clear how to configure Connection Resiliency to use BeginTransaction. Just wanted to get some clarification if BeginTransaction was supported and if so which options we needed to add when configuring our DBContext.

Hope this question makes sense.

Thanks!

[Dixin]

I got this error message when using EnableRetryOnFailure with transactions.

This seems work:

using (AdventureWorks adventureWorks = new AdventureWorks())
{
    adventureWorks.Database.CreateExecutionStrategy().Execute(() =>
    {
        using (IDbContextTransaction transaction = adventureWorks.Database.BeginTransaction(
            IsolationLevel.ReadUncommitted))
        {
            try
            {
                ProductCategory category = new ProductCategory() { Name = nameof(ProductCategory) };
                adventureWorks.ProductCategories.Add(category);
                Trace.WriteLine(adventureWorks.SaveChanges()); // 1

                Trace.WriteLine(adventureWorks.Database.ExecuteSqlCommand(
                    "DELETE FROM [Production].[ProductCategory] WHERE [Name] = {0}",
                    nameof(ProductCategory))); // 1
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    });
}

[divega]

@chassq An execution strategy that automatically retries on failures needs to be able to play back each operation that fails. When you just enable retries and then write your application code as usual, each individual EF Core API call you make becomes its own retriable operation, i.e. each individual query that you execute or each call to SaveChanges() you make will be retried as a unit if a transient failure occurs.

But when your code initiates a transaction using BeginTransaction() you are defining your own group of operations that need to be treated as a unit, i.e. everything inside the transaction would need to be played back shall a failure occur.

That is why execution strategies that automatically retry on failures do not support users initiated transactions in plain code. The code that @Dixin posted works because it feeds the execution strategy with a delegate representing everything that need to be executed. If a transient failure occurs, the execution strategy will just invoke the delegate again.

BTW, if you don't need to use a special isolation level, you can make the code briefer using the ExecuteInTransaction() extension method.

[divega]

Note for triage: I am leaving this open to consider:

1. Whether we need to improve our documentation in this area (I haven't checked if there are already issues tracking this).
2. Having an overload of the ExecuteInTransaction() that allows specifying the isolation level.

[chassq]

Thanks for the phenomenal answers! @Dixin code is much appreciated and @divega thanks for the in-depth explanation! Most appreciated!!!

[divega]

I have created issues for the two action items we have to follow up on. Closing as I believe the question was answered.

[pseabury]

@divega Is there a way to specify a Transaction Timeout as well as the Isolation level with these extensions? Some of the command I run via Transactions have timeouts greater than the default.

[AndriySvyryd]

@pseabury You can copy the code from the extension method and modify it to suit your needs: https://github.com/dotnet/efcore/blob/0d76bbf45a42148924b413ef8f37bf49c1ce10d3/src/EFCore.Relational/Storage/RelationalExecutionStrategyExtensions.cs#L258

[pseabury]

Great, thanks @AndriySvyryd