ZombieCheck Exception - This SqlTransaction has completed; it is no longer usable -- during simple commit

Question

I have the following code which performs a commit of a single row to a database table (SQL 2008 / .NET 4)

using (var db = new MyDbDataContext(_dbConnectionString))
{
    Action action = new Action();
    db.Actions.InsertOnSubmit(dbAction);
    db.SubmitChanges();
}

Normally everything is fine, but once in a while I get the following exception:

System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.Rollback()
at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

There are a number of similar questions on SO but I after reading them I cannot work out the cause.

Could this be simply due to a SQL timeout (the exception occurs close to 25s after the call is made)? Or should I expect a SQL timeout exception in that case?

Does anyone know what else may cause this?

Answer 1

The DataContext.SubmitChanges method has the following code lines in it's body:

// ...
try
{
    if (this.provider.Connection.State == ConnectionState.Open)
    {
        this.provider.ClearConnection();
    }
    if (this.provider.Connection.State == ConnectionState.Closed)
    {
        this.provider.Connection.Open();
        flag = true;
    }
    dbTransaction = this.provider.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
    this.provider.Transaction = dbTransaction;
    new ChangeProcessor(this.services, this).SubmitChanges(failureMode);
    this.AcceptChanges();
    this.provider.ClearConnection();
    dbTransaction.Commit();
}
catch
{
    if (dbTransaction != null)
    {
        dbTransaction.Rollback();
    }
    throw;
}
// ...

When the connection times out, the catch block is executed and the dbTransaction.Rollback(); line will throw a InvalidOperationException.

If you had control over the code, you could catch the exception like this:

catch
{
    // Attempt to roll back the transaction. 
    try
    {
        if (dbTransaction != null)
        {
            dbTransaction.Rollback();
        }
    }
    catch (Exception ex2)
    {
        // This catch block will handle any errors that may have occurred 
        // on the server that would cause the rollback to fail, such as 
        // a closed connection.
        Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
        Console.WriteLine("  Message: {0}", ex2.Message);
    }
    throw;
}

Answer 2

YES! I had the same issue. The scary answer is that SQLServer sometimes rolls back a transaction on the server side when it encounters an error, and does not pass the error back to the client. YIKES!

Look on the Google Group microsoft.public.dotnet.framework.adonet for "SqlTransaction.ZombieCheck error" Colberd Zhou [MSFT] explains it very well.

and see aef123's comment on this SO post

Answer 3

May I suggest that connection closes earlier that transaction commits. Then the transaction is rolled back. Check this article on MSDN Blog.