views:

913

answers:

1

I'm trying to make a quick dummy app so I can learn the ins and outs of System.Transactions. This app interacts with 2 different SQLExpress DBs. If I pull up my transaction statistics in component services, I can see a transaction start up in the outerScope when the second connection is opened. If failOuter is true the transaction aborts, but it doesn't throw any exceptions. When failInner is true a TransactionAbortedException is thrown.

From MSDN:

When your application completes all the work it wants to perform in a transaction, you should call the Complete method only once to inform the transaction manager that it is acceptable to commit the transaction. It is very good practice to put the call to Complete as the last statement in the using block.

Failing to call this method aborts the transaction, because the transaction manager interprets this as a system failure, or equivalent to an exception thrown within the scope of transaction.

A TransactionAbortedException is thrown if the scope creates the transaction, and the transaction is aborted.

Based on that I would expect my outerScope to throw a TransactionAbortedException since my Transaction Statistics are showing an aborted transaction everytime I run my app with failOuter set to true. My method returns a true since no exceptions are thrown even if the transaction aborts. Unless I abort the inner transaction, then it behaves as I would expect. Any clarification would be most appreciated.

public bool CreateNestedTransaction(bool failOuter, bool failInner)
    {
        try
        {
            using (TransactionScope outerScope = new TransactionScope())
            {

                /* Perform transactional work here */
                using (SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=test1"))
                {
                    SqlCommand myCommand = new SqlCommand();
                    myConnection.Open();
                    myCommand.Connection = myConnection;

                    myCommand.CommandText = "update test set Value = ((select Value from test where Id = (select max(Id) from test))+1) where Id = (select max(Id) from test)";
                    myCommand.ExecuteNonQuery();
                }


                using (SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=test1"))
                {
                    SqlCommand myCommand = new SqlCommand();
                    myConnection.Open();
                    myCommand.Connection = myConnection;

                    myCommand.CommandText = "update test set Value = Value";
                    myCommand.ExecuteNonQuery();
                }

                using (TransactionScope innerScope = new TransactionScope())
                {
                    using (SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=test2"))
                    {
                        SqlCommand myCommand = new SqlCommand();
                        myConnection.Open();
                        myCommand.Connection = myConnection;

                        myCommand.CommandText = "update test set Value = ((select Value from test where Id = (select max(Id) from test))+1) where Id = (select max(Id) from test)";
                        myCommand.ExecuteNonQuery();
                    }
                    if (failInner == false) { innerScope.Complete(); }
                }

                if (failOuter == false) { outerScope.Complete(); }
            }
        }

        catch (TransactionAbortedException)
        {
            return false;
        }

        return true;
    }
+6  A: 

Normally you don't get an exception thrown by failing to call TransactionScope.Complete() before the TransactionScope goes out of scope and gets disposed. The transaction will just quietly roll back.

The exception in your case is happening because you are trying to call Complete on the outer TransactionScope and it can't complete properly because the inner TransactionScope already failed - hence this throws an exception.

Does that make sense?

If you want to carry out some operation if your outer transaction aborted you might try something like this:

// Inside each using TransactionScope(), hhok up the current transaction completed event
Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler(Current_TransactionCompleted);

// handle the event somewhere else
void Current_TransactionCompleted(object sender, TransactionEventArgs e)
{
  //  check the status of the transaction
  if(e.Transaction.TransactionInformation.Status == TransactionStatus.Aborted)
    // do something here
}

Although I think a cleaner pattern for general usage would be to always call Complete() inside your TransactionScope and handle any resulting exception if you want to do something specific on transaction failure.

Steve Willcock
Yes, that makes sense. I think the last line from MSDN was throwing me off. I was watching my transactions abort and expecting an exception. Thanks for the explanation! Is there a way to tell if my outer exception aborted? I want to return false in that case.
Justin Holbrook
Hmm interesting question - not sure! I'll check it out when I get home and let you know :)
Steve Willcock
Yes, it looks like there is a way to do it, although it's not the pattern I would use in general - see edited answer for details
Steve Willcock