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