SQL “此 SqlTransaction 已完成；它不再可用。”...配置错误？

"This SqlTransaction has completed; it is no longer usable."... configuration error?

提问by White Island

I've been working on this for about a day and a half now, and searched numberous blogs and help articles on the Web. I found several questions on SO related to this error, but I didn't think they quite applied to my situation (or in some cases, unfortunately, I couldn't understand them well enough to implement :P). I'm not sure I can describe this well enough for help... but here goes:

我已经为此工作了大约一天半，并在网上搜索了大量博客和帮助文章。我在 SO 上发现了几个与此错误相关的问题，但我认为它们并不完全适用于我的情况（或者在某些情况下，不幸的是，我无法很好地理解它们以实现 :P）。我不确定我是否可以很好地描述这一点以获得帮助......但这里是：

We have a .NET app to track our resources. There's an export function to copy a resource to the time tracking system and the billing system; this accesses a stored procedure that links to the time and billing databases.

我们有一个 .NET 应用程序来跟踪我们的资源。有将资源复制到时间跟踪系统和计费系统的导出功能；这将访问链接到时间和计费数据库的存储过程。

I recently moved the billing system database to a new server (original server: Server 2003 SP2, SQL 2005; new server: Server 2008 R2, SQL 2008 R2). I have a Linked Server set up that points to the 2008 databases. I updated the stored procedure to point to the 2008 server, and then I got an error about MSDTC and RPC (http://www.safnet.com/writing/tech/archives/2007/06/server_myserver.html). I enabled 'rpc/rpc out' on the Linked Server and set MSDTC to allow Network Access (something like this: http://www.sqlwebpedia.com/content/msdtc-troubleshooting).

我最近将计费系统数据库移到了新服务器（原服务器：Server 2003 SP2、SQL 2005；新服务器：Server 2008 R2、SQL 2008 R2）。我有一个指向 2008 数据库的链接服务器设置。我更新了存储过程以指向 2008 服务器，然后我收到了关于 MSDTC 和 RPC 的错误（http://www.safnet.com/writing/tech/archives/2007/06/server_myserver.html）。我在链接服务器上启用了“rpc/rpc out”并将 MSDTC 设置为允许网络访问（类似于：http: //www.sqlwebpedia.com/content/msdtc-troubleshooting）。

Now I'm getting the above, when I try to run the export function: "This SqlTransaction has completed; it is no longer usable." What seems odd to me is that when I just run the stored procedure (from SSMS), it says it completes successfully.

现在，当我尝试运行导出功能时，我得到了上述信息：“此 SqlTransaction 已完成；它不再可用。” 对我来说奇怪的是，当我刚刚运行存储过程（来自 SSMS）时，它说它成功完成。

Has anyone seen this before? Have I missed something in the configuration? I keep going over the same pages, and the only thing I found was that I didn't reboot after making the MSDTC changes (mentioned in here: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/7172223f-acbe-4472-8cdf-feec80fd2e64/).

有没有人见过这个？我在配置中遗漏了什么吗？我继续浏览相同的页面，唯一发现的是在进行 MSDTC 更改后我没有重新启动（此处提到：http: //social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders /thread/7172223f-acbe-4472-8cdf-feec80fd2e64/）。

I can post part or all of the stored procedure, if it would help... please let me know.

我可以发布部分或全部存储过程，如果有帮助的话...请告诉我。

采纳答案by Phil Sandler

I believe this error message is due to a "zombie transaction".

我相信此错误消息是由于“僵尸交易”造成的。

Look for possible areas where the transacton is being committed twice (or rolled back twice, or rolled back and committed, etc.). Does the .Net code commit the transaction after the SP has already committed it? Does the .Net code roll it back on encountering an error, then attempt to roll it back again in a catch (or finally) clause?

寻找事务被提交两次（或回滚两次，或回滚并提交等）的可能区域。.Net 代码是否在 SP 已经提交事务之后提交事务？.Net 代码是否在遇到错误时回滚它，然后尝试在 catch（或 finally）子句中再次回滚它？

It's possible an error condition was never being hit on the old server, and thus the faulty "double rollback" code was never hit. Maybe now you have a situation where there issome configuration error on the new server, and now the faulty code is getting hit via exception handling.

有可能在旧服务器上从未遇到过错误情况，因此从未遇到过错误的“双重回滚”代码。也许现在你遇到这样的情况存在是通过异常处理的新的服务器上的一些配置错误，现在的错误代码越来越命中。

Can you debug into the error code? Do you have a stack trace?

你能调试到错误代码中吗？你有堆栈跟踪吗？

回答by Phil Cooper

I had this recently after refactoring in a new connection manager. A new routine accepted a transaction so it could be run as part of a batch, problem was with a using block:

我最近在重构一个新的连接管理器后遇到了这个问题。一个新的例程接受了一个事务，因此它可以作为批处理的一部分运行，问题在于 using 块：

public IEnumerable<T> Query<T>(IDbTransaction transaction, string command, dynamic param = null)
{
  using (transaction.Connection)
  {
    using (transaction)
    {
      return transaction.Connection.Query<T>(command, new DynamicParameters(param), transaction, commandType: CommandType.StoredProcedure);
    }
  }
}

It looks as though the outer using was closing the underlying connection thus any attempts to commit or rollback the transaction threw up the message "This SqlTransaction has completed; it is no longer usable."

看起来好像外部 using 正在关闭底层连接，因此任何提交或回滚事务的尝试都会抛出消息 "This SqlTransaction has completed; it is no longer usable."

I removed the usings added a covering test and the problem went away.

我删除了 using 添加了一个覆盖测试，问题就消失了。

public IEnumerable<T> Query<T>(IDbTransaction transaction, string command, dynamic param = null)
{
  return transaction.Connection.Query<T>(command, new DynamicParameters(param), transaction, commandType: CommandType.StoredProcedure);
}

Check for anything that might be closing the connection while inside the context of a transaction.

检查在事务上下文中可能会关闭连接的任何内容。

回答by HydPhani

I have recently ran across similar situation. To debug in any VS IDE version, open exceptions from Debug (Ctrl + D, E) - check all checkboxes against the column "Thrown", and run the application in debug mode. I have realized that one of the tables was not imported properly in the new database, so internal Sql Exception was killing the connection, thus results into this error.

我最近遇到了类似的情况。要在任何 VS IDE 版本中进行调试，请从 Debug (Ctrl + D, E) 打开异常 - 选中“Thrown”列中的所有复选框，并在调试模式下运行应用程序。我意识到在新数据库中没有正确导入其中一个表，因此内部 Sql Exception 正在终止连接，从而导致此错误。

Gist of the story is, If Previously working code returns this error on a new database, this could be database schema missing issue, realize by above debugging tip,

故事的要点是，如果以前的工作代码在新数据库上返回此错误，则可能是数据库模式丢失问题，请通过上述调试提示实现，

Hope It Helps, HydTechie

希望有帮助，HydTechie

回答by Ademilso Peres

I have the same problem. This error occurs because conection pooling. When exists two or more users acess the system the connetion pooling reuse a connetion and the transation too. If the first user execute commit ou rollback the transaction is no longe usable.

我也有同样的问题。发生此错误的原因是连接池。当存在两个或更多用户访问系统时，连接池也重用一个连接和事务。如果第一个用户执行提交或回滚，则事务不再可用。

回答by Loris

In my case the problem was that one of the queries included in the transaction was raising an exception, and even though the exception was "gracefully" handled, it still managed to roll back the entire transaction.

在我的情况下，问题是事务中包含的查询之一引发了异常，即使异常被“优雅地”处理，它仍然设法回滚整个事务。

My pseudo-code was like:

我的伪代码是这样的：

var transaction = connection.BeginTransaction();
for(all the lines in a file)
{
     try{
         InsertLineInTable(); // INSERT statement might fail and throw an exception
     }
     catch {
         // notify the user about the error on line x and continue
     }
}

// Commit and Rollback will fail if one of the queries 
// in InsertLineInTable threw an exception
if(CheckTableForErrors())
{
    transaction.Commit();
}
else
{
    transaction.Rollback();
}

回答by Milan

Also check for any long running processes executed from your .NET app against the DB. For example you may be calling a stored procedure or query which does not have enough time to finish which can show in your logs as:

还要检查从您的 .NET 应用程序针对数据库执行的任何长时间运行的进程。例如，您可能正在调用没有足够时间完成的存储过程或查询，这可能会在您的日志中显示为：

• Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

  • This SqlTransaction has completed; it is no longer usable.

• 执行超时已过期。操作完成前超时时间已过或服务器未响应。

  • 这个SqlTransaction已经完成；它不再可用。

Check the command timeout settings Try to run a trace (profiler) and see what is happening on the DB side...

检查命令超时设置尝试运行跟踪（分析器）并查看 DB 端发生的情况...

回答by Amal P S

In my case , I've some codes which needs to execute after committing the transaction at the same try catch block.One of the code threw an error then try block handed over the error to it's catch block which contains the transaction rollback. It will show the similar error. For example look at the code structure below :

就我而言，我有一些代码需要在同一个 try catch 块中提交事务后执行。其中一个代码抛出一个错误，然后 try 块将错误移交给它的包含事务回滚的 catch 块。它会显示类似的错误。例如看下面的代码结构：

SqlTransaction trans = null;

try{
 trans = Con.BeginTransaction();
// your codes

  trans.Commit();
//your codes having errors

}
catch(Exception ex)
{
     trans.Rollback(); //transaction roll back
    // error message
}

finally
{ 
    // connection close
}

Hope it will someone :)

希望有人会:)

回答by Jeson Martajaya

Here is a way to detect Zombie transaction

这是一种检测僵尸交易的方法

SqlTransaction trans = connection.BeginTransaction();

//some db calls here

if (trans.Connection != null) //Detecting zombie transaction
{
  trans.Commit();
}

Decompiling the SqlTransaction class, you will see the following

反编译SqlTransaction类，会看到如下

public SqlConnection Connection
{
  get
  {
    if (this.IsZombied)
      return (SqlConnection) null;
    return this._connection;
  }
}

I notice if the connection is closed, the transOP will become zombie, thus cannot Commit. For my case, it is because I have the Commit()inside a finallyblock, while the connection was in the tryblock. This arrangement is causing the connection to be disposed and garbage collected. The solution was to put Commitinside the tryblock instead.

我注意到如果连接关闭，transOP 将变成僵尸，因此不能Commit。就我而言，这是因为我有Commit()一个finally块内部，而连接在try块中。这种安排导致连接被处理和垃圾收集。解决方案是将其放入块Commit内try。