¿Saber cuándo reintentar o fallar al llamar a SQL Server desde C #?

Tengo una aplicación C # que recupera datos de SQL Server alojado en un entorno algo escabroso. No hay nada que pueda hacer para abordar los problemas ambientales, por lo que debo manejarlos de la forma más elegante posible.

Para hacerlo, quiero volver a intentar las operaciones que son el resultado de fallas de la infraestructura, como problemas técnicos de red, servidores SQL que se desconectan porque están siendo reiniciados, tiempos de espera de consultas, etc. Al mismo tiempo, no quiero para volver a intentar las consultas si fallaron los errores lógicos. Solo quiero que hagan una excepción al cliente.

Mi pregunta es esta: ¿cuál es la mejor manera de distinguir entre problemas ambientales (conexiones perdidas, tiempos muertos) y otros tipos de excepciones (cosas como errores lógicos que podrían haber sucedido incluso si el entorno fuera estable).

¿Existe un patrón comúnmente utilizado en C # para tratar con cosas como esta? Por ejemplo, ¿hay alguna propiedad que pueda verificar en el objeto SqlConnection para detectar conexiones fallidas? Si no, ¿cuál es la mejor manera de abordar este problema?

Por lo que vale, mi código no es nada especial:

using (SqlConnection connection = new SqlConnection(myConnectionString)) using (SqlCommand command = connection.CreateCommand()) { command.CommandText = mySelectCommand; connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { // Do something with the returned data. } } } 

Una única SqlException (puede) envuelve múltiples errores de SQL Server. Puede iterar a través de ellos con la propiedad Errors . Cada error es SqlError :

 foreach (SqlError error in exception.Errors) 

Cada SqlError tiene una propiedad de Class que puede usar para determinar aproximadamente si puede volver a intentarlo o no (y en caso de que vuelva a intentarlo si también debe volver a crear la conexión). Desde MSDN :

  • Class <10 es para errores en la información que pasó entonces (probablemente) no puede volver a intentar si primero no corrige las entradas.
  • Class de 11 a 16 son “generadas por el usuario”, entonces probablemente tampoco podrá hacer nada si el usuario no corrige sus entradas. Tenga en cuenta que la clase 16 incluye muchos errores temporales y la clase 13 es para lockings (gracias a EvZ) por lo que puede excluir estas clases si las maneja una a una.
  • Class del 17 al 24 son errores generics de hardware / software y puede volver a intentarlo. Cuando Class tiene 20 o más, también debe recrear la conexión . Tenga en cuenta que 22 y 23 pueden ser errores graves de hardware / software y puede dejar de intentarlo directamente. 24 indica un error de medios (algo que debe advertirse al usuario, pero puede volver a intentarlo en caso de que se trate de un error “temporal”).

Puede encontrar una descripción más detallada de cada clase aquí .

En general, si maneja los errores con su clase, no necesitará saber exactamente cada error (utilizando error.Number property o exception.Number que es solo un atajo para el primer SqlError en esa lista). Esto tiene el inconveniente de que puede volver a intentarlo cuando no es útil (o no se puede recuperar el error). Sugeriría un enfoque de dos pasos :

  • Compruebe si hay códigos de error conocidos (enumere los códigos de error con SELECT * FROM master.sys.messages ) para ver qué desea controlar (saber cómo). Esa vista contiene mensajes en todos los idiomas admitidos, por lo que es posible que deba filtrarlos por la columna msglangid (por ejemplo, 1033 para inglés).
  • Para todo lo demás, confíe en la clase de error, reintentando cuando Class es 13 o mayor que 16 (y reconectando si es 20 o superior).
  • Los errores con una gravedad superior a 21 (22, 23 y 24) son errores graves y la poca espera no solucionará esos problemas (la base de datos también puede estar dañada).

La estrategia para reintentar depende del error que esté manejando: recursos gratuitos, espere a que finalice una operación pendiente, realice una acción alternativa, etc. En general, debe volver a intentar solo si todos los errores son “reintentables”:

 bool rebuildConnection = true; // First try connection must be open for (int i=0; i < MaximumNumberOfRetries; ++i) { try { // (Re)Create connection to SQL Server if (rebuildConnection) { if (connection != null) connection.Dispose(); // Create connection and open it... } // Perform your task // No exceptions, task has been completed break; } catch (SqlException e) { if (e.Errors.Cast().All(x => CanRetry(x))) { // What to do? Handle that here, also checking Number property. // For Class < 20 you may simply Thread.Sleep(DelayOnError); rebuildConnection = e.Errors .Cast() .Any(x => x.Class >= 20); continue; } throw; } } 

Envuelva todo en try / finally para disponer adecuadamente de la conexión. Con esta simple y falsa ingenuidad de la función CanRetry() :

 private static readonly int[] RetriableClasses = { 13, 16, 17, 18, 19, 20, 21, 22, 24 }; private static bool CanRetry(SqlError error) { // Use this switch if you want to handle only well-known errors, // remove it if you want to always retry. A "blacklist" approach may // also work: return false when you're sure you can't recover from one // error and rely on Class for anything else. switch (error.Number) { // Handle well-known error codes, } // Handle unknown errors with severity 21 or less. 22 or more // indicates a serious error that need to be manually fixed. // 24 indicates media errors. They're serious errors (that should // be also notified) but we may retry... return RetriableClasses.Contains(error.Class); // LINQ... } 

Algunas formas bastante complicadas de encontrar una lista de errores no críticos aquí .

Por lo general, incrusto este código (repetitivo) en un método (donde puedo ocultar todas las cosas sucias hechas para crear / eliminar / recrear la conexión) con esta firma:

 public static void Try( Func connectionFactory, Action performer); 

Para ser usado así:

 Try( () => new SqlConnection(connectionString), cmd => { cmd.CommandText = "SELECT * FROM master.sys.messages"; using (var reader = cmd.ExecuteReader()) { // Do stuff } }); 

Tenga en cuenta que esqueleto (reintentar en caso de error) también se puede usar cuando no se está trabajando con SQL Server (en realidad se puede usar para muchas otras operaciones como E / S y cosas relacionadas con la red, así que sugeriría escribir una función general y reutilizarlo extensivamente).

Puede simplemente las propiedades SqlConnectionStringBuilder volver a intentar la conexión sql.

var conBuilder = new SqlConnectionStringBuilder(Configuration["Database:Connection"]); conBuilder.ConnectTimeout = 90; conBuilder.ConnectRetryInterval = 15; conBuilder.ConnectRetryCount = 6;

Nota: – Se requiere .Net 4.5 o posterior.

No conozco ningún estándar, pero aquí hay una lista de excepciones de Sql-Server que, en general, he considerado como reutilizables, con un sabor DTC también:

 catch (SqlException sqlEx) { canRetry = ((sqlEx.Number == 1205) // 1205 = Deadlock || (sqlEx.Number == -2) // -2 = TimeOut || (sqlEx.Number == 3989) // 3989 = New request is not allowed to start because it should come with valid transaction descriptor || (sqlEx.Number == 3965) // 3965 = The PROMOTE TRANSACTION request failed because there is no local transaction active. || (sqlEx.Number == 3919) // 3919 Cannot enlist in the transaction because the transaction has already been committed or rolled back || (sqlEx.Number == 3903)); // The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. } 

Con respecto a los rebashs, sugiera que al azar se agregue demora entre rebashs, para reducir las posibilidades de, por ejemplo, las mismas 2 transacciones de locking de nuevo .

Con algunos de los errores relacionados con DTC , es posible que sea necesario eliminar la conexión (o, en el peor de los casos, SqlClient.SqlConnection.ClearAllPools() ); de lo contrario, se devuelve una conexión inactiva al grupo.

En el espíritu de mantener las preocupaciones separadas, me estoy imaginando tres capas lógicas en este caso …

  1. La capa de aplicación, que llama a una capa “manejador de dependencias escamosas”
  2. La capa “manejador de dependencias escamosas”, que llama a la capa de acceso a datos
  3. La capa de acceso a datos, que no sabe nada de flakiness

Toda la lógica para reintentar estaría en esa capa de controlador, para no contaminar la capa de acceso a los datos con una lógica que no sea la de comunicarse con la base de datos. (Como tal, su código de acceso a datos no necesita cambiar. Y no tendría que preocuparse por la “falta de tinta” si lógicamente necesita cambiar para obtener nuevas características).

Un patrón para volver a intentar podría basarse en la captura de excepciones específicas en un ciclo de contador. (El contador es solo para evitar volver a intentar infinitamente). Algo como esto:

 public SomeReturnValue GetSomeData(someIdentifier) { var tries = 0; while (tries < someConfiguredMaximum) { try { tries++; return someDataAccessObject.GetSomeData(someIdentifier); } catch (SqlException e) { someLogger.LogError(e); // maybe wait for some number of milliseconds? make the method async if possible } } throw new CustomException("Maximum number of tries has been reached."); } 

Esto se repetirá varias veces configuradas, volviendo a intentar hasta que funcione o hasta que se scope el máximo. Después de ese número máximo, se lanza una excepción personalizada para que la maneje la aplicación. Puede ajustar aún más el manejo de excepciones inspeccionando la SqlException específica atrapada. Tal vez basado en el mensaje de error, es posible que desee continuar con el ciclo o lanzar una CustomException .

Puede refinar aún más esta lógica capturando otros tipos de excepciones, examinándolos, etc. El punto principal aquí es que esta responsabilidad se mantiene aislada de una capa lógica específica en la aplicación, lo más transparente posible para las otras capas. Idealmente, la capa de controlador y la capa de acceso a datos implementan las mismas interfaces. De esta forma, si alguna vez mueve el código a un entorno más estable y ya no necesita la capa de controlador, sería trivial eliminarlo sin necesidad de realizar ningún cambio en la capa de la aplicación.

No sé de un estándar real. Puede intentar mirar el Bloque de aplicación de manejo de fallas transitorias . Es bastante robusto, pero puede ser un poco demasiado “empresarial” para algunos usuarios. Otro enfoque podría ser utilizar un marco de aspecto para atrapar errores. O bien, bueno, viejo try / catch funcionará.

En cuanto a determinar qué reintentar, generalmente querrá ver la excepción. SqlException proporciona bastante información sobre el origen de su problema, pero puede ser doloroso analizarlo. Junté un código para separarlos e intentar determinar qué es reutilizable y qué no. Esto no se ha mantenido por un tiempo, por lo que probablemente debería tomarlo como punto de partida en lugar de como producto terminado. Además, esto estaba dirigido a SQL Azure, por lo que es posible que no se aplique por completo a su situación (por ejemplo, la limitación de recursos es una función específica de Azure, IIRC).

 ///  /// Helps to extract useful information from SQLExceptions, particularly in SQL Azure ///  public class SqlExceptionDetails { public ResourcesThrottled SeriouslyExceededResources { get; private set; } public ResourcesThrottled SlightlyExceededResources { get; private set; } public OperationsThrottled OperationsThrottled { get; private set; } public IList Errors { get; private set; } public string ThrottlingMessage { get; private set; } public bool ShouldRetry { get; private set; } public bool ShouldRetryImmediately { get; private set; } private SqlExceptionDetails() { this.ShouldRetryImmediately = false; this.ShouldRetry = true; this.SeriouslyExceededResources = ResourcesThrottled.None; this.SlightlyExceededResources = ResourcesThrottled.None; this.OperationsThrottled = OperationsThrottled.None; Errors = new List(); } public SqlExceptionDetails(SqlException exception) :this(exception.Errors.Cast()) { } public SqlExceptionDetails(IEnumerable errors) : this() { List errorWrappers = (from err in errors select new SqlErrorWrapper(err)).Cast().ToList(); this.ParseErrors(errorWrappers); } public SqlExceptionDetails(IEnumerable errors) : this() { ParseErrors(errors); } private void ParseErrors(IEnumerable errors) { foreach (ISqlError error in errors) { SqlErrorCode code = GetSqlErrorCodeFromInt(error.Number); this.Errors.Add(code); switch (code) { case SqlErrorCode.ServerBusy: ParseServerBusyError(error); break; case SqlErrorCode.ConnectionFailed: //This is a very non-specific error, can happen for almost any reason //so we can't make any conclusions from it break; case SqlErrorCode.DatabaseUnavailable: ShouldRetryImmediately = false; break; case SqlErrorCode.EncryptionNotSupported: //this error code is sometimes sent by the client when it shouldn't be //Therefore we need to retry it, even though it seems this problem wouldn't fix itself ShouldRetry = true; ShouldRetryImmediately = true; break; case SqlErrorCode.DatabaseWorkerThreadThrottling: case SqlErrorCode.ServerWorkerThreadThrottling: ShouldRetry = true; ShouldRetryImmediately = false; break; //The following errors are probably not going to resolved in 10 seconds //They're mostly related to poor query design, broken DB configuration, or too much data case SqlErrorCode.ExceededDatabaseSizeQuota: case SqlErrorCode.TransactionRanTooLong: case SqlErrorCode.TooManyLocks: case SqlErrorCode.ExcessiveTempDBUsage: case SqlErrorCode.ExcessiveMemoryUsage: case SqlErrorCode.ExcessiveTransactionLogUsage: case SqlErrorCode.BlockedByFirewall: case SqlErrorCode.TooManyFirewallRules: case SqlErrorCode.CannotOpenServer: case SqlErrorCode.LoginFailed: case SqlErrorCode.FeatureNotSupported: case SqlErrorCode.StoredProcedureNotFound: case SqlErrorCode.StringOrBinaryDataWouldBeTruncated: this.ShouldRetry = false; break; } } if (this.ShouldRetry && Errors.Count == 1) { SqlErrorCode code = this.Errors[0]; if (code == SqlErrorCode.TransientServerError) { this.ShouldRetryImmediately = true; } } if (IsResourceThrottled(ResourcesThrottled.Quota) || IsResourceThrottled(ResourcesThrottled.Disabled)) { this.ShouldRetry = false; } if (!this.ShouldRetry) { this.ShouldRetryImmediately = false; } SetThrottlingMessage(); } private void SetThrottlingMessage() { if (OperationsThrottled == Sql.OperationsThrottled.None) { ThrottlingMessage = "No throttling"; } else { string opsThrottled = OperationsThrottled.ToString(); string seriousExceeded = SeriouslyExceededResources.ToString(); string slightlyExceeded = SlightlyExceededResources.ToString(); ThrottlingMessage = "SQL Server throttling encountered. Operations throttled: " + opsThrottled + ", Resources Seriously Exceeded: " + seriousExceeded + ", Resources Slightly Exceeded: " + slightlyExceeded; } } private bool IsResourceThrottled(ResourcesThrottled resource) { return ((this.SeriouslyExceededResources & resource) > 0 || (this.SlightlyExceededResources & resource) > 0); } private SqlErrorCode GetSqlErrorCodeFromInt(int p) { switch (p) { case 40014: case 40054: case 40133: case 40506: case 40507: case 40508: case 40512: case 40516: case 40520: case 40521: case 40522: case 40523: case 40524: case 40525: case 40526: case 40527: case 40528: case 40606: case 40607: case 40636: return SqlErrorCode.FeatureNotSupported; } try { return (SqlErrorCode)p; } catch { return SqlErrorCode.Unknown; } } ///  /// Parse out the reason code from a ServerBusy error. ///  /// Basic idea extracted from http://msdn.microsoft.com/en-us/library/gg491230.aspx ///  ///  private void ParseServerBusyError(ISqlError error) { int idx = error.Message.LastIndexOf("Code:"); if (idx < 0) { return; } string reasonCodeString = error.Message.Substring(idx + "Code:".Length); int reasonCode; if (!int.TryParse(reasonCodeString, out reasonCode)) { return; } int opsThrottledInt = (reasonCode & 3); this.OperationsThrottled = (OperationsThrottled)(Math.Max((int)OperationsThrottled, opsThrottledInt)); int slightResourcesMask = reasonCode >> 8; int seriousResourcesMask = reasonCode >> 16; foreach (ResourcesThrottled resourceType in Enum.GetValues(typeof(ResourcesThrottled))) { if ((seriousResourcesMask & (int)resourceType) > 0) { this.SeriouslyExceededResources |= resourceType; } if ((slightResourcesMask & (int)resourceType) > 0) { this.SlightlyExceededResources |= resourceType; } } } } public interface ISqlError { int Number { get; } string Message { get; } } public class SqlErrorWrapper : ISqlError { public SqlErrorWrapper(SqlError error) { this.Number = error.Number; this.Message = error.Message; } public SqlErrorWrapper() { } public int Number { get; set; } public string Message { get; set; } } ///  /// Documents some of the ErrorCodes from SQL/SQL Azure. /// I have not included all possible errors, only the ones I thought useful for modifying runtime behaviors ///  ///  /// Comments come from: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management-in-sql-azure.aspx ///  public enum SqlErrorCode : int { ///  /// We don't recognize the error code returned ///  Unknown = 0, ///  /// A SQL feature/function used in the query is not supported. You must fix the query before it will work. /// This is a rollup of many more-specific SQL errors ///  FeatureNotSupported = 1, ///  /// Probable cause is server maintenance/upgrade. Retry connection immediately. ///  TransientServerError = 40197, ///  /// The server is throttling one or more resources. Reasons may be available from other properties ///  ServerBusy = 40501, ///  /// You have reached the per-database cap on worker threads. Investigate long running transactions and reduce server load. /// http://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management.aspx#Throttling_Limits ///  DatabaseWorkerThreadThrottling = 10928, ///  /// The per-server worker thread cap has been reached. This may be partially due to load from other databases in a shared hosting environment (eg, SQL Azure). /// You may be able to alleviate the problem by reducing long running transactions. /// http://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management.aspx#Throttling_Limits ///  ServerWorkerThreadThrottling = 10929, ExcessiveMemoryUsage = 40553, BlockedByFirewall = 40615, ///  /// The database has reached the maximum size configured in SQL Azure ///  ExceededDatabaseSizeQuota = 40544, ///  /// A transaction ran for too long. This timeout seems to be 24 hours. ///  ///  /// 24 hour limit taken from http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management-in-sql-azure.aspx ///  TransactionRanTooLong = 40549, TooManyLocks = 40550, ExcessiveTempDBUsage = 40551, ExcessiveTransactionLogUsage = 40552, DatabaseUnavailable = 40613, CannotOpenServer = 40532, ///  /// SQL Azure databases can have at most 128 firewall rules defined ///  TooManyFirewallRules = 40611, ///  /// Theoretically means the DB doesn't support encryption. However, this can be indicated incorrectly due to an error in the client library. /// Therefore, even though this seems like an error that won't fix itself, it's actually a retryable error. ///  ///  /// http://social.msdn.microsoft.com/Forums/en/ssdsgetstarted/thread/e7cbe094-5b55-4b4a-8975-162d899f1d52 ///  EncryptionNotSupported = 20, ///  /// User failed to connect to the database. This is probably not recoverable. ///  ///  /// Some good info on more-specific debugging: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx ///  LoginFailed = 18456, ///  /// Failed to connect to the database. Could be due to configuration issues, network issues, bad login... hard to tell ///  ConnectionFailed = 4060, ///  /// Client tried to call a stored procedure that doesn't exist ///  StoredProcedureNotFound = 2812, ///  /// The data supplied is too large for the column ///  StringOrBinaryDataWouldBeTruncated = 8152 }