Zombie check on Transaction - Error : This SqlTransaction has completed; it is no longer usable.

You may get intermittent error from your application saying " This SqlTransaction has completed; it is no longer usable.". This may have to do with the way connection and transaction are handled in your application. One of the most frequent reasons I have seen this error showing up in various applications is, sharing SqlConnection across our application.

If the underlying connection on which transaction depends, gets closed unexpectedly and if you continue to rollback this transaction, You would see exactly same error message.

I tried something similar to illustrate scenario above. This is the code I used.

    class Program

    {

        static void Main(string[] args)

        {

            SqlConnection con = new SqlConnection("server=.;database=test;integrated security=true");

            SqlTransaction trn;

            con.Open();

            trn = con.BeginTransaction();

            try

            {

                con.Close();

                SqlCommand cmd = new SqlCommand("insert into tab values (1,1)");

                cmd.Connection = con;

                cmd.ExecuteNonQuery();

            }

            catch(Exception ex)

            {

                Console.WriteLine(ex.Message );

                trn.Rollback(); 

            }

        }

 In my example above, I intentionally closed an existing connection and tried to Execute my insert query on it. As expected, It would fail and execution would jump to catch block. In my catch block I tried to rollback my transaction without checking if connection it is associated with is still open or not. Now this unhandled exception inside catch would raise this error as below.

System.InvalidOperationException was unhandled

Message="This SqlTransaction has completed; it is no longer usable."

Source="System.Data"

StackTrace:

       at System.Data.SqlClient.SqlTransaction.ZombieCheck()

       at System.Data.SqlClient.SqlTransaction.Rollback()

       at SQLTransaction.Program.Main(String[] args) in C:\Users\runeetv\Documents\Visual Studio

      2005\Projects\SQLTransaction\SQLTransaction\Program.cs:line 27

       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)

       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback

      callback, Object state)

       at System.Threading.ThreadHelper.ThreadStart()

I also ran into MSDN document where the example written by content team has specially take care of this kind of scenario (They have try-catch inside the catch block) https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

catch(Exception ex)

        {

            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());

            Console.WriteLine(" Message: {0}", ex.Message);

            // Attempt to roll back the transaction.

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

            }

        }

Certainly a better way to roll back transaction on a shared connection.

Author : Runeet(MSFT), SQL Developer Engineer, Microsoft 

Reviewed by : Naresh(MSFT), SQL Developer Technical Lead, Microsoft