TransactionScope:避免分布式事务

问题描述

我有一个父对象(DAL 的一部分)，其中包含子对象的集合(List).

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.

我打算使用标准的 ADO 事务，但在我的旅行中，我偶然发现了 TransactionScope 对象，我相信这将使我能够将所有数据库交互包装在父方法中(以及子方法中的所有交互)一笔交易.

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

那么下一个问题是如何在这个 TransactionScope 内创建和使用连接.我听说使用多个连接，即使它们是同一个 DB 也会迫使 TransactionScope 认为它是一个分布式事务(涉及一些昂贵的 DTC 工作).

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. 在父级中创建单独的连接 &child(尽管具有相同的连接字符串)
2. 在父级中创建一个连接并将其作为参数传递(对我来说似乎很笨拙)
3. 做点别的……?

更新:

虽然看起来我可以使用我常用的 .NET3.5+ 和 SQL Server 2008+，但该项目的另一部分将使用 Oracle (10g)，所以我不妨练习一种技术跨项目一致使用.

所以我只是将连接传递给子方法.

选项 1 代码示例:

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

推荐答案

当您使用 TransactionScope 跨多个连接进行事务时，许多数据库 ADO 提供程序(例如 Oracle ODP.NET)确实会开始分布式事务- 即使它们共享相同的连接字符串.

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.

某些提供程序(如 .NET 3.5+ 中的 SQL2008)识别何时在引用相同连接字符串的事务范围内创建新连接，并且不会导致 DTC 工作.但是连接字符串中的任何变化(例如调整参数)可能会阻止这种情况发生 - 并且行为将恢复为使用分布式事务.

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.

不幸的是，在不创建分布式事务的情况下确保您的事务协同工作的唯一可靠方法是将连接对象(或 IDbTransaction)传递给需要在同一对象上继续"的方法.交易.

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.

有时将连接提升到您在其中进行工作的类的成员会有所帮助，但这会造成尴尬的情况 - 并使控制连接对象的生命周期和处置变得复杂(因为它通常排除使用using 语句).

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

这篇关于TransactionScope:避免分布式事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！