如何使用 ADO 在 C# 中高效处理 Sql Server 死锁？

Question

我有一个“数据库”类，它用作 ADO.net 的包装器。 例如，当我需要执行一个过程时，我调用Database.ExecuteProcedure（procedureName，parametersAndItsValues）。

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

我们希望死锁受害者在延迟一段时间后重试，但我不知道这是否可能。 以下是我们使用的方法的代码：

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 块内执行此处理吗？

Answer 1

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

其次，我会检查我的体系结构，以确认死锁语句确实需要频繁调用（select count(*) from bob 是否必须每秒调用 100 次？）。

但是，如果您确实需要一些死锁支持并且 SQL 或体系结构中没有错误，请尝试以下操作。 （注意：我必须对每秒支持数千个查询的系统使用此技术，并且很少会遇到死锁）

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

Answer 2

基于@Sam 的回复，我提出了一个通用的重试包装方法：

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

Answer 3

如果可以在数据层解决死锁，那绝对是可行的方法。 锁定提示、重新设计模块的工作方式等等。 不过，NoLock 并不是万能药 - 有时由于事务完整性的原因而无法使用，并且我遇到过对所有相关表进行直接（尽管复杂）数据读取 NoLock 的情况，但仍然会导致其他查询阻塞。

无论如何 - 如果由于某种原因你无法在数据层解决它，怎么样？

bool OK = false;
Random Rnd = new Random();

while(!OK)
{
    try
    {
        rows = Command.ExecuteNonQuery();
        OK = true;
    }
    catch(Exception exDead)
    {
        if(exDead.Message.ToLower().Contains("deadlock"))
            System.Threading.Thread.Sleep(Rnd.Next(1000, 5000));
        else
            throw exDead;
    }
}

Answer 4

如果您遇到死锁问题，最好看看 SQL 代码在做什么。 例如，如果您具有可序列化的隔离级别（或 RDBMS 中的任何等效级别），则很容易创建锁升级死锁 - 并且可以通过几种方式缓解，例如重新排序查询，或（在 SQL Server 中）至少）使用 (UPDLOCK) 提前获取写锁（这样您就不会获得竞争的读锁）。

重试的情况会很复杂……例如，如果您位于 TransactionScope 中，它可能已经中止。 但只是在纯粹的层面上 - 如果我在与数据库通信时遇到问题，我希望我的代码出现恐慌，并且尽早恐慌......在这种特定情况下，重试似乎有点hacky。