Polly retry policy with sql holding transaction open

Question

I am using Polly to implement a retry policy for transient SQL errors. The issue is I need to wrap my db calls up in a transaction (because if any one fails, I want to rollback). This was easy before I implemented retry from Polly because I would just catch the exception and rollback. However, I am now using the code below to implement Polly and retry a few times. The issue is, when I have an exception and Polly does the retry and let's say the retry doesn't work and it fails all of the attempts, the transaction is held open and I get errors that say "Cannot begin a transaction while in a transaction". I know why this is happening, it is because the .WaitAndRetry will execute the code in the block BEFORE each attempt. This is where I have my rollback now. This works for all attempts except for the last one.

The question is, how do I implement Polly when I have a transaction and need to rollback after each failure so that even on the last failure, it is still rolled back?

Here is what I am doing now:

return Policy
    .Handle<SQLiteException>()
    .WaitAndRetry(retryCount: 2, sleepDurationProvider: retryAttempt => TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)), onRetry: (exception, retryCount, context) =>
    {
        connection.Rollback();
         Logger.Instance.WriteLog<DataAccess>($"Retry {retryCount} of inserting employee files", LogLevel.Error, exception);
    })
    .Execute(() =>
    {
        connection.BeginTransaction();
        connection.Update(batch);
        connection.Insert(pkgs);
        if (pkgStatus != null)
            connection.Insert(pkgStatus);
        if (extended != null)
            connection.Insert(extended);
        connection.Commit();
        return true;
    });

Answer 1

As you have described the WaitAndRetry's onRetry delegate runs before the retry policy goes to sleep. That delegate is normally used to capture log information, not to perform any sort of compensating action.

If you need to rollback then you have several options:

• The rollback is part of your to be executed delegate
  • the method which is decorated with the policy
• By making use of NoOp policy and ExecuteAndCapture method
• The success and failure cases are separated by using the Fallback policy

Let me show you the last two via a simplified example:

Simplified application

private static bool isHealthy = true;
static void SampleCall()
{
    Console.WriteLine("SampleCall");
    isHealthy = false;
    throw new NotSupportedException();
}

static void Compensate()
{
    Console.WriteLine("Compensate");
    isHealthy = true;
}

To put it simply:

• we have the SampleCall which can ruin the healthy state
• and we have the Compensate which can do self-healing.

NoOp + ExecuteAndCapture

static void Main(string[] args)
{
    var retry = Policy<bool>
        .HandleResult(isSucceeded => !isSucceeded)
        .Retry(2);

    var noop = Policy.NoOp();

    bool isSuccess = retry.Execute(() =>
    {
        var result = noop.ExecuteAndCapture(SampleCall);
        if (result.Outcome != OutcomeType.Failure) 
            return true;
        
        Compensate();
        return false;

    });

    Console.WriteLine(isSuccess);
}

• The NoOp, as its name suggests, does not do anything special. It will execute the provided the delegate and that's it.
• The ExecuteAndCapture will execute the provided delegate and will return a PolicyResult object, which has a couple of useful properties: Outcome, FinalException, ExceptionType and Context
  • If the Outcome is not a Failure (so no exception has been thrown) then we will return true and the retry policy will NOT be triggered.
  • If the OutCome is a Failure then we will preform the Compensate action and we will return false to trigger the retry policy.
• The HandleResult will examine the returned value and decides whether or not the provided delegate should be re-executed.
• The isSuccess contains the final result.
  • It might be true if the SampleCall succeeds with at most 3 executions (1 initial call and 2 retries).
  • Or it might be false if the all 3 executions fail.

Fallback

static void Main(string[] args)
{
    var retry = Policy<bool>
        .HandleResult(isSucceeded => !isSucceeded)
        .Retry(2);

    var fallback = Policy<bool>
        .Handle<NotSupportedException>()
        .Fallback(() => { Compensate(); return false; });

    var strategy = Policy.Wrap(retry, fallback);

    bool isSuccess = strategy.Execute(() =>
    {
        SampleCall();
        return true;
    });

    Console.WriteLine(isSuccess);
}

• Here we have separated the success and failure cases.
  • In case of success we return true from the Execute's delegate.
  • In case of failure the Execute will propagate the Exception to the Fallback policy which executes the Compensate action and then return false to trigger the retry policy.
• The Policy.Wrap is normally used to define an escalation chain. If the inner fails and does not handle the given situation then it will call the outer.
  • For example, if a NotImplementedException is thrown then from the Fallback's perspective this is an unhandled exception so escalation is needed.
• In our case we use it to perform the self-healing and then trigger the retry.

---

I hope these two simple examples help you to decided which way you prefer to achieve your goal.

Answer 2

Through some research and testing, here is one possible solution I came up with. Functionally it works so I am offering it as an answer but I don't know if there is a better way to do it or a more supported way from within Polly.

One way is to wrap the connection transaction stuff, inside of the .Execute in a separate try/catch so that the rollback can happen there and then re-throw the exception so Polly can pick it up and fire the retry.

Here is what I did:

return Policy
    .Handle<SQLiteException>()
    .WaitAndRetry(retryCount: 2, sleepDurationProvider: retryAttempt => TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)), onRetry: (exception, retryCount, context) =>
    {
        //connection.Rollback();
        Logger.Instance.WriteLog<DataAccess>($"Retry {retryCount} of inserting employee files", LogLevel.Error, exception);
    })
    .Execute(() =>
    {
        try
        {
            connection.BeginTransaction();
            connection.Update(batch);
            connection.Insert(pkgs);
            if (pkgStatus != null)
                connection.Insert(pkgStatus);
            if (extended != null)
                connection.Insert(extended);
            connection.Commit();
            return true;
        }
        catch (SQLiteException sx)
        {
            connection.Rollback();
            throw;
        }
    });