Lesson Learned #488: A severe error occurred on the current command. Operation cancelled by user.

Today, I worked on a service request that our customer got this error message:  "A severe error occurred on the current command. The results, if any, should be discarded.\r\nOperation cancelled by user.". This cancellation happens before the CommandTimeout duration is met in the SQL Client application, normally, asynchronous database operations that the CancellationToken setting is reached. Following I would like to share with you my lessons learned. 

The customer application was running asynchronous database operations and two primary types of cancellations can occur:

• A CommandTimeout cancellation typically indicates that the query is taking longer than expected, possibly due to database performance issues or query complexity. On the other hand, a cancellation triggered by a CancellationToken may be due to application logic deciding to abort the operation, often in response to user actions or to maintain application responsiveness.

Error Handling and Connection Resilience:

Errors during query execution, such as syntax errors or references to non-existent database objects, necessitate immediate attention and are not suitable for retry logic. The application must distinguish these errors from transient faults, where retry logic with exponential backoff can be beneficial. Moreover, connection resilience is paramount, and implementing a retry mechanism for establishing database connections ensures that transient network issues do not disrupt application functionality.

Measuring Query Execution Time:

Gauging the execution time of queries is instrumental in identifying performance bottlenecks and optimizing database interactions. The example code demonstrates using a Stopwatch to measure and log the duration of query execution, providing valuable insights for performance tuning.

Adaptive Timeout Strategy:

The code snippet illustrates an adaptive approach to handling query cancellations due to timeouts. By dynamically adjusting the CommandTimeout and CancellationToken timeout values upon encountering a timeout-related cancellation, the application attempts to afford the query additional time to complete in subsequent retries, where feasible.

Tests and Results:

I conducted a series of tests to understand the behavior under different scenarios and the corresponding exceptions thrown by the .NET application. Here are the findings:

1. Cancellation Prior to Query Execution:
• Scenario: The cancellation occurs before the query gets a chance to execute, potentially due to reasons such as application overload or a preemptive cancellation policy.
• Exception Thrown: TaskCanceledException
• Internal Error Message: "A task was canceled."
• Explanation: This exception is thrown when the operation is canceled through a CancellationToken, indicating that the asynchronous task was canceled before it could begin executing the SQL command. It reflects the application's decision to abort the operation, often to maintain responsiveness or manage workload.
2. Cancellation Due to CommandTimeout:
• Scenario: The cancellation is triggered by reaching the CommandTimeout of SqlCommand, indicating that the query's execution duration exceeded the specified timeout limit.
• Exception Thrown: SqlException with an error number of -2
• Internal Error Message: "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
• Explanation: This exception occurs when the query execution time surpasses the CommandTimeout value, prompting SQL Server to halt the operation. It suggests that the query may be too complex, the server is under heavy load, or there are network latency issues.
3. Cancellation Before CommandTimeout is Reached:
• Scenario: The cancellation happens before the CommandTimeout duration is met, not due to the CommandTimeout setting but possibly due to an explicit cancellation request or an unforeseen severe error during execution.
• Exception Thrown: General Exception (or a more specific exception depending on the context)
• Internal Error Message: "A severe error occurred on the current command. The results, if any, should be discarded.\r\nOperation cancelled by user."
• Explanation: This exception indicates an abrupt termination of the command, potentially due to an external cancellation signal or a critical error that necessitates aborting the command. Unlike the TaskCanceledException, this may not always originate from a CancellationToken and can indicate more severe issues with the command or the connection.

using System;
using System.Diagnostics;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

namespace CancellationToken
{
    class Program
    {
        private static string ConnectionString = "Server=tcp:servername.database.windows.net,1433;User Id=username;Password=pwd!;Initial Catalog=dbname;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=1;ConnectRetryCount=3;ConnectRetryInterval=10;Application Name=ConnTest";
        private static string Query = "waitfor delay '00:00:20'";

        static async Task Main(string[] args)
        {
            SqlConnection connection = await EstablishConnectionWithRetriesAsync(3, 2000);
            if (connection == null)
            {
                Console.WriteLine("Failed to establish a database connection.");
                return;
            }

            await ExecuteQueryWithRetriesAsync(connection, 5, 1000, 10000,15);

            connection.Close();
        }

        private static async Task<SqlConnection> EstablishConnectionWithRetriesAsync(int maxRetries, int initialDelay)
        {
            SqlConnection connection = null;
            int retryDelay = initialDelay;

            for (int attempt = 1; attempt <= maxRetries; attempt++)
            {
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    await connection.OpenAsync();
                    Console.WriteLine("Connection established successfully.");
                    return connection;
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"Failed to establish connection: {ex.Message}. Attempt {attempt} of {maxRetries}.");
                    if (attempt == maxRetries)
                    {
                        Console.WriteLine("Maximum number of connection attempts reached. The application will terminate.");
                        return null;
                    }

                    Console.WriteLine($"Waiting {retryDelay / 1000} seconds before the next connection attempt...");
                    await Task.Delay(retryDelay);

                    retryDelay *= 2;
                }
            }

            return null;
        }

        private static async Task ExecuteQueryWithRetriesAsync(SqlConnection connection, int maxRetries, int initialDelay, int CancellationTokenTimeout, int CommandSQLTimeout)
        {
            int retryDelay = initialDelay;

            for (int attempt = 1; attempt <= maxRetries; attempt++)
            {
                using (var cts = new CancellationTokenSource())
                {
                    cts.CancelAfter(CancellationTokenTimeout*attempt); // Set CancellationToken timeout to 10 seconds

                    try
                    {
                        using (SqlCommand command = new SqlCommand(Query, connection))
                        {
                            command.CommandTimeout = CommandSQLTimeout*attempt; // Set CommandTimeout to 15 seconds

                            Stopwatch stopwatch = Stopwatch.StartNew();

                            await command.ExecuteNonQueryAsync(cts.Token);

                            stopwatch.Stop();
                            Console.WriteLine($"Query executed successfully in {stopwatch.ElapsedMilliseconds} milliseconds.");

                            return;
                        }
                    }
                    catch (TaskCanceledException)
                    {
                        Console.WriteLine($"Query execution was canceled by the CancellationToken. Attempt {attempt} of {maxRetries}.");
                    }
                    catch (SqlException ex) when (ex.Number == -2)
                    {
                        Console.WriteLine($"Query execution was canceled due to CommandTimeout. Attempt {attempt} of {maxRetries}.");
                    }
                    catch (SqlException ex) when (ex.Number == 207 || ex.Number == 208 || ex.Number == 2627)
                    {
                        Console.WriteLine($"SQL error preventing retries: {ex.Message}");
                        return;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"An exception occurred: {ex.Message}");
                        return;
                    }

                    Console.WriteLine($"Waiting {retryDelay / 1000} seconds before the next query attempt...");
                    await Task.Delay(retryDelay);

                    retryDelay *= 2;
                }
            }
        }
    }
}

Disclaimer:

The example code provided in this article is intended for educational and informational purposes only. It is strongly recommended to test this code in a controlled and secure environment before implementing it in any production system. The user assumes full responsibility for any risks, damages, or losses incurred by using the code. The author and the blog are not liable for any issues that may arise from the use or misuse of the provided code.