TransactionScope的prematurely完成 [英] TransactionScope Prematurely Completed

问题描述

我有code我多次调用数据库中一个TransactionScope和code此块中运行的块。选择，更新，创建和删除，整个色域。当我执行我删除它使用将自动重新提交查询，如果它锁死因为此查询可能创下僵局的SqlCommand的扩展方法我执行。

我相信，当一个死锁被击中的函数试图重新提交查询时出现问题。这是我收到的错误：

  

与当前连接关联的事务已完成，但还没有被释放。该交易必须处理之前，连接可用于执行SQL语句。

这是一个执行的查询（所有的code以下的使用TransactionScope内执行）简单的code：

 使用（sqlCommand.Connection =新的SqlConnection（ConnectionStrings.App））
{
    sqlCommand.Connection.Open（）;
    sqlCommand.ExecuteNonQueryWithDeadlockHandling（）;
}
 

下面是重新提交陷入僵局查询扩展方法：

 公共静态类SqlCommandExtender
{
    私人const int的DEADLOCK_ERROR = 1205;
    私人const int的MAXIMUM_DEADLOCK_RETRIES = 5;
    私人const int的SLEEP_INCREMENT = 100;

    公共静态无效ExecuteNonQueryWithDeadlockHandling（此的SqlCommand的SqlCommand）
    {
        诠释计数= 0;
        SQLEXCEPTION deadlockException = NULL;

        做
        {
            如果（计数大于0）的Thread.Sleep（计数* SLEEP_INCREMENT）;
            deadlockException =的ExecuteNonQuery（SqlCommand的）;
            算上++;
        }
        而（deadlockException = NULL和放大器;！＆安培; COUNT＆LT; MAXIMUM_DEADLOCK_RETRIES）;

        如果（！deadlockException = NULL）抛出deadlockException;
    }

    私有静态SQLEXCEPTION的ExecuteNonQuery（的SqlCommand的SqlCommand）
    {
        尝试
        {
            sqlCommand.ExecuteNonQuery（）;
        }
        赶上（SqlException异常）
        {
            如果（exception.Number == DEADLOCK_ERROR）返回的异常;
            扔;
        }

        返回null;
    }
}
 

该错误就行发生：

  sqlCommand.ExecuteNonQuery（）;
 

解决方案

不要忘了苏preSS你从你的TransactionScope的SELECT语句。在SQL Server 2005及以上的，甚至当你与（NOLOCK）使用，锁仍然在这些表中选择触摸创建。检查了这一点，就说明你如何设置和使用的TransactionScope 。

 使用（TransactionScope的TS =新的TransactionScope
{
  //数据库调用这里的交易
  使用（TransactionScope的tsSup pressed =新的TransactionScope（TransactionScopeOption.Sup preSS））
  {
    //所有的数据库调用这是我们目前没有在交易
  }
}
 

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:

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.

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

The error occurs on the line:

sqlCommand.ExecuteNonQuery();

解决方案

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的prematurely完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！