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
Comments
Anonymous
June 26, 2011
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.Anonymous
October 16, 2012
Be sure you didn't disconnect connection before trying to commit transaction. http://www.datanetzs.co.ccAnonymous
August 12, 2014
Yes the main problem is disconnection or losing connection to a database. For example, i had an issue with "using" statement, because inside this statement was my connection and outside it - doing transaction committing.Anonymous
October 06, 2014
i like it very much...code is very useful.i have seen many website.i lost one dayAnonymous
May 31, 2017
Thanks for posting this article. Its really helpful for me.