TransactionScope的：避免分布式事务 [英] TransactionScope: Avoiding Distributed Transactions

问题描述

我有一个包含，除其他事项外父对象（DAL的一部分），集合（列表< T> ）的子对象的

当我保存对象返回到数据库，我输入/更新父，并通过每一个孩子，然后循环。对于可维护性，我已经把所有的代码为孩子到一个单独的私有方法。

我要使用标准的ADO事务，但在我的旅行，我迷迷糊糊在整个的TransactionScope对象，我相信这将让我来包装父类的方法所有的数据库交互在一个事务（与子方法的所有交互一起）。

所以，到目前为止好..？

所以，接下来的问题是如何创造这个TransactionScope的范围内使用连接。我听说使用多个连接，即使是相同的DB可以强制的TransactionScope以为它是一个分布式事务（包括一些昂贵的DTC工作）。

时的情况？或者是，我似乎是在其他地方读书，在使用相同的连接字符串（这将借给自己连接池）将被罚款的情况？

更​​实际说，做我...

1. 创建父和放大器单独的连接;孩子（尽管有相同的连接字符串）


2. 创建父连接的通过将它作为一个参数（似乎笨拙对我来说）


3. ？做别的事情...

更新：

的虽然看起来我会用我平时.NET3.5 +和SQL Server 2008+即可，该项目的另一部分将使用Oracle（10克），所以我还不如练技术，可以可以跨项目使用一致。的

的所以，我只是简单地通过孩子的方法连接。的

---

选项1代码示例：

 >使用（TransactionScope的TS =新的TransactionScope（））
 {
 {
使用（的SqlCommand CMD =新的SqlCommand（）） 
 {
 cmd.Connection =康恩; 
 cmd.Connection.Open（）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 
试
 {
 //创建和放大器;参数添加到命令
 
 //父对象保存到数据库
 cmd.ExecuteNonQuery（）; 
 
如果（（int）的cmd.Parameters [@结果。值！= 0）
 {
 //不正常
 // ROLLBACK TRANSACTION 
 ts.Dispose（）; 
返回FALSE; 
} 
，否则//查询保存OK 
 {
如果（更新）
 {
 enquiryID =（int）的cmd.Parameters [@ EnquiryID ]。值; 
} 
 
 //保存车辆（子对象）
如果（SaveVehiclesToEPE（））
 {
 ts.Complete（）; 
返回真; 
} 
，否则
 {
 ts.Dispose（）; 
返回FALSE; 
} 
} 
} 
赶上（异常前）
 {
 //记录错误
 ts.Dispose（）; 
抛出; 
} 
} 
} 
} 
  

解决方案

许多数据库ADO供应商（如Oracle ODP.NET）当您使用的TransactionScope 来在多个连接交易确实开始分布式事务 - 即使当它们共享相同的连接字符串。

一些供应商，（如SQL2008在.NET 3.5 +）时引用相同的连接字符串在一个事务中创建一个新的连接识别，并且不会造成在DTC工作。但连接字符串（如调整参数）在任何差异可能发生的历史妨碍这一点 - 以及行为将恢复使用分布式事务

不幸的是，唯一可靠确保您的交易将携手而无需创建一个分布式事务方法是连接对象（或 IDbTransaction ）传递给需要在同一交易继续的方法。

有时，它有助于提升中，你正在做的工作连接到类的成员，但可以创造尴尬的情况 - 和复杂控制寿命和处置连接对象的（因为它通常排除使用使用语句）。

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

解决方案

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

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