SqlTransaction 已完成 [英] SqlTransaction has completed

问题描述

我有一个应用程序，它可能会对 SQL Server 2005 数据库执行数千次插入操作.如果插入因任何原因(外键约束、字段长度等)失败，应用程序将记录插入错误并继续.

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.

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

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.

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

为了追查原因，我在每个事务操作中都放置了跟踪语句，以便在调用 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.

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

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.

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

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.

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

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.

推荐答案

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

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.

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

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.

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

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.

这篇关于SqlTransaction 已完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！