TransactionScope: Avoiding Distributed Transactions

Question

I have a parent object (part of a DAL) that contains, amongst other things, a collection (List<t>) of child objects.

When I'm saving the object back to the DB, I enter/update the parent, and then loop through each child. For maintainability, I've put all the code for the child into a separate private method.

I was going to use standard ADO Transactions, but on my travels, I stumbled across the TransactionScope object, which I believe will enable me to wrap all DB interaction in the parent method (along with all interaction in the child method) in one transaction.

So far so good..?

So the next question is how to create and use connections within this TransactionScope. I have heard that using multiple connections, even if they are to the same DB can force TransactionScope into thinking that it is a distributed transaction (involving some expensive DTC work).

Is the case? Or is it, as I seem to be reading elsewhere, a case that using the same connection string (which will lend itself to connection pooling) will be fine?

More practically speaking, do I...

1. Create separate connections in the parent & child (albeit with the same connection string)
2. Create a connection in the parent an pass it through as a parameter (seems clumsy to me)
3. Do something else...?

UPDATE:

While it appears I would be OK using my usual .NET3.5+ and SQL Server 2008+, another part of this project will be using Oracle (10g) so I might as well practice a technique that can be used consistently across projects.

So I'll simply pass the connection through to the child methods.

---

Option 1 Code Sample:

using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.Connection.Open();
                        cmd.CommandType = CommandType.StoredProcedure;

                        try
                        {
                            //create & add parameters to command

                            //save parent object to DB
                            cmd.ExecuteNonQuery();

                            if ((int)cmd.Parameters["@Result"].Value != 0)
                            {
                                //not ok
                                //rollback transaction
                                ts.Dispose();
                                return false;
                            }
                            else //enquiry saved OK
                            {
                                if (update)
                                {
                                    enquiryID = (int)cmd.Parameters["@EnquiryID"].Value;
                                }

                                //Save Vehicles (child objects)
                                if (SaveVehiclesToEPE())
                                {
                                    ts.Complete();
                                    return true;
                                }
                                else
                                {
                                    ts.Dispose();
                                    return false;
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            //log error
                            ts.Dispose();
                            throw;
                        }
                    }
                }
            }

Answer 1

Many database ADO providers (such as Oracle ODP.NET) do indeed begin distributed transactions when you use TransactionScope to transact across multiple connections - even when they share the same connection string.

Some providers, (like SQL2008 in .NET 3.5+) recognizes when a new connection is created in a transaction scope that refers to the same connection string, and will not result in DTC work. But any variance in the connection string (such as tuning parameters) may preclude this from occuring - and the behavior will revert to using a distributed transaction.

Unfortunately, the only reliable means of ensuring your transactions will work together without creating a distributed transaction is to pass the connection object (or the IDbTransaction) to methods that need to "continue" on the same transaction.

Sometimes it helps to elevate the connection to a member of the class in which you're doing the work, but this can create awkward situations - and complicates controlling the lifetime and disposal of the connection object (since it generally precludes use of the using statement).

Answer 2

Empirically, I have determined that (for the SQL Server provider) if the process can take advantage of connection pooling to share the connection (and the transaction) between the parent and child processes, the DTC will not necessarily become involved.

This is a big "if", however, as per your example, the connection created by the parent process cannot be shared by the child processes (you do not close/release the connection before invoking the child processes). This will result in a transaction that spans two actual connections, which will result in the transaction being promoted to a distributed transaction.

It seems that it would be easy to refactor your code to avoid this scenario: just close the connection created by the parent process before invoking the child processes.