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

by

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

More Related Contents:

  1. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  2. Call a stored procedure with parameter in c#
  3. Insert 2 million rows into SQL Server quickly
  4. Calling stored procedure with return value
  5. SQL command INSERT is working but the data not appear in table
  6. Return value from SQL Server Insert command using c#
  7. How to pass sqlparameter to IN()? [duplicate]
  8. How do I return multiple result sets with SqlCommand?
  9. SqlConnection SqlCommand SqlDataReader IDisposable
  10. SqlBulkCopy from a List
  11. Use of SqlParameter in SQL LIKE clause not working
  12. Using DateTime in a SqlParameter for Stored Procedure, format error
  13. How to run multiple SQL commands in a single SQL connection?
  14. Connection timeout for SQL server
  15. MultipleActiveResultSets=True or multiple connections?
  16. WHERE IN (array of IDs)
  17. How can I retrieve a list of parameters from a stored procedure in SQL Server
  18. Creating a SQL Server table from a C# datatable
  19. SQL: Update a row and returning a column value with 1 query
  20. How to keep single SQL Server connection instance open for multiple request in C#?
  21. How to use SqlCommand to CREATE DATABASE with parameterized db name?
  22. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  23. Using SqlDataAdapter to insert a row
  24. What size do you use for varchar(MAX) in your parameter declaration?
  25. Add WHERE clauses to SQL dynamically / programmatically
  26. Unable to connect to localDB in VS2012 – “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”
  27. Why does the SqlParameter name/value constructor treat 0 as null?
  28. How do I connect to a SQL database from C#?
  29. Passing array to a SQL Server Stored Procedure
  30. How to give ADO.NET Parameters

Leave a Comment