Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

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

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

  • Comments 2

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) http://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

Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post
  • My problem was similar, and it turned out that I was doing it to myself. I was running a bunch of scripts in a VS2008 project to create stored procedures. In one of the procs, I used a transaction. The script was executing the creation of the proc inside a transaction, rather than including the transaction code as part of the procedure. So when it got to the end without an error, it committed the transaction for the script. The .Net code was also using and then committing a transaction, and the zombie effect came when it tried to close the transaction that had already been closed inside the SQL script. I removed the transaction from the SQL script, relying on the transaction opened and checked in the .Net code, and this solved the problem.

  • Be sure you didn't disconnect connection before trying to commit transaction.

    http://www.datanetzs.co.cc

Page 1 of 1 (2 items)