Maximum Time for an execution block.........with retry and in-between-delay

Question

I'm trying to write a Policy.

I want my execution-code to run for a maximum amount of time (10 seconds in the example). But I also want to retry x number of times (3 times in sample). And have a in between failure pause (2 seconds in the sample).

I've rigged up my stored procedure to artificially delay to test my behavior.

As coded (below code), my dataset gets populated after 30 seconds (fyi: the 30 seconds is a hard coded value in the stored procedure). So my execution-code is NOT bailing out after 10 seconds....

Ideally, I see the code bailing out after 10 seconds on the first two attempts, then working on the third attempt (because the stored procedure won't artificially delay). Obviously this isn't real code, but weird stored procedure gives me a way to test the behavior.

My stored procedure:

USE [Northwind]
GO


/* CREATE */ ALTER PROCEDURE [dbo].[uspWaitAndReturn]
(
    @InvokeDelay bit
)

AS

SET NOCOUNT ON;

if ( @InvokeDelay > 0)
BEGIN
    WAITFOR DELAY '00:00:30';  
END

select top 1 * from dbo.Customers c order by newid()

GO

My C#/Polly/Database code:

    public DataSet GetGenericDataSet()
    {
        DataSet returnDs = null;

        int maxRetryAttempts = 3; /* retry attempts */
        TimeSpan pauseBetweenFailuresTimeSpan = TimeSpan.FromSeconds(2); /* pause in between failures */
        Policy timeoutAfter10SecondsPolicy = Policy.Timeout(TimeSpan.FromSeconds(10)); /* MAGIC SETTING here, my code inside the below .Execute block below would bail out after 10 seconds */
        Policy retryThreeTimesWith2SecondsInBetweenPolicy = Policy.Handle().WaitAndRetry(maxRetryAttempts, i => pauseBetweenFailuresTimeSpan);
        Policy aggregatePolicy = timeoutAfter10SecondsPolicy.Wrap(retryThreeTimesWith2SecondsInBetweenPolicy);

        int attemptCounter = 0; /* used to track the attempt and conditionally set the @InvokeDelay value for the stored procedure */

        aggregatePolicy.Execute(() =>
        {
            try
            {
                attemptCounter++;

                /* Microsoft.Practices.EnterpriseLibrary.Data code */
                ////////DatabaseProviderFactory factory = new DatabaseProviderFactory();
                ////////Database db = factory.CreateDefault();
                ////////DbCommand dbc = db.GetStoredProcCommand("dbo.uspWaitAndReturn");
                ////////dbc.CommandTimeout = 120;
                ////////db.AddInParameter(dbc, "@InvokeDelay", DbType.Boolean, attemptCounter < maxRetryAttempts ? true : false); /* if i'm not on my last attempt, then pass in true to cause the artificial delay */
                ////////DataSet ds;
                ////////ds = db.ExecuteDataSet(dbc);
                ////////returnDs = ds;

                using (SqlConnection conn = new SqlConnection(@"MyConnectionStringValueHere"))
                {
                    SqlCommand sqlComm = new SqlCommand("[dbo].[uspWaitAndReturn]", conn);
                    sqlComm.Parameters.AddWithValue("@InvokeDelay", attemptCounter < maxRetryAttempts ? true : false);
                    sqlComm.CommandType = CommandType.StoredProcedure;

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = sqlComm;
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    returnDs = ds;
                }

            }
            catch (Exception ex)
            {
                string temp = ex.Message;
                throw;
            }
        });

        return returnDs;
    }

The using statements:

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
////    using Microsoft.Practices.EnterpriseLibrary.Data;
using Polly;

The versions (packages.config)

/>

APPEND:

After the great answer from @mountain traveller, I have a working example:

The important points were:

Added TimeoutStrategy.Pessimistic

And added a DbCommand.Cancel() call (or SqlCommand.Cancel() if you don't use enterprise library) to kill the (previous) commands or else they will continue to run (not good).

I also had to "reverse" my "Policy aggregatePolicy".

    public DataSet GetGenericDataSet()
    {
        DataSet returnDs = null;

        DbCommand dbc = null; /* increase scope so it can be cancelled */

        int maxRetryAttempts = 3; /* retry attempts */
        TimeSpan pauseBetweenFailuresTimeSpan = TimeSpan.FromSeconds(2); /* pause in between failures */
        Policy timeoutAfter10SecondsPolicy = Policy.Timeout(
            TimeSpan.FromSeconds(10), 
            TimeoutStrategy.Pessimistic,
            (context, timespan, task) =>
            {
                string x = timespan.Seconds.ToString();
                if (null != dbc)
                {
                    dbc.Cancel();
                    dbc = null;
                }
            });

        Policy retryThreeTimesWith2SecondsInBetweenPolicy = Policy.Handle().WaitAndRetry(maxRetryAttempts, i => pauseBetweenFailuresTimeSpan);
        ////Policy aggregatePolicy = timeoutAfter10SecondsPolicy.Wrap(retryThreeTimesWith2SecondsInBetweenPolicy);
        Policy aggregatePolicy = retryThreeTimesWith2SecondsInBetweenPolicy.Wrap(timeoutAfter10SecondsPolicy);

        int attemptCounter = 0; /* used to track the attempt and conditionally set the @InvokeDelay value for the stored procedure */

        aggregatePolicy.Execute(() =>
        {
            try
            {
                attemptCounter++;

                /* Microsoft.Practices.EnterpriseLibrary.Data code */
                DatabaseProviderFactory factory = new DatabaseProviderFactory();
                Database db = factory.CreateDefault();
                dbc = db.GetStoredProcCommand("dbo.uspWaitAndReturn");
                dbc.CommandTimeout = 120;
                db.AddInParameter(dbc, "@InvokeDelay", DbType.Boolean, attemptCounter < maxRetryAttempts ? true : false); /* if i'm not on my last attempt, then pass in true to cause the artificial delay */
                DataSet ds;
                ds = db.ExecuteDataSet(dbc);
                returnDs = ds;

                ////////using (SqlConnection conn = new SqlConnection(@"YOUR_VALUE_HERE"))
                ////////{
                ////////    SqlCommand sqlComm = new SqlCommand("[dbo].[uspWaitAndReturn]", conn);
                ////////    sqlComm.Parameters.AddWithValue("@InvokeDelay", attemptCounter < maxRetryAttempts ? true : false);
                ////////    sqlComm.CommandType = CommandType.StoredProcedure;

                ////////    SqlDataAdapter da = new SqlDataAdapter();
                ////////    da.SelectCommand = sqlComm;
                ////////    DataSet ds = new DataSet();
                ////////    da.Fill(ds);
                ////////    returnDs = ds;
                ////////}
            }
            catch (SqlException sqlex)
            {
                switch (sqlex.ErrorCode)
                {
                    case -2146232060:
                        /* I couldn't find a more concrete way to find this specific exception, -2146232060 seems to represent alot of things */
                        if (!sqlex.Message.Contains("cancelled"))
                        {
                            throw;
                        }

                        break;
                    default:
                        throw;
                }
            }
        });

        return returnDs;
    }

mountain traveller · Accepted Answer

The Polly TimeoutPolicy comes in two modes:

Optimistic Timeout enforces the timeout by co-operative cancellation via a CancellationToken. It expects the executed delegates to respond to a CancellationToken.
Pessimistic Timeout enforces timeouts on delegates which are not responsive to a CancellationToken

The delegate you are executing does not honour any CancellationToken. So (for the original code posted) you would need to configure the policy to use TimeoutStrategy.Pessimistic:

Policy timeoutAfter10SecondsPolicy = Policy.Timeout(TimeSpan.FromSeconds(10), TimeoutStrategy.Pessimistic);

(In the original code posted, Policy timeoutAfter10SecondsPolicy = Policy.Timeout(TimeSpan.FromSeconds(10)); adopted TimeoutStrategy.Optimistic, as this is the default.)

The above is to explain why you are not seeing the TimeoutPolicy working in the code presented. That said: Note the discussion in the Polly wiki about what pessimistic timeout means: it allows the calling thread to walk away from waiting for an executed delegate, but does not cancel the thread/Task running that delegate. So the SQL resources in use would not be released at timeout.

To ensure the SQL resources would be released on timeout, you would need to extend your code to cancel the SQL operation on timeout. You could use the SqlCommand.Cancel() method, for example as shown in this StackOverflow answer. Polly's TimeoutPolicy can be configured with an onTimeout delegate which is invoked when timeout occurs: ie configure this delegate to call the appropriate SqlCommand.Cancel().

Alternatively, the entire code could be moved to an async approach, and use something like SqlCommand.ExecuteReaderAsync(CancellationToken), coupled with Polly's optimistic timeout driven by CancellationToken. But that is a much wider discussion.

Maximum Time for an execution block.........with retry and in-between-delay

Answers (1)

Related Questions