SqlTransaction has completed

Question

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.

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() 

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.

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.

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.

Answer 1

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.

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.

Answer 2

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.

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.

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.