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

Question

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

我们在SQL Server 2000中遇到了严重的死锁问题。 我们的团队正在努力优化sql代码和事务以尽量减少这些问题，但我想让这个Database类对死锁情况更加强健。

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

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 块里吗？

- Victor Rodrigues

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

- sambo99

---

在retryCount--之后，考虑在重试循环中添加一个短暂的延迟，例如Thread.Sleep(100);，以避免对SQL Server造成过大的负荷。 - Doug Knudsen

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

- Neil

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

- eftpotrm

---

我对使用上述解决方案很感兴趣，但希望能获得更多关于如何适用于我的情况的信息。 - Kobojunkie

对于“upvote for the random timer”，我的翻译如下：给随机定时器点赞。对于“Sams solution hammers the sql server”，我的翻译如下：Sam的解决方案严重影响了SQL服务器。 - DeveloperChris

我建议将最后一行改为throw;而不是throw exDead;。 - Phil Haselden

Answer 4

如果您遇到死锁问题，最好查看SQL代码在做什么。例如，如果您使用的是可序列化隔离级别（或者相应的rdbms），那么升级锁定死锁非常容易发生，并且可以通过重新排列查询或者（至少在SQL Server中）使用(UPDLOCK)早期采取写锁（这样您就不会得到竞争读锁）等方法进行缓解。

重试可能会有所不同…例如，如果您在TransactionScope中，则可能已经中止。但是，从最纯粹的层面上讲，如果我与数据库通信出现问题，我希望我的代码能够尽早感知并恐慌…在这种特殊情况下，重试似乎有点hacky。

- Marc Gravell

---

我们正在考虑所有的SQL代码和事务，这是正在进行的工作，我们不仅仅是试图通过在C#中重新调用来欺骗死锁。但并发可能会导致死锁受害者，因此我们希望在发生这种情况时使我们的应用程序更加健壮。 - Victor Rodrigues

Victor - 你可以并且应该通过在数据层解决死锁来处理它们。一旦解决了，你就不需要在C#中做任何事情。 - Dave Markle

@ Dave - 这可能过于简单化了。 - Marc Gravell

@Marc - 确实如此，但对于那些提出这类问题的人来说，99%的情况下，他们来自一个没有很多交易处理经验的背景，他们会试图在不止一个地方解决问题，从而使问题变得过于复杂化。 - Dave Markle