问题描述

我有一个在 TransactionScope 内运行的代码块，在这个代码块中我对数据库进行了多次调用.选择、更新、创建和删除，整个范围.当我执行删除时，我使用 SqlCommand 的扩展方法执行它，如果它死锁，它将自动重新提交查询，因为此查询可能会遇到死锁.

I have a block of code that runs within a TransactionScope and within this block of code I make several calls to the DB. Selects, Updates, Creates, and Deletes, the whole gamut. When I execute my delete I execute it using an extension method of the SqlCommand that will automatically resubmit the query if it deadlocks as this query could potentially hit a deadlock.

我相信当遇到死锁并且函数尝试重新提交查询时会出现问题.这是我收到的错误:

I believe the problem occurs when a deadlock is hit and the function tries to resubmit the query. This is the error I receive:

与当前连接关联的事务已完成但尚未处理.必须先释放事务，然后才能使用连接执行 SQL 语句.

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

这是执行查询的简单代码(下面的所有代码都在 TransactionScope 的使用范围内执行):

This is the simple code that executes the query (all of the code below executes within the using of the TransactionScope):

using (sqlCommand.Connection = new SqlConnection(ConnectionStrings.App))
{
    sqlCommand.Connection.Open();
    sqlCommand.ExecuteNonQueryWithDeadlockHandling();
}

这里是重新提交死锁查询的扩展方法:

Here is the extension method that resubmits the deadlocked query:

public static class SqlCommandExtender
{
    private const int DEADLOCK_ERROR = 1205;
    private const int MAXIMUM_DEADLOCK_RETRIES = 5;
    private const int SLEEP_INCREMENT = 100;

    public static void ExecuteNonQueryWithDeadlockHandling(this SqlCommand sqlCommand)
    {
        int count = 0;
        SqlException deadlockException = null;

        do
        {
            if (count > 0) Thread.Sleep(count * SLEEP_INCREMENT);
            deadlockException = ExecuteNonQuery(sqlCommand);
            count++;
        }
        while (deadlockException != null && count < MAXIMUM_DEADLOCK_RETRIES);

        if (deadlockException != null) throw deadlockException;
    }

    private static SqlException ExecuteNonQuery(SqlCommand sqlCommand)
    {
        try
        {
            sqlCommand.ExecuteNonQuery();
        }
        catch (SqlException exception)
        {
            if (exception.Number == DEADLOCK_ERROR) return exception;
            throw;
        }

        return null;
    }
}

错误发生就行了:

sqlCommand.ExecuteNonQuery();

推荐答案

不要忘记从 TransactionScope 中禁止选择语句.在 SQL Server 2005 及更高版本中，即使您使用 with(nolock)，仍然会在 select 触及的那些表上创建锁.看看这个，它向您展示了 如何设置和使用 TransactionScope.

Don't forget to supress your select statements from your TransactionScope. In SQL Server 2005 and above, even when you use with(nolock), locks are still created on those tables the select touches. Check this out, it shows you how to setup and use TransactionScope.

using(TransactionScope ts = new TransactionScope 
{ 
  // db calls here are in the transaction 
  using(TransactionScope tsSuppressed = new TransactionScope (TransactionScopeOption.Suppress)) 
  { 
    // all db calls here are now not in the transaction 
  } 
} 

这篇关于TransactionScope 过早完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！