ADO.NET 2.0 and System.Transactions, downlevel functions and nested scopes

As with most of my blog posts this started as a customer question, the core of the problem was that he was not sure how he was expected to use TransactionScopes in methods that call other methods or "downlevel" functions as he named them.

There are two important concepts to keep in mind.

1) downlevel functions should not know or care about what happens on the functions that call them. Don't  pass TransactionScope as properties, don't assume that there is an existing transaction, etc. 

2)You can nest TransactionScopes (with the default TransactionScopeOption, "Required") or isolate downlevel functions from outer TransactionScopes (with the optional Suppress and RequiresNew TransactionScopeOption(s) )

Here is some pseudo code for a small stress app I wrote that shows some of this off: 

InsertMoneyIntoAccount() //doesn’t use transactions

RemoveMoneyFromAccount() //doesn’t  use transactions

TransferMoney() //creates TransactionScope then calls RemoveMoney and InsertMoney

TransferMoneyWithExtraCharges //creates TransactionScope, calls TransferMoney and RemoveMoneyFromAccount

// the TransactionScope in TransferMoney will be nested inside the outer Scope.

A more advanced (and to my way of thinking less recommended) concept is nesting Scopes where you do not want the behavior in the downlevel functions to affect outer scopes, you can do this by using the TransactionScopeOption.RequiresNew and Suppress enum values.

TransferVerifyAndTransfer() //creates a TransactionScope, calls Transfer, uses retry logic to call VerifyTransfer multiple times and once the transfer is verified calls Transfer again. The main problem here is that we don’t want a fatal exception in VerifyTransfer to roll back the transaction since we are building in logic to retry on exception.

VerifyTransfer() //creates a TransactionScope with TransactionScopeOption.Suppress, this guarantees that it will not affect the scopes in uplevel functions.

//It is important to realize that some exceptions even when handled can affect the transaction. In the example below if we don’t use the Suppress option for B’s TransactionScope then the ExecuteNonQuery (level 20 exception) will cause A’s outer TransactionScope to be rolled back.

 

            public void A(){  

                using (TransactionScope transactionscope1 = new TransactionScope()){

                        this.B( );

                        transactionscope1.Complete()

                }

            }

            public void B(){  

             //always create a TransactionScope with the “using” language construct.

             using (TransactionScope transactionscope1 = new TransactionScope(TransactionScopeOption.Suppress)){

                        //always create a connection with the “using” language construct.

                        using (SqlConnection sqlconnection1 = new SqlConnection(connectionstring)){

                                    sqlconnection1.Open();

                                    SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

                                    sqlcommand1.CommandText = "raiserror ('my error', 20, 20) with log";

                                    try {

                                                sqlcommand1.ExecuteNonQuery();

                                    }

                                   catch(Exception e){

                                                Console.WriteLine(e.Message);

                                    }

                                    }

                                    transactionscope1.Complete();

                        } //Here sqlconnection1 is _guaranteed_ to be disposed. I think it is a good idea to do this before the scope is disposed.

                 }

            }

Rambling out: This post is provided "AS IS" and confers no rights.

Comments

• Anonymous
  February 12, 2005
  The comment has been removed
• Anonymous
  February 13, 2005
  Bill,
  Not quite, if B throws an exception then the transactionscope1.Complete() call will never be made and you will roll back the transaction scope.
  
  As you can see in the example B needs to handle the exception, the difference is that the level 20 error would have automatically rolled back the transaction if you werent inside of a Suppressed scope, does that makes sense? You can play with this by having A insert data into a database and changing the TransactionScopeOption of B.
  
• Anonymous
  February 13, 2005
  Angel,
  
  If I'm writing a class, what do I have to do to make it automatically enlist in transactions? Also is there a list of classes that will work with System.Transaction?
  
  _ SM
• Anonymous
  February 14, 2005
  The comment has been removed
• Anonymous
  February 14, 2005
  Thanks Angel. :)