如何获得有效的SQL服务器死锁处理在C＃中使用ADO？ [英] 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 code和交易，以尽量减少这些事件，但我想使这一数据库类对僵局的情况下强劲

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.

我们希望死锁牺牲品，以延迟一段时间后可能重试，但我不知道这是否是可能的。这里是code的方法，我们使用：

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 code和拿地为何这一僵局正在发生的底部。修复这可能隐藏着一个更大的问题（如丢失索引或坏的查询）。

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（*）已被称为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;
  }
}

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