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.

Solution 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.

Solution 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.

Solution 3

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

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

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, DbConnection implements IDisposable, so you need to ensure you clean up appropriately - with a using statement if you can, or by calling Dispose() directly if you can't. 'DbCommand' is similar, as it also implements IDisposable.

Solution 4

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".

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

Solution 5

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

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

View more solutions

Comments

[LukeH]

Use transactions "for the performance gain"? How does using transactions improve performance?

[eglasius]

Agreed with luke, why do you see or think you have a performance gain by doing so?

[Sam Saffron]

having a transaction around multiple inserts will, in general, increase performance, test it yourself and see. by having a transaction you reduce the number of locks you need to acquire before inserts

[Sam Saffron]

Are you committing the same transaction twice in a row?

[Mark Sowul]

You also reduce the number of transactions since otherwise each statement would be its own implicit transaction.

[aef123]

Normally I'd agree with you about the app dealing with business rules. However, in this case the app is a data conversion tool. The end user sets up all the business rules. It is the users responsibility to build their conversion in a way that the DB accepts. This is why I need to log and continue

[SnapJag]

Agreed as well. My answer is to support why the problem is occuring. The application hasn't accounted for a failing error IMO. Execute the stored proc as an statement in SQL Enterprise Manager (bypassing the app) and see what the results are, successful or not.

[aef123]

The final result was that SQL Server automatically rolls back transactions on what it considers serious errors. DateTime conversion error is considered one of these serious errors. Currently there is no way to stop SQL Server from terminating your transaction for this kind of error.

[Marius]

Both links are dead

[FrostyOnion]

Thanks for this - been trying to debug for a couple of hours and your post is the most relevant to my problem. Have a date conversion error on a particular insert but it doesn't seem to kill the transaction every time... just sometimes from what I can gather..