如何使用 ADO 在 C# 中获得高效的 Sql Server 死锁处理? [英] How to get efficient Sql Server deadlock handling in C# with ADO?

问题描述

我有一个作为 ADO.net 包装器的数据库"类.例如，当我需要执行一个过程时，我调用 Database.ExecuteProcedure(procedureName, parametersAndItsValues).

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).

我们在 SQL Server 2000 中遇到了死锁情况的严重问题.我们团队的一部分正在研究 sql 代码和事务以最大限度地减少这些事件，但我正在考虑使这个 Database 类对死锁情况具有鲁棒性.

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

我可以在 catch 块中进行这种处理吗?

Can I do this handling inside a catch block?

推荐答案

首先，我将查看我的 SQL 2000 代码，并深入了解为什么会发生这种死锁.解决这个问题可能会隐藏一个更大的问题(例如，缺少索引或错误的查询).

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).

其次，我将检查我的架构，以确认确实需要如此频繁地调用死锁语句(select count(*) from bob 是否必须每秒调用 100 次?).

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?).

但是，如果您确实需要一些死锁支持并且您的 SQL 或架构中没有错误，请尝试以下几行.(注意:我不得不将这种技术用于每秒支持数千个查询的系统，并且很少会遇到死锁)

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

这篇关于如何使用 ADO 在 C# 中获得高效的 Sql Server 死锁处理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！