How to get efficient Sql Server deadlock handling in C# with ADO?

Question

I have a class 'Database' that works as a wrapper for ADO.net. For instance, when I need to execute a procedure, I call Database.ExecuteProcedure(procedureName, parametersAndItsValues).

We are experiencing serious problems with Deadlock situations in SQL Server 2000. Part of our team is working on the sql code and transactions to minimize these events, but I'm thinking about making this Database class robust against deadlock situations.

We want the deadlock victim to retry perhaps after some time delay, but I don't know if it is possible. Here is the code for a method we use:

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; } 

Can I do this handling inside a catch block?

Answer 1

First, I would review my SQL 2000 code and get to the bottom of why this deadlock is happening. Fixing this may be hiding a bigger problem (Eg. missing index or bad query).

Second I would review my architecture to confirm the deadlocking statement really needs to be called that frequently (Does select count(*) from bob have to be called 100 times a second?).

However, if you really need some deadlock support and have no errors in your SQL or architecture try something along the following lines. (Note: I have had to use this technique for a system supporting thousands of queries per second and would hit deadlocks quite rarely)

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 2

Building on @Sam's response, I present a general purpose retry wrapper method:

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;         }     }); }