Nested IExecutionStrategy with IDbContextTransaction

[RaHorusFreak]

Hello, I need to clarify myself about how the resiliency (EnableRetryOnFailure) works with nested code and the scope of every IExecutionStrategy.

This is the code I'm using to guarantee connection resiliency on my app for PostgreSQL databases:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql(connection, x =>
{
    x.UseNetTopologySuite();
    if(retrier?.Any() == true)
        if(retrier.Full())
            x.EnableRetryOnFailure(retrier.Attempts.Value, TimeSpan.FromSeconds(retrier.NextRetrySeconds), retrier.ErrorCodes);
        else if(retrier.AnyAttemp())
            x.EnableRetryOnFailure(retrier.Attempts.Value);
        else
            x.EnableRetryOnFailure(retrier.ErrorCodes);
});

This is working awesome across all EF transactions, but my problems show up when working with very long transactions. In a nutshell, my goal is to divide the retries into logical blocks, and thus not repeat the transaction as a whole, as the process is very long (more than 3 hours with mandatory deletions and the begining that are rolled back if the process fails at any point). So I came up with the following idea:

context.Value.Database.CreateExecutionStrategy().Execute(() =>
{
    using(IDbContextTransaction transaction = context.Value.Database.BeginTransaction())
    {
        transaction.CreateSavepoint(crudTransaction);
        try
        {
            context.Value.Database.CreateExecutionStrategy().Execute(()=>Action1());
            context.Value.Database.CreateExecutionStrategy().Execute(()=>Action2());
            context.Value.Database.CreateExecutionStrategy().Execute(()=>Action3());
            context.Value.Database.CreateExecutionStrategy().Execute(()=>Action4());
            context.Value.Database.CreateExecutionStrategy().Execute(()=>Action5());
            transaction.Commit();
        }
        catch
        {
            transaction.RollbackToSavepoint(crudTransaction);
        }
    }
});

But I realized that even so, the retries do not occur in the context of the individual actions, but for the entire transaction, so instead of retrying Action1, Action2, Action3, Action4 and Action5 independently, the whole process starts again: exceptions are not processed by the IExecutionStrategy of each action, but by the IExecutionStrategy of the transaction.

I tested with this similar approach (assuming that only one call to CreateExecutionStrategy could be made) without success:

IExecutionStrategy strategy = context.Value.Database.CreateExecutionStrategy();
strategy.Execute(() =>
{
    using(IDbContextTransaction transaction = context.Value.Database.BeginTransaction())
    {
        transaction.CreateSavepoint(crudTransaction);
        try
        {
            strategy.Execute(()=>Action1());
            strategy.Execute(()=>Action2());
            strategy.Execute(()=>Action3());
            strategy.Execute(()=>Action4());
            strategy.Execute(()=>Action5());
            transaction.Commit();
        }
        catch
        {
            transaction.RollbackToSavepoint(crudTransaction);
        }
    }
});

So my question is this: is it possible to use an IDbContextTransaction with nested IExecutionStrategy? And if possible, how do I do it?

Thank you very much in advance.

EF Core version: 8.0.7
Database provider: PostgreSQL 15.2
Target framework: NET 8.0
Operating system: Windows 11 22H2
IDE: Visual Studio 2022 17.10.4

[AndriySvyryd]

So my question is this: is it possible to use an IDbContextTransaction with nested IExecutionStrategy?

No. The nested IExecutionStrategy essentially becomes a no-op.

Consider what would happen if a transient error occurred during Action3 - the whole transaction would be rolled back, so it would be an error to retry only Action3, since Action1 and Action2 are essentially lost.

The only feasible workaround is to create a separate transaction for each action, of course this also has negative consequences.

[RaHorusFreak]

Then the procedure falls into a deadlock: if you use transactions for security reasons, EF loses the ability to use EnableRetryOnFailure atomically (which helps to retry simple operations, but which can be numerous), to move to a larger scope retry (having to repeat the entire procedure from A to Z, and being able to face the same error again, which can come from a simple procedure that does not alter the database). On the other hand, if you don't use transactions, each item is retried independently thanks to EnableRetryOnFailure, however, if the number of retries times out and any modification to the database was made, you can't revert it.
So based on your answer, the ideal scenario is: if a transient failure occurs in Action3, Action3 is retried as many times as defined on EnableRetryOnFailure, otherwise the IDbContextTransaction is retried in the same way (even if it is a resource-intensive strategy).
Grateful for your attention. Best regards.