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:
- What is zombie transaction?
- Zombie check on Transaction – Error
- SqlTransaction has completed
- http://forums.asp.net/t/1579684.aspx/1
- "This SqlTransaction has completed; it is no longer usable."… configuration error?
- dotnet.sys-con.com – SqlClient Connection Pooling Exposed
- Thread abort leaves zombie transactions and broken SqlConnection
Best Answer
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 inCommit
stage, or if a connection to server breaks. For that reason you should wrap it in atry
/catch
.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
Rethrow a new exception to tell user that data may not have been saved, and ask her to refresh and review