Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 581
Victor Rodrigues Avatar asked Nov 26 '08 13:11

Victor Rodrigues


People also ask

How can we reduce deadlock in SQL Server?

Useful ways to avoid and minimize SQL Server deadlocksTry to keep transactions short; this will avoid holding locks in a transaction for a long period of time. Access objects in a similar logical manner in multiple transactions. Create a covering index to reduce the possibility of a deadlock.

How can deadlocks be resolved?

Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order - meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.

How can we avoid deadlock while updating SQL Server?

Update lock (U) is used to avoid deadlocks. Unlike the Exclusive lock, the Update lock places a Shared lock on a resource that already has another shared lock on it.


2 Answers

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;   } } 
like image 150
Sam Saffron Avatar answered Sep 30 '22 06:09

Sam Saffron


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;         }     }); } 
like image 26
Neil Avatar answered Sep 30 '22 06:09

Neil