SQLTransaction has completed error

this sqltransaction has completed; it is no longer usable
this sqltransaction has completed; it is no longer usable. umbraco
this sqltransaction has completed; it is no longer usable sitefinity
this oracletransaction has completed; it is no longer usable
this oledbtransaction has completed; it is no longer usable

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:

  1. What is zombie transaction?
  2. Zombie check on Transaction - Error
  3. SqlTransaction has completed
  4. http://forums.asp.net/t/1579684.aspx/1
  5. "This SqlTransaction has completed; it is no longer usable."... configuration error?
  6. dotnet.sys-con.com - SqlClient Connection Pooling Exposed
  7. Thread abort leaves zombie transactions and broken SqlConnection

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 in Commit stage, or if a connection to server breaks. For that reason you should wrap it in a try/catch.

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

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

A Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

Rethrow a new exception to tell user that data may not have been saved, and ask her to refresh and review

"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  Hi, Thanks for the idea. I manage to get it right, but I'm not sure if what I did will have a drawback.I simply put the two commit statements after the second ExecuteNonQuery.

Note: This error came only once.

then it is very hard to say much; it could be simply that the // Other Code etc simply took to long, and the entire thing got killed. Maybe your connection died, or an admin deliberately killed it because you were blocking.

What is the mistake in the code?

over-complicating it; it can be much simpler:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using(var transaction = connection.BeginTransaction())
    {
        try
        {
            sessionID = GetSessionIDForAssociate(connection, empID, transaction);
            //Other Code
            transaction.Commit();
         }
         catch
         {
            transaction.Rollback();
            throw;
         }
    }
}

much less code to get wrong.

Zombie check on Transaction, I purposely put some error on the second sql commandtext to force it to rollback everything, but I'm having an error when it hits the TransHQ. Execution will be retried (attempt #1) in 00:00:01 seconds. System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.

I use code below can reproduce this error, I use 1000 tasks to execute Sql, after about 300 tasks Successfully Completed, lots of exception about timeout error start to occur on ExecuteNonQuery(),

then next error This SqlTransaction has completed will occur on transaction.RollBack(); and its call stack also contains ZombieCheck().

(If single program with 1000 tasks pressure not enough, you can execute multiple compiled exe file at the same time, or even use multi computers execute to one DataBase.)

So I guess one of the reason cause this error can be something wrong in Connection, then cause the transaction error happens as well.

Task[] tasks = new Task[1000];
for (int i = 0; i < 1000; i++)
{
    int j = i;
    tasks[i] = new Task(() =>
         ExecuteSqlTransaction("YourConnectionString", j)
        );
}

foreach (Task task in tasks)
{
    task.Start();
}       

/////////////    

public void ExecuteSqlTransaction(string connectionString, int exeSqlCou)
{

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction();

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText =
                "select * from Employee";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();

            Console.WriteLine("Execute Sql to database."
                + exeSqlCou);
        }
        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);

            }
        }
    }
}

Besides I find if I commit twice sequentailly will invoke this exception as well.

       transaction.Commit();
       transaction.Commit();

Or if Connection Closed before commit also invoke this error.

       connection.Close();
       transaction.Commit();

Update:

I find it strange that I create another new table and insert 500 thousand data to it,

then use 100000 tasks with select * from newtable sql, running 5 programs at the same time, this time the Timeout Error occur, but when transaction.Rollback() it didn't invoke the SQLTransaction has completed error.

but if the Timeout Error occur, jump into the catch block, and in the catch block do transaction.Commit() again, the SQLTransaction has completed error will happen.

[Solved] This sqltransaction has completed; it is no longer usable , It was an exception: “This SqlTransaction has completed; it is no longer usable”. This type of error comes in the project when SQL connection and transaction are handled properly in the project. If connection is being closed in between the process and you are going to roll back the transaction the error occurs. Hi. I am not creating new column . I just want to Update GSTINLINK value with existing value + ID value in the beginning. New value of GSTINLINK will be -

I have experienced this error once and i was stuck and unable to know what is going wrong. Actually i was deleting a record and in the Stored procedure i was not deleting its child and specially the delete statement in Stored Procedure was inside the Transaction boundary. I removed that transaction code from stored procedure and got rid of getting this Error of "This SqlTransaction has completed; it is no longer usable."

How To Handle 'No Longer Usable SqlTransaction' Error In C#, I got "This SqlTransaction has completed; it is no longer usable. The problem is with rollback command after the transaction is committed. System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. at System.Data.SqlClient.SqlTransaction.ZombieCheck() at System.Data.SqlClient.SqlTransaction.Rollback() at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

This message is simply because that you wrote code that throws an exception after the transaction has been already committed successfully.Please try to check the code you wrote after the Commit method or you can handle it by using Try..Catch and finally Blocks :) .

This SqlTransaction has completed; it is no longer usable., Error: This SqlTransaction has completed; it is no longer usable. Hello,. I'm getting this error every now and then in a MSSQL application. I'v looked for it a bit​,  It was an exception: “ This SqlTransaction has completed; it is no longer usable ”. This type of error comes in the project when SQL connection and transaction are handled properly in the project. You are doing some transaction in the code and let say by any reason the you suffer lost connection. Then it will throw this type of exception.

Error: This SqlTransaction has completed; it is no longer usable. : SQL, Application works with Azure SQL. At some point hangfire fails with following errors: 2018-11-21 06:02:35.6082 +00:00||ERROR|Hangfire. It looks as though the outer using was closing the underlying connection thus any attempts to commit or rollback the transaction threw up the message "This SqlTransaction has completed; it is no longer usable." I removed the usings added a covering test and the problem went away.

This SqlTransaction has completed; it is no longer usable. · Issue , An error has occurred in SPM, version=X.X.X.XXXXX: This SqlTransaction has completed; it is no longer usable. at System.DataSqlTransaction. The fundamental problem with SQL Server is that, although it has a transactional DDL, when you create nested transactions, it will not stop the nested transaction until all the transactions have been evaluated. It will THEN rollback. The final wrinkle in this is that it's somewhat surprising what causes a User Error Message event to fire.

SQL Transaction is No Longer Usable, But if I try to install it with SQL Server or Custom connection string database type it ends up with error This SqlTransaction has completed; it is  minutes. When this happens, the system is painfully slow and locks up, giving SQL Server Connection errors, or the "This SqlTransaction has completed; it is no

Comments
  • What is the exception that causes your code to reach the 'catch'?
  • In this case you should use a 'using' statement for your transaction. See stackoverflow.com/questions/1127830/…
  • @Maarten to be fair, the OP does make sure it gets disposed; but I agree that not using using makes it over-complex
  • There is a critical warning coming in code analysis, if I swallow exception. That means if I don't throw the catch exception.
  • Then you have a problem with your analysis policies. I can't believe that you don't allow actual handling of exception? Do you throw all exceptions out to user?
  • Yes, I need to log the exception and throw it to the front end. I throw my custom exception, usually, in the client application.
  • In that case, rethrow a new exception to tell user that data may not have been saved, and ask her to refresh and review. Simply put, this error may happen whatever you do, only question you need to ask is do you and how do you present it to the user.
  • in your case you could save more lines, if you remove the try catch.. with using the "using" statement a transaction is automatically roll backed if an exception happenes.. see msdn.microsoft.com/en-us/library/…
  • Thanks. But I don't understand why the suggested code will resolve the problem. Is it because you are not calling Dispose() ?
  • @Lijo since you don't have a reproducible example, it is impossible to talk much about "resolve the problem". My advice here is simply: you're making it very easy to introduce subtle bugs - which is risky, unnecessary, and more work. The unnecessary code that never gets written doesn't have any bugs in it.
  • I liked the point - code that never gets written doesn't have any bugs in it :-)
  • @nWorx Just to be clear to anyone else reading this, an unhandled exception inside using(var tran = connection.BeginTransaction()) will cause tran to be rolled back when it is disposed at the end of the using block (note though that according to the MSDN docs this behaviour is specific to the DbTransaction.Dispose implementation). An exception itself does not cause tran to roll back.