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?
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.
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.
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.
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; } }
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; } }); }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With