EF和TransactionScope用于SQL Server和Oracle，而不会升级/跨越DTC？ [英] EF and TransactionScope for both SQL Server and Oracle without escalating/spanning to DTC?

问题描述

任何人都可以更新我这个话题吗？

我想在我的应用程序中同时支持SQL Server和Oracle。

是否可以拥有以下内容代码（在BL中）适用于SQL Server和Oracle，而不会升级/跨越分布式事务（DTC）？

  // dbcontext在之前创建，相同的dbcontext将被两个存储库使用
使用（var ts = new TransactionScope（））
 {
 //创建顺序 - 使用dbcontext，可能在此调用SaveChanges 
 orderRepository.CreateOrder（order）; 
 
 //更新库存 - 使用相同的dbcontext，可能在这里调用SaveChanges 
 inventoryRepository.UpdateInventory（inventory）; 
 
 ts.Complete（）; 
} 
  

截至今天8月底，我明白它适用于SQL Server 2008+ ...但是Oracle呢？我发现这个线程 ...看起来像是Oracle正在推广到分布式事务，但仍然没有对我清楚有没有人有过编写应用程序的经验，以支持SQL Server和Oracle与实体框架来启发我？

谢谢！

更新：最后我注意到EF6附带了改进的事务支持。这个，除了Remus的建议可能是我的解决方案。

解决方案

首先：永远不要使用 var ts = new TransactionScope（）。是一个班轮杀死你的应用程序始终使用允许您指定隔离级别的显式构造函数。请参阅使用新的TransactionScope（ ）认为有害的。

现在关于你的问题：不将同一范围内的两个连接推广到DTC的逻辑主要在于驱动程序/提供者合作通知System.Transactions，两个不同的连接能够管理分布式事务自己，因为所涉及的资源管理器是相同的。 SqlClient post SQL Server 2008是一个能够执行此逻辑的驱动程序。您使用的Oracle驱动程序不是（而且我不知道任何版本是btw）。

最终真的非常基本：如果你不想要DTC，不要创建一个！确保在范围内使用一个连接。很明显，你不需要两个连接。换句话说，摆脱数据模型中的两个独立的存储库。只能使用一个存储库订单，库存，还有什么没有。你正在和他们一起射击自己，你正在要求小精灵解决方案。

更新： Oracle驱动程序12c r1 ：

事务和连接关联：默认情况下，ODP.NET连接仅在连接对象关闭或事务对象被处理时才从事务中分离

Can anyone update me on this topic?

I want to support both SQL Server and Oracle in my application.

Is it possible to have the following code (in BL) working for both SQL Server and Oracle without escalating/spanning to distributed transactions (DTC) ?

 // dbcontext is created before, same dbcontext will be used by both repositories
 using (var ts = new TransactionScope())
 {
    // create order - make use of dbcontext, possibly to call SaveChanges here
    orderRepository.CreateOrder(order);

    // update inventory - make use of same dbcontext, possibly to call SaveChanges here
    inventoryRepository.UpdateInventory(inventory);

    ts.Complete();
 }

As of today, end of August 2013, I understand that it works for SQL Server 2008+ ... but what about Oracle? I found this thread... it looks like for Oracle is promoting to distributed transactions but is still not clear to me.

Does anyone have experience with writing apps to support both SQL Server and Oracle with Entity Framework to enlighten me?

Thanks!

Update: Finally I noticed EF6 comes with Improved Transaction Support. This, in addition to Remus' recommendations could be the solution for me.

解决方案

First: never use var ts = new TransactionScope(). Is the one liner that kills your app. Always use the explicit constructor that let you specify the isolation level. See using new TransactionScope() Considered Harmful.

Now about your question: the logic not to promote two connections in the same scope into DTC relies heavily on the driver/providers cooperating to inform the System.Transactions that the two distinct connections are capable of managing the distributed transaction just fine on their own because the resource managers involved is the same. SqlClient post SQL Server 2008 is a driver that is capable of doing this logic. The Oracle driver you use is not (and I'm not aware of any version that is, btw).

Ultimately is really really really basic: if you do not want a DTC, do not create one! Make sure you use exactly one connection in the scope. It is clearly arguable that you do not need two connections. In other words, get rid of the two separate repositories in your data model. Use only one repository for Orders, Inventory and what else what not. You are shooting yourself in the foot with them and you're asking for pixie dust solutions.

Update: Oracle driver 12c r1:

"Transaction and connection association: ODP.NET connections, by default, detach from transactions only when connection objects are closed or transaction objects are disposed"

这篇关于EF和TransactionScope用于SQL Server和Oracle，而不会升级/跨越DTC？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！