net - obtener instancias sql server c#

¿Cómo conseguir un manejo eficiente del punto muerto del Servidor Sql en C#con ADO?

Question

Tengo una ''Base de datos'' de clase que funciona como un contenedor para ADO.net. Por ejemplo, cuando necesito ejecutar un procedimiento, llamo a Database.ExecuteProcedure (procedureName, parametersAndItsValues).

Estamos experimentando problemas graves con situaciones de interbloqueo en SQL Server 2000. Parte de nuestro equipo está trabajando en el código sql y las transacciones para minimizar estos eventos, pero estoy pensando en robustecer esta clase de base de datos contra situaciones de interbloqueo.

Queremos que la víctima del interbloqueo vuelva a intentarlo después de un tiempo de retraso, pero no sé si es posible. Aquí está el código de un método que usamos:

public int ExecuteQuery(string query)
{
    int rows = 0;

    try
    {
        Command.Connection = Connection;
        Command.CommandType = CommandType.Text;

        if(DatabaseType != enumDatabaseType.ORACLE)
          Command.CommandText = query;
        else
          Command.CommandText ="BEGIN " +  query + " END;";



        if (DatabaseType != enumDatabaseType.SQLCOMPACT)
            Command.CommandTimeout = Connection.ConnectionTimeout;

        if (Connection.State == ConnectionState.Closed)
            Connection.Open();

        rows = Command.ExecuteNonQuery();
    }
    catch (Exception exp)
    {
        //Could I add here any code to handle it?
        throw new Exception(exp.Message);
    }
    finally
    {
        if (Command.Transaction == null)
        {
            Connection.Close();
            _connection.Dispose();
            _connection = null;
            Command.Dispose();
            Command = null;
        }
    }
    return rows;
}

¿Puedo hacer este manejo dentro de un bloque catch?

Answer 1

Si tiene problemas con los interbloqueos, sería mejor ver lo que está haciendo el código SQL. Por ejemplo, los bloqueos de escalamiento de bloqueo son muy fáciles de crear si tiene un nivel de aislamiento serializable (o lo que sea equivalente en sus rdbms), y se pueden mitigar de varias maneras, como reordenar consultas o (en SQL Server al menos) usando el (UPDLOCK) para tomar un bloqueo de escritura antes (para que no tenga un bloqueo de lectura competidor).

Reintentar va a ser mixto ... por ejemplo, si estás en un TransactionScope, es posible que ya haya abortado. Pero solo en el nivel purista: si tengo problemas para hablar con el DB, quiero que el código entre en pánico, y el pánico temprano ... volver a intentarlo parece un poco hacky en este escenario en particular.

Answer 2

Primero, revisaría mi código SQL 2000 y llegaría al fondo de por qué está pasando este punto muerto. Reparar esto puede estar ocultando un problema mayor (por ejemplo, índice faltante o consulta incorrecta).

En segundo lugar, revisaría mi arquitectura para confirmar que la declaración de bloqueo debe ser llamada con tanta frecuencia (¿El select count(*) from bob debe llamarse 100 veces por segundo?).

Sin embargo, si realmente necesita soporte de interbloqueo y no tiene errores en su SQL o arquitectura, intente algo a lo largo de las siguientes líneas. (Nota: He tenido que usar esta técnica para un sistema que soporta miles de consultas por segundo y que llegaría a bloqueos muy raramente)

int retryCount = 3;
bool success = false;  
while (retryCount > 0 && !success) 
{
  try
  {
     // your sql here
     success = true; 
  } 
  catch (SqlException exception)
  {
     if (exception.Number != 1205)
     {
       // a sql exception that is not a deadlock 
       throw; 
     }
     // Add delay here if you wish. 
     retryCount--; 
     if (retryCount == 0) throw;
  }
}

Answer 3

Sobre la base de la respuesta de @ Sam, presento un método de reintento de reintento de propósito general:

private static T Retry<T>(Func<T> func)
{
    int count = 3;
    TimeSpan delay = TimeSpan.FromSeconds(5);
    while (true)
    {
        try
        {
            return func();
        }
        catch(SqlException e)
        {
            --count;
            if (count <= 0) throw;

            if (e.Number == 1205)
                _log.Debug("Deadlock, retrying", e);
            else if (e.Number == -2)
                _log.Debug("Timeout, retrying", e);
            else
                throw;

            Thread.Sleep(delay);
        }
    }
}

private static void Retry(Action action)
{
    Retry(() => { action(); return true; });
}

// Example usage
protected static void Execute(string connectionString, string commandString)
{
    _log.DebugFormat("SQL Execute /"{0}/" on {1}", commandString, connectionString);

    Retry(() => {
        using (SqlConnection connection = new SqlConnection(connectionString))
        using (SqlCommand command = new SqlCommand(commandString, connection))
            command.ExecuteNonQuery();
    });
}

protected static T GetValue<T>(string connectionString, string commandString)
{
    _log.DebugFormat("SQL Scalar Query /"{0}/" on {1}", commandString, connectionString);

    return Retry(() => { 
        using (SqlConnection connection = new SqlConnection(connectionString))
        using (SqlCommand command = new SqlCommand(commandString, connection))
        {
            object value = command.ExecuteScalar();
            if (value is DBNull) return default(T);
            return (T) value;
        }
    });
}

Answer 4

Si el punto muerto se puede resolver en la capa de datos, definitivamente es el camino a seguir. Consejos de bloqueo, rediseñando la forma en que funciona el módulo, etc. Sin embargo, NoLock no es una panacea, a veces no es posible usarlo por razones de integridad transaccional y he tenido casos de lecturas de datos directas (aunque complejas) con todas las tablas relevantes NoLock''d que aún causaron bloqueos en otras consultas.

De todos modos, si no puede resolverlo en la capa de datos por el motivo que sea, ¿qué tal

bool OK = false;
Random Rnd = new Random();

while(!OK)
{
    try
    {
        rows = Command.ExecuteNonQuery();
        OK = true;
    }
    catch(Exception exDead)
    {
        if(exDead.Message.ToLower().Contains("deadlock"))
            System.Threading.Thread.Sleep(Rnd.Next(1000, 5000));
        else
            throw exDead;
    }
}