Paul Kyrejto Paul Kyrejto - 1 month ago 13
SQL Question

TransactionScope has aborted transaction before disposal

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

Answer

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.