SQLTransaction has completed error

Question

I got following error once in my application.

This SQLTransaction has completed; it is no longer usable

Stack Trace is attached below – It says about Zombie Check and Rollback.

What is the mistake in the code?

Note: This error came only once.

UPDATE

From MSDN - SqlTransaction.Rollback Method

A Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

From Zombie check on Transaction - Error

One of the most frequent reasons I have seen this error showing up in various applications is, sharing SqlConnection across our application.

CODE

public int SaveUserLogOnInfo(int empID)
{
        int? sessionID = null;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction();
                sessionID = GetSessionIDForAssociate(connection, empID, transaction);

                    //Other Code

                //Commit
                transaction.Commit();
            }
            catch
            {
                //Rollback
                if (transaction != null)
                {
                    transaction.Rollback();
                    transaction.Dispose();
                    transaction = null;
                }

                //Throw exception
                throw;
            }
            finally
            {
                if (transaction != null)
                {
                    transaction.Dispose();
                }
            }
        }

        return Convert.ToInt32(sessionID,CultureInfo.InvariantCulture);

   }

Stack Trace

---

REFERENCE:

1. What is zombie transaction?
2. Zombie check on Transaction - Error
3. SqlTransaction has completed
4. http://forums.asp.net/t/1579684.aspx/1
5. "This SqlTransaction has completed; it is no longer usable."... configuration error?
6. dotnet.sys-con.com - SqlClient Connection Pooling Exposed
7. Thread abort leaves zombie transactions and broken SqlConnection

---

Answer 1

You should leave some of the work to compiler, to wrap that in a try/catch/finally for you.

Also, you should expect that Rollback can occasionally throw an exception, if a problem occurs in Commit stage, or if a connection to server breaks. For that reason you should wrap it in a try/catch.

try
{
    transaction.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);
}

This is copied exactly from MSDN documentation page for Rollback method.

I see that you're worried that you have a zombie transaction. In case you pasted, it doesn't sound like you have a problem. You're transaction has been completed, and you should no longer have anything to do with it. Remove references to it if you hold them, and forget about it.

---

From MSDN - SqlTransaction.Rollback Method

A Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

Rethrow a new exception to tell user that data may not have been saved, and ask her to refresh and review

Answer 2

Note: This error came only once.

then it is very hard to say much; it could be simply that the // Other Code etc simply took to long, and the entire thing got killed. Maybe your connection died, or an admin deliberately killed it because you were blocking.

What is the mistake in the code?

over-complicating it; it can be much simpler:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using(var transaction = connection.BeginTransaction())
    {
        try
        {
            sessionID = GetSessionIDForAssociate(connection, empID, transaction);
            //Other Code
            transaction.Commit();
         }
         catch
         {
            transaction.Rollback();
            throw;
         }
    }
}

much less code to get wrong.