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;
                        }
                    }
                }
            }

Solution 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).

Solution 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.

Solution 3

In your example the TransactionScope is still in the context of a method, you could simply create a SqlTransaction with multiple commands beneath that. Use TransactionScope if you want to move the transaction out of a method, to say, the caller of that method, or if you access multiple databases.

Update: never mind I just spotted the child call. In this situation, you could pass the connection object to child classes. Also, you don't need to manually dispose the TransactionScope - using blocks act like try-finally blocks and will execute the dispose even on exceptions.

Update 2: better yet, pass the IDbTransaction to the child class. The connection can be retrieved from that.

Comments

[CJM]

I was under the impression that the Connection has to be created within the TransactionScope for it to be covered. I'm guessing that simply passing the Connection through is simpler and tidier than figuring out some other workaround?

[CJM]

Yes, I'd understood that the disposal wasn't explicitly needed, but when I retrofit the TransactionScope to my code, clearly I'd forgotten and continued to convert my old trm.Rollback statements to ts.Dispose without thinking. Good spot!

[CJM]

Are you saying that if I pass through the connection object, I need to close in the parent and reopen in the child?

[Daniel Pratt]

No. I was assuming that you were trying to take advantage of the convenience of TransactionScope without causing the transaction to be promoted to a distributed transaction. It sounds like this isn't possible when targeting Oracle databases, but it is possible targeting SQL Server databases...so long as the connections can be pooled and there is only one open connection at a time.

[Richard Dingwall]

Re Update 2 -You can just pass the IDbConnection to the child class. If you use IbConnection.BeginTransaction(), all commands created with IDbConnection.CreateCommand() will automatically have the transaction associated. This is better because it's one less parameter to pass down your heirarchy, and reduces coupling as child classes won't need to be concerned with whether they are executing within a transaction or not.

[pauloya]

Do you know if this is changed with Oracle 12c? I see it marked as "Available in ODAC 12c or later." here: apex.oracle.com/pls/apex/…

[Timo]

That's good to know. It is good practice to close the DbConnection before working with the next one anyway, and if that reliably avoids distributed transactions (given that the connection strings are identical), all the better!