.net SqlTransaction 已完成

SqlTransaction has completed

提问by aef123

I have an application which potentially does thousands of inserts to a SQL Server 2005 database. If an insert fails for any reason (foreign key constraint, field length, etc.) the application is designed to log the insert error and continue.

我有一个应用程序，它可能会对 SQL Server 2005 数据库进行数千次插入。如果插入因任何原因（外键约束、字段长度等）失败，则应用程序旨在记录插入错误并继续。

Each insert is independent of the others so transactions aren't needed for database integrity. However, we do wish to use them for the performance gain. When I use the transactions we'll get the following error on about 1 out of every 100 commits.

每个插入都独立于其他插入，因此数据库完整性不需要事务。但是，我们确实希望使用它们来提高性能。当我使用事务时，每 100 次提交中约有 1 次会出现以下错误。

This SqlTransaction has completed; it is no longer usable.
   at System.Data.SqlClient.SqlTransaction.ZombieCheck()
   at System.Data.SqlClient.SqlTransaction.Commit()

To try to track down the cause I put trace statements at every transaction operation so I could ensure the transaction wasn't being closed before calling commit. I've confirmed that my app wan't closing the transaction. I then ran the app again using the exact same input data and it succeeds.

为了试图找出原因，我在每个事务操作中都放置了跟踪语句，这样我就可以确保在调用 commit 之前事务没有被关闭。我已经确认我的应用程序不会关闭交易。然后我使用完全相同的输入数据再次运行该应用程序，它成功了。

If I turn the logging off it fails again. Turn it back on and it succeeds. This on/off toggle is done via the app.config without the need to recompile.

如果我关闭日志记录，它会再次失败。重新打开它，它成功了。这种开/关切换是通过 app.config 完成的，无需重新编译。

Obviously the act of logging changes the timing and causes it to work. This would indicate a threading issue. However, my app isn't multi-threaded.

显然，日志记录的行为会改变时间并使其工作。这将表明线程问题。但是，我的应用程序不是多线程的。

I've seen one MS KB entry indicating a bug with .Net 2.0 framework could cause similar issues (http://support.microsoft.com/kb/912732). However, the fix they provided doesn't solve this issue.

我已经看到一个 MS KB 条目表明 .Net 2.0 框架的错误可能会导致类似的问题 ( http://support.microsoft.com/kb/912732)。但是，他们提供的修复程序并没有解决这个问题。

回答by aef123

Thanks for all the feedback. I've been working with someone from MSFT on the MSDN forums to figure out what's going on. It turns out the issue is due to one of the inserts failing due to a date time conversion problem.

感谢所有反馈。我一直在与 MSDN 论坛上来自 MSFT 的人合作，以弄清楚发生了什么。事实证明，问题是由于日期时间转换问题导致其中一个插入失败。

The major problem is the fact that this error shows up if it's a date conversion error. However, if it's another error such as a field being too long it doesn't cause this issue. In both cases I would expect the transaction to still exist so I can call Rollback on it.

主要问题是，如果是日期转换错误，则会显示此错误。但是，如果是另一个错误，例如字段太长，则不会导致此问题。在这两种情况下，我都希望事务仍然存在，因此我可以对其调用回滚。

I have a full sample program to replicate this issue. If anyone wishes to see it or the exchange with MSFT you can find the thread on MSFT's newsgroups in microsoft.public.dotnet.framework.adonet under the SqlTransaction.ZombieCheck error thread.

我有一个完整的示例程序来复制这个问题。如果有人希望看到它或与 MSFT 的交流，您可以在 SqlTransaction.ZombieCheck 错误线程下的 microsoft.public.dotnet.framework.adonet 中的 MSFT 新闻组上找到该线程。

回答by Joe

Difficult to help without seeing code. I assume from your description you are using a transaction to commit after every N inserts, which will improve performance vs committing each insert provided N is not too big.

不看代码很难帮助。我假设根据您的描述，您在每 N 次插入后使用一个事务进行提交，这将提高性能，而不是在 N 不是太大的情况下提交每个插入。

But the downside is: if an insert fails, any other inserts within the current batch of N will be rolled back when you rollback the transaction.

但缺点是：如果插入失败，当您回滚事务时，当前批次 N 中的任何其他插入都将被回滚。

In general you should dispose a transaction before closing the connection (which will rollback the transaction if it hasn't been committed). The usual pattern looks something like the following:

通常，您应该在关闭连接之前处理一个事务（如果它尚未提交，它将回滚该事务）。通常的模式如下所示：

using(SqlConnection connection = ...)
{
    connection.Open();
    using(SqlTransaction transaction = connection.BeginTransaction())
    {
        ... do stuff ...
        transaction.Commit(); // commit if all is successful
    } // transaction.Dispose will be called here and will rollback if not committed
} // connection.Dispose called here

Please post code if you need more help.

如果您需要更多帮助，请发布代码。

回答by Bevan

Keep in mind that your application isn't the only participant in the transaction - the SQL Server is involved as well.

请记住，您的应用程序并不是事务的唯一参与者——SQL Server 也参与其中。

The error you quote:

你引用的错误：

This SqlTransaction has completed; it is no longer usable. at System.Data.SqlClient.SqlTransaction.ZombieCheck() at System.Data.SqlClient.SqlTransaction.Commit()

这个SqlTransaction已经完成；它不再可用。在 System.Data.SqlClient.SqlTransaction.ZombieCheck() 在 System.Data.SqlClient.SqlTransaction.Commit()

doesn't indicate the transaction has comitted, only that it is complete.

并不表示事务已提交，仅表示它已完成。

My first suggestion is that your server has killed off the transaction because it either took too long (ellapsed wall time) or got too large (too many changes or too many locks).

我的第一个建议是您的服务器已经终止了事务，因为它要么花费了太长时间（挂墙时间），要么变得太大（更改太多或锁太多）。

My second suggestion is to check that you're cleaning up connections and transactions appropriately. It's possible that you're running into problems because you are occasionally exhausting a pool of some resource before things get automatically recycled.

我的第二个建议是检查您是否正确清理了连接和事务。您可能会遇到问题，因为您偶尔会在自动回收之前耗尽某些资源池。

For example, DbConnectionimplements IDisposable, so you need to ensure you clean up appropriately - with a usingstatement if you can, or by calling Dispose()directly if you can't. 'DbCommand'is similar, as it also implements IDisposable.

例如，DbConnection实现IDisposable，因此您需要确保适当清理 -如果可以，请使用 using语句，或者如果不能，则Dispose()直接调用。'DbCommand'类似，因为它也实现了IDisposable.

回答by Alex Yakunin

This exception is thrown because actual DB transaction is already rolled back, so a .NET object representing it on the client side is already a "zombie".

抛出此异常是因为实际的 DB 事务已经回滚，因此在客户端表示它的 .NET 对象已经是“僵尸”。

More detailed explanation is here. This postexplains how to write a correct transaction rollback code for such scenarios.

更详细的解释是here。这篇文章解释了如何为此类场景编写正确的事务回滚代码。

回答by Joey Morgan

My problem was similar, and it turned out that I was doing it to myself. I was running a bunch of scripts in a VS2008 project to create stored procedures. In one of the procs, I used a transaction. The script was executing the creation of the proc inside a transaction, rather than including the transaction code as part of the procedure. So when it got to the end without an error, it committed the transaction for the script. The .Net code was also using and then committing a transaction, and the zombie effect came when it tried to close the transaction that had already been closed inside the SQL script. I removed the transaction from the SQL script, relying on the transaction opened and checked in the .Net code, and this solved the problem.

我的问题是类似的，结果证明是我自己做的。我在 VS2008 项目中运行了一堆脚本来创建存储过程。在其中一个过程中，我使用了一个事务。该脚本在事务内执行 proc 的创建，而不是将事务代码作为过程的一部分。所以当它没有错误地结束时，它为脚本提交了事务。.Net 代码也在使用然后提交一个事务，当它试图关闭在 SQL 脚本中已经关闭的事务时，僵尸效应就出现了。我从 SQL 脚本中删除了事务，依靠在 .Net 代码中打开和检查的事务，这解决了问题。

回答by Charles

According to this post: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/24/zombie-check-on-transaction-error-this-sqltransaction-has-completed-it-is-no-longer-usable.aspx

根据这篇文章：http: //blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/24/zombie-check-on-transaction-error-this-sqltransaction-has-completed-it-is-no -更长的可用.aspx

A temporary solution could be to try catching the rollback or commit operation. So this code will be enough for stopping the bug to be throwing:

临时解决方案可能是尝试捕获回滚或提交操作。所以这段代码足以阻止抛出的错误：

    public static void TryRollback(this System.Data.IDbTransaction t)
    {
        try
        {
            t.Rollback();
        }
        catch (Exception ex)
        {
            // log error in my case
        }
    }

    public static void TryCommit(this System.Data.IDbTransaction t)
    {
        try
        {
            t.Commit();
        }
        catch (Exception ex)
        {
            // log error in my case
        }
    }

Check this example from msdn website: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

从 msdn 网站查看此示例：http: //msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

回答by SnapJag

Well, first IMO, you shouldn't expect your app to deal with "hard" errors like these. Your application should understand what these business rules are and account for them. DON'T force your database to be the business rule, or the constraint rule cop. It should only be a data rule cop, and at that, be graceful about telling the interface with RETURNs and other methods. Schema stuff shouldn't be force up that far.

好吧，首先 IMO，您不应该期望您的应用程序处理这些“硬”错误。您的应用程序应该了解这些业务规则是什么并对其进行说明。不要强迫您的数据库成为业务规则或约束规则警察。它应该只是一个数据规则警察，并且在使用 RETURN 和其他方法告诉接口时要优雅。架构的东西不应该被强迫到那么远。

On to answer your question, I suspect, without seeing any of your code, that you are trying to do a commit when an error has occured, and you don't know it yet. This is the reasoning behind my first statement ... trying to trap for the error the database gives, without having your application understand and participate in these rules, you're giving yourself a headache.

在回答您的问题时，我怀疑在没有看到您的任何代码的情况下，您正在尝试在发生错误时进行提交，而您还不知道。这就是我的第一条语句背后的原因......试图捕获数据库给出的错误，而没有让您的应用程序理解并参与这些规则，您会让自己头疼。

Try this. Insert rows that won'tfail with a database constraint, or other errors and process the inserts with a commit. See what happens. Then shove in some records that willfail, process a commit and see if you get your lovely error. Third, run the errors again, do a forced rollback, and see if you succeed.

尝试这个。插入不会因数据库约束或其他错误而失败的行，并通过提交处理插入。走着瞧吧。然后推入一些会失败的记录，处理一次提交，看看你是否得到了可爱的错误。第三，再次运行错误，强制回滚，看看是否成功。

Just some ideas. Again, as a summary, I think it has to do with not trapping certain errors from the database in a graceful way for things that are "hard" errors and expecting the front end to deal with them. Again, my expert opinion, NOPE, don't do it. It's a mess. Your app needs to overlap in knowledge about the rules on the back. Those things are in place just to make sure this doesn't happen during testing, and the occasional bug that surfaces like this, to then put in the front it to handle the forgotten lookup to a foreign key table set.

只是一些想法。同样，作为总结，我认为这与不以优雅的方式从数据库中捕获某些“硬”错误并期望前端处理它们有关。再次，我的专家意见，NOPE，不要这样做。一团糟。您的应用程序需要在有关背面规则的知识方面重叠。这些东西只是为了确保在测试期间不会发生这种情况，以及偶尔会出现这样的错误，然后将其放在前面以处理对外键表集的遗忘查找。

Hope it helps.

希望能帮助到你。

回答by SnapJag

You have proved your data is OK beyond all reasonable doubt.
FWIW, I would prefer to move the insert into a SPROC and don't use the transaction at all.
If you need the UI to be responsive, use a background worker to do the database grunt.
To me, a transaction is for interrelated activities, not a time saving device. The insertion cost has to be paid somewhere along the line.

您已经证明您的数据没有任何合理怀疑。
FWIW，我更愿意将插入移动到 SPROC 中并且根本不使用事务。
如果您需要 UI 响应，请使用后台工作人员来执行数据库操作。
对我来说，事务是用于相互关联的活动，而不是节省时间的设备。插入成本必须在生产线上的某个地方支付。

I recently used ANTS profiler on a database application and was amazed to see intermittant SQlClient exceptions showing in solidly performing code. The errors are deep in the framework when opening a connection. They never make it to the surface and aren't detectable by the client code. So... The point?
It is not all rock solid out there, move the heavy work off the U.I. and accept the cost. HTH Bob

我最近在一个数据库应用程序上使用了 ANTS 分析器，并惊讶地看到在稳定执行的代码中显示间歇性 SQlClient 异常。打开连接时，错误在框架中很深。它们永远不会浮出水面，也无法被客户端代码检测到。所以……重点？
并不是所有的事情都是坚如磐石的，将繁重的工作从 UI 上移开并接受成本。HTH鲍勃

回答by Frank Myat Thu

I also faced the same problem.

我也面临同样的问题。

This SqlTransaction has completed; it is no longer usable

After doing some research, I found that my datatabase log file size is 40GB round.
It is large data volume which cause my sql transaction un-commited.

经过一番研究，我发现我的数据库日志文件大小为 40GB。
数据量很大，导致我的 sql 事务未提交。

So, My solution is to shrinklog file size by seeing this reference link.

因此，我的解决方案是shrink通过查看此参考链接来记录文件大小。

CREATE PROCEDURE sp_ShrinkLog_ByDataBaseName(
@DataBaseName VARCHAR(200)
)
AS
BEGIN

DECLARE @DBName VARCHAR(200)
DECLARE @LogFileName VARCHAR(200)
SET @DBName = @DataBaseName

SELECT @LogFileName = [Logical File Name]
FROM
(
SELECT
    DB_NAME(database_id)  AS "Database Name", 
type_desc             AS "File Type", 
name                  AS "Logical File Name", 
physical_name         AS "Physical File", 
state_desc            AS "State"
FROM
    SYS.MASTER_FILES
WHERE
    database_id = DB_ID(@DBName)
    and type_desc = 'LOG'
) AS SystemInfo
SELECT LogFileName = @LogFileName

EXECUTE(
'USE ' + @DBName + ';

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ' + @DBName + '
SET RECOVERY SIMPLE;

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (' + @LogFileName + ', 1);

-- Reset the database recovery model.
ALTER DATABASE ' + @DBName + '
SET RECOVERY FULL;

')
END

Then execute it EXEC sp_ShrinkLog_ByDataBaseName 'Yor_DB_NAME'.

然后执行它EXEC sp_ShrinkLog_ByDataBaseName 'Yor_DB_NAME'。

If this is not working for you, here is another solutionwhich I think it will work.

如果这对您不起作用，这里有另一种解决方案，我认为它会起作用。