TransactionScope has aborted transaction before disposal

9
votes

When using TransactionScope it apperars that if internally executed code rolled back the transaction than the parent transaction will rollback as well. Which is good for me. But when disposing that scope it throws an exception meaning that transaction was rolled back already and aborted. So what is the right way to handle that and properly dispose the scope?

    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
                    using (var conn = GetConnection())
                    {
                            string query = 
              @"some query that may contain transaction itself 
              or some SP whith transaction included"

                            using (var command = new SqlCommand(query, conn))
                                command.ExecuteNonQuery();
                        }
                    }
                    scope.Complete();
    } // Exception here
3
c#sqltransactions

3 Answers

9
votes

scope.Dispose() may throw TransactionAborted exception even if scope.Complete() has been called. For example some stored procedures a smart enough to handle exceptions and abort transaction inside T-SQL script using T-SQL TRY/CATCH construct w/o throwing exception to the caller. So I would consider the safest approach I would suggest is as follows: 

try
{
    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        try
        {
            using (var conn = GetConnection())
            {
                string query = 
                @"some query that may contain transaction itself 
                or some SP whith transaction included"

                using (var command = new SqlCommand(query, conn))
                command.ExecuteNonQuery();
            }
        }
        catch (SqlException ex)
        {
            // log SQL Exception, if any
            throw;  // re-throw exception
        }

        scope.Complete();
    }
}
catch (TransactionAbortedException ex)
{
    // we can get here even if scope.Complete() was called.
    // log TransactionAborted exception if necessary
}

And don't worry about disposing TransactionScope. scope.Dispose performs whatever necessary to clean it up before throwing TransactionAborted exception.

2
votes

I do not use stored procs or SQL-specific try/catch, so my situation was slightly different but I got the exact same transaction aborted exception mentioned in the post. I found that if I have a SELECT somewhere inside the primary TransactionScope, that will cause the Transaction to commit, although I am not sure why. I had a case where in order to create an object in the database it first checked to make sure the object didn't already exist with a SELECT, and then the abort exception occurred when Dispose was called. I looked at the Inner Exception and it said the Transaction was trying to commit without a begin. I finally tried wrapping my SELECT in a Suppressed TransactionScope, and then it worked. So:

using(TransactionScope tx = new TransactionScope()) 
{ 
  //UPDATE command for logging that I want rolled back if CREATE fails

  using(TransactionScope tx2 = new TransactionScope(TransactionScopeOption.Suppress)) 
  { 
    // SELECT command
  } 

  //If not exists logic
  //CREATE command
} //Used to error here, but not with the SELECT Suppressed

I hope this helps anyone else who might have gotten this exception without using stored procs.

0
votes

If an exception throws from your inner query the scope.Complete() line 'll not executed. Please refer the link below.. And I've made some changes to your query also. I hope it should work for you. Transaction Scope

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    using (var conn = GetConnection())
    {
        string query = 
        @"some query that may contain transaction itself 
        or some SP whith transaction included"

        using (var command = new SqlCommand(query, conn))
        command.ExecuteNonQuery();
    }
    scope.Complete();
}