如何SqlDataReader.Read期间从僵局异常中恢复（） [英] How to recover from deadlock exception during SqlDataReader.Read()

问题描述

我的.NET应用程序节目的同时，从SQL Server读取偶尔死锁事件日志。这通常是非常罕见的，因为我们已经优化了我们的疑问，以避免死锁，但它们有时仍时有发生。在过去，我们已经有同时呼吁对我们的的SqlCommand 实例的ExecuteReader 功能发生了一些死锁。为了解决这个问题，我们已经添加重试代码简单地再次运行该查询，如下所示：

The event logs for my .NET application show that it occasionally deadlocks while reading from Sql Server. This is usually very rare as we have already optimized our queries to avoid deadlocks, but they sometimes still occur. In the past, we've had some deadlocks that occur while calling the ExecuteReader function on our SqlCommand instance. To fix this we've added retry code to simply run the query again, like this:

//try to run the query five times if a deadlock happends
int DeadLockRetry = 5;

while (DeadLockRetry > 0)
{
    try
    {
        return dbCommand.ExecuteReader();
    }
    catch (SqlException err)
    {
        //throw an exception if the error is not a deadlock or the deadlock still exists after 5 tries
        if (err.Number != 1205 || --DeadLockRetry == 0)
            throw;
    }
}

这工作得很好，其中死锁发生的情况下，初始查询过程中执行，但现在我们正在死锁，同时通过结果使用上返回的 SqlDataReader的阅读（）函数迭代$ C>。

This worked very well for the case where the deadlock happened during the initial query execute, but now we are getting deadlocks while iterating through the results using the Read() function on the returned SqlDataReader.

再次我不关心优化查询，但只是想在一个僵局出现罕见的情况下恢复。我想使用类似的重试过程。我可以创建我自己的类，从 SqlDataReader的继承它只是简单地重写重试代码读功能。像这样的：

Again, I'm not concerned about optimizing the query, but merely trying to recover in the rare case that a deadlock occurs. I was thinking about using a similar retry process. I could create my own class that inherits from SqlDataReader which simply overrides the Read function with retry code. Like this:

public class MyDataReader : SqlDataReader
{
    public override bool Read()
    {
        int DeadLockRetry = 5;
        while (DeadLockRetry > 0)
        {
            try
            {
                return base.Read();
            }
            catch (SqlException ex)
            {
                if (ex.ErrorCode != 1205 || --DeadLockRetry == 0)
                    throw;
            }
        }
        return false;
    }
}

这是正确的做法？我想，以确保记录没有在读者跳过。将重试读死锁后跳过任何行？另外，我应该叫 Thread.sleep代码重试之间给数据库时要走出僵局的状态，或者是这就够了。这种情况下，不容易繁殖，所以我想可以肯定这件事之前，我修改任何我的代码

Is this the right approach? I want to be sure that records aren't skipped in the reader. Will retrying Read after a deadlock skip any rows? Also, should I call Thread.Sleep between retries to give the database time to get out of the deadlock state, or is this enough. This case is not easy to reproduce so I'd like to be sure about it before I modify any of my code.

编辑：

根据要求，一些关于我的情况的详细信息：在一个案例中，我有一个执行加载需要更新记录ID列表的查询过程。然后，我通过ID的该列表重复使用读功能并运行该记录，最终将更新数据库中该记录的值的更新过程。 （没有，就没有办法在初始查询执行更新，发生对返回的每个记录许多其他东西）。此代码一直致力于罚款一段时间，但我们运行了相当多的每条记录的代码，所以我可以想像这些过程之一是创建的初始表锁正在读通过。

As requested, some more info about my situation: In one case, I have a process that performs a query that loads a list of record ids that need to be updated. Then I iterate through that list of ids using the Read function and run an update process on that record, which will eventually update a value for that record in the database. (No, there is no way to perform the update in the initial query, many other things occur for each record that is returned). This code has been working fine for a while, but we are running quite a bit of code for each record, so I can imagine one of those processes is creating a lock on the initial table being read through.

经过一番思考，皮蓬的建议，使用一个数据结构来存储结果可能会修正这种情况。我可以存储在名单，LT返回的ID; INT> ，并通过循环。这样的行上的锁可以立即删除。

After some thought, Scottie's suggestion to use a data structure to store the results would likely fix this situation. I could store the ids returned in a List<int> and loop through that. That way the locks on the rows can be removed right away.

不过，我仍然有兴趣知道，如果有从死锁在读操作恢复的一般方法

However, I would still be interested in knowing if there is a general way to recover from deadlocks on a read.

推荐答案

您整个事务陷入僵局丢失。你必须从头开始重新启动，从的办法的，你读数据读取器的水平之上。你说你读一些记录，然后在循环更新。您有再次读取记录重新启动：

Your entire transaction is lost in a deadlock. You have to restart from scratch, from way above the level where you read a data reader. You say that you read some records, then update them in a loop. You have to restart with reading again the records:

function DoWork() {
  using (TransactionScope scope = new TransactionScope(...)) {
    cmd = new SqlCommand("select ...");
    using (DataReader rdr = cmd.ExecuteReader ()) {
        while(rdr.Read()) {
          ... process each record
        }
    }
    scope.Complete ();
  }
}

您必须重试的全部 的DoWork 电话：

retries = 0;
success = false;
do {
 try {
  DoWork ();
  success = true;
 }
 catch (SqlException e) {
   if (can retry e)  {
     ++retries;
   }
   else {
     throw;
   }
 }
} while (!success);

这篇关于如何SqlDataReader.Read期间从僵局异常中恢复（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！