Zombie check on Transaction - Error : This SqlTransaction has completed; it is no longer usable.
Published Jan 15 2019 01:12 PM 18.6K Views
First published on MSDN on Aug 24, 2010

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

Version history
Last update:
‎Jan 15 2019 01:12 PM
Updated by: