SQL Server 和 Oracle 的 EF 和 TransactionScope 没有升级/跨越到 DTC? [英] EF and TransactionScope for both SQL Server and Oracle without escalating/spanning to DTC?

问题描述

有人可以就这个话题向我更新吗?

Can anyone update me on this topic?

我希望在我的应用程序中同时支持 SQL Server 和 Oracle.

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

是否可以让以下代码(在 BL 中)同时适用于 SQL Server 和 Oracle，而无需升级/跨越到分布式事务 (DTC)?

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

截至今天，即 2013 年 8 月，我知道它适用于 SQL Server 2008+ ......但是 Oracle 呢?我发现这个线程...看起来Oracle正在推广分布式事务，但仍然没有对我来说很清楚.

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.

有没有人有编写应用程序以支持 SQL Server 和 Oracle 与实体框架的经验来启发我?

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

谢谢！

更新: 最后我注意到 EF6 带有 改进交易支持.除了 Remus 的建议外，这可能是我的解决方案.

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

推荐答案

第一:永远不要使用 var ts = new TransactionScope().是杀死您的应用程序的一个班轮.始终使用可让您指定隔离级别的显式构造函数.请参阅 使用新的 TransactionScope() 被认为是有害的.

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.

现在关于您的问题:不将同一范围内的两个连接提升到 DTC 的逻辑在很大程度上依赖于驱动程序/提供者合作通知 System.Transactions 这两个不同的连接能够很好地管理分布式事务他们自己的，因为所涉及的资源管理器是相同的.SqlClient post SQL Server 2008 是一个能够执行此逻辑的驱动程序.您使用的 Oracle 驱动程序不是(顺便说一句，我不知道任何版本).

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

最终真的真的非常基本:如果您不想要 DTC，请不要创建！确保在范围内只使用一个连接.您不需要两个连接显然是有争议的.换句话说，摆脱数据模型中的两个独立存储库.仅使用一个存储库来存储订单、库存以及其他不包含的内容.您正在用他们的脚射击自己，并且您正在寻求精灵尘解决方案.

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.

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

事务和连接关联:ODP.NET 连接，默认情况下，只有在连接对象关闭或事务对象被释放时才与事务分离"

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

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