TransactionScope 和 Oracle 的问题

Problems with TransactionScope and Oracle

提问by Mace

we have written a C# 3.5 client talking to an Oracle database (11g) using the ODP.NET.

我们编写了一个 C# 3.5 客户端，使用 ODP.NET 与 Oracle 数据库 (11g) 通信。

This application has a batch process where a long running task is performed making various calls to the database within a TransactionScope.

此应用程序有一个批处理，其中执行长时间运行的任务，对 TransactionScope 内的数据库进行各种调用。

On our development environment all goes well, but at the UAT environment of one of our clients (who has loads of data) two alternating (sometimes the one, sometimes the other...) errors occur:

在我们的开发环境中一切顺利，但在我们的一个客户（拥有大量数据）的 UAT 环境中，出现了两个交替（有时是一个，有时是另一个......）错误：

1. Unable to enlist in a distributed transaction
2. The transaction has aborted. (inner exception: Transaction Timeout)

1. 无法加入分布式事务
2. 事务已中止。（内部异常：事务超时）

We currently use a time-out of one dayfor the transaction (for testing purposes).

我们目前为交易使用一天的超时时间（用于测试目的）。

Running said process on the UAT environment causes to halt after approx. 10 mins with one of above exceptions, so no way near the timeout value.

在 UAT 环境中运行上述进程会导致在大约 5 秒后停止。10 分钟，上述例外之一，因此无法接近超时值。

Here's a snippet of the stacktrace for the second error:

这是第二个错误的堆栈跟踪片段：

at System.Transactions.TransactionStatePromotedAborted.CreateAbortingClone(InternalTransaction tx)
   at System.Transactions.DependentTransaction..ctor(IsolationLevel isoLevel, InternalTransaction internalTransaction, Boolean blocking)
   at System.Transactions.Transaction.DependentClone(DependentCloneOption cloneOption)
   at System.Transactions.TransactionScope.SetCurrent(Transaction newCurrent)
   at System.Transactions.TransactionScope.PushScope()
   at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption)
   at System.Transactions.TransactionScope..ctor()
   at Application.Domain.DataAccess.Oracle.EntityDaoBase`2.SaveItem(TEntity item, EntityReference`1 user)

The process tries to save an item to the DB within the transaction scope, but the stacktrace shows that the constructor is hit for the TransactionScope class, meaning it creates a new TransactionScope.

该过程尝试将一个项目保存到事务范围内的 DB，但堆栈跟踪显示构造函数已命中 TransactionScope 类，这意味着它创建了一个新的 TransactionScope。

Am I right so far?

到目前为止我是对的吗？

Because I don't know much of the inner workings of the TransactionScope, but it seems like when you call a method within the scope, it will create a new transaction (assumingly inheriting from the ambient transaction).

因为我不太了解 TransactionScope 的内部工作原理，但是看起来当您调用范围内的方法时，它会创建一个新事务（假设是从环境事务继承）。

Could it be that if I am right, that this new transaction does not inherit the correct timeout (but the default one), so that a nested transaction will cause this timeout exception?

难道如果我是对的，这个新事务没有继承正确的超时（而是默认的），从而嵌套事务会导致此超时异常？

If not, any thoughts on what it possibly can be? On a side note, there are no nested transactions defined within the methods called from within the ambient transaction.

如果没有，对它可能是什么的任何想法？附带说明一下，在从环境事务中调用的方法中没有定义嵌套事务。

Any help would be greatly appreciated!

任何帮助将不胜感激！

Edit 1:

编辑1：

Simplified code snippet of the function:

函数的简化代码片段：

public void SomeLengthyBatchProcess()
{
   using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(1, 0, 0, 0)))
   {
       foreach (var item in Items)
       {
          SaveItemToDB(item);
       }

       transaction.Complete();
   }
}

public void SaveItemToDB(object item)
{
   using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(1, 0, 0, 0)))
   {
       // Performing data persistency here

       transaction.Complete();
   }
}

Edit 2:

编辑2：

Okay, so as it turns out, there isa nested transaction going on in the method 'SaveItemToDB'. After some digging through the code a colleague made, I saw that it has its own TransactionScope defined, but without options and timeout.

好了，事实证明，有是一个嵌套事务的方法“SaveItemToDB”怎么回事。在对同事编写的代码进行了一些挖掘后，我看到它定义了自己的 TransactionScope，但没有选项和超时。

After modifying this method so that it has the same parameters regarding timeout, I ran the code again on the customer's server and still no luck (again the transaction aborted error with the time out).

修改此方法使其具有相同的超时参数后，我再次在客户的服务器上运行代码，但仍然没有运气（再次事务因超时而中止错误）。

So my questions are now as follows:

所以我现在的问题如下：

1. Is it necessary to define timeout values for nested transactions or do they inherit this from the ambient transaction?
2. How is it possible that a timeout exception can occur when the timeout setting is (presumably, aside from inner workings that I do not know about) the same for all transaction scopes and has a timeout value defined of 1 day, where the exception occurs after approx. 10 minutes?
3. Is it possible to prevent Oracle from creating a distributed transaction for transactions where the connectionstring is the same?
4. Can it be that the added overhead of a distributed transaction causes exceptions like the transaction aborted one?

1. 是否有必要为嵌套事务定义超时值，或者它们是否从环境事务继承？
2. 当所有事务范围的超时设置（大概，除了我不知道的内部工作之外）都相同并且超时值定义为 1 天时，超时异常怎么可能发生，异常发生在大约 10分钟？
3. 是否可以阻止 Oracle 为连接字符串相同的事务创建分布式事务？
4. 会不会是分布式事务增加的开销会导致像事务中止这样的异常？

I updated the code snippet so it better reflects the situation.

我更新了代码片段，以便更好地反映情况。

(btw: the second, nested transaction, is necessary because the DAL also seperately persists some child items, if present, and the whole item should of course, be rolled back if anything goes wrong while persisting the child items)

（顺便说一句：第二个嵌套事务是必要的，因为 DAL 还单独保留一些子项（如果存在），并且如果在保留子项时出现任何问题，当然应该回滚整个项目）

Hopefully with this addition it will be easier to shed some light on this issue!

希望通过这个添加，可以更容易地阐明这个问题！

回答by Mace

Because we couldn't find a solution, we have decided to stop using the TransactionScope for our purposes and arrange the rollback ourselves.

因为找不到解决方案，我们决定停止使用 TransactionScope 并自行安排回滚。

I find that TransactionScope and Oracle do not mix well, perhaps SQL Server handles it better, but that is not an option for us.

我发现 TransactionScope 和 Oracle 不能很好地混合，也许 SQL Server 处理得更好，但这不是我们的选择。

Thanks for reading.

谢谢阅读。

回答by gjax

the default transaction timeout in machine.config is 10 minutes...that is probably why you are timing out.

machine.config 中的默认事务超时为 10 分钟……这可能就是您超时的原因。

回答by b_levitt

I know this is an old question but I'll add to it since I've seen this quite a bit.

我知道这是一个老问题，但我会补充它，因为我已经看到了很多。

Are you using RAC? Have you worked with a DBA to see if you're experiencing locking/blocking. I've used System.Transactions with Oracle for years and the only time I've had similar issues is when we were using RAC and additional configuration needed to be done.

您在使用 RAC 吗？您是否与 DBA 合作过，看看您是否遇到锁定/阻塞。我已经将 System.Transactions 与 Oracle 结合使用多年，唯一一次遇到类似问题是在我们使用 RAC 并且需要进行额外配置时。

Here's what happens: You start a transaction and are opening connections during the transaction (which is fine). However, the oracle service is not configured for distributed transaction processing (it's a simple checkbox option on the service). So additional connections start spanning more than one instance in the RAC cluster, and the related transactions are unaware of each other causing the .net process to block itself.

下面是会发生的事情：您开始一个事务并在事务期间打开连接（这很好）。但是，oracle 服务没有配置为分布式事务处理（它是服务上的一个简单的复选框选项）。因此，额外的连接开始跨越 RAC 集群中的多个实例，并且相关事务彼此不知道，导致 .net 进程自我阻塞。

It's a simple fix. The oracle service you are using just needs DTP enabled.

这是一个简单的修复。您使用的 oracle 服务只需要启用 DTP。

回答by Krishna Desiraju

although an old question, am hoping this answer helps... this especially happens for a long running transactions because the underlying IDbConnection does not remain open for longer duration and new connection is created for parts of transactionscope (connection pooling). it is for the same reason, the long transaction could succeed if the same open connection is returned and used else it fails. Only solution for this is to control connection creation and ensure that only one connection is used throughout.

虽然是一个老问题，但我希望这个答案有帮助......这尤其发生在长时间运行的事务中，因为底层 IDbConnection 不会长时间保持打开状态，并且为部分事务范围（连接池）创建了新连接。出于同样的原因，如果返回并使用相同的打开连接，则长事务可能会成功，否则会失败。唯一的解决方案是控制连接创建并确保始终只使用一个连接。

回答by Eugene Beresovsky

Addressing the main issue first:

先解决主要问题：

2. How is it possible that a timeout exception can occur when the timeout setting is (presumably, aside from inner workings that I do not know about) the same for all transaction scopes and has a timeout value defined of 1 day, where the exception occurs after approx. 10 minutes?

2. 当所有事务范围的超时设置（大概，除了我不知道的内部工作之外）都相同并且超时值定义为 1 天时，超时异常怎么可能发生，异常发生在大约 10分钟？

There is the TransactionManager.MaximumTimeoutproperty which is the upper boundof whatever you are trying to set via your scope. On your system, it is set to 10 minutes, but according to the documentation

有一个TransactionManager.MaximumTimeout属性是您尝试通过范围设置的任何内容的上限。在您的系统上，它设置为10 minutes，但根据文档

This value can be set in the MachineSettingsSection of the config file.

该值可以在配置文件的 MachineSettingsSection 中设置。

As to the other questions:

至于其他问题：

1. Is it necessary to define timeout values for nested transactions or do they inherit this from the ambient transaction?

1. 是否有必要为嵌套事务定义超时值，或者它们是否从环境事务继承？

The scope initiating a transaction(i.e. any RequiresNewscope, any outermost Requiredscope, and any Requiredscope that has a Suppressscope one level up the nesting stack) will establish a transaction timeout, and as far as my reading of the sourcesgoes, this timeout is not affected by nested scopes.

启动事务的作用域（即任何RequiresNew作用域、任何最外层Required作用域以及Required具有Suppress嵌套堆栈上一级作用域的任何作用域）将建立一个transaction timeout，并且就我对源的阅读而言，此超时不受嵌套的影响范围。

However, every nested scope participating in an existing transaction(i.e. any Requiredscope that has a Requiredor RequiresNewscope one level up the stack) will establish its own scope timeoutthat runs in addition to the transaction timeoutmentioned above.

然而，参与现有事务的每个嵌套作用域（即任何Required具有堆栈上一级Required或RequiresNew作用域的作用域）都将建立自己的作用域超时，该超时除了上述事务超时之外还运行。

Transaction timeoutsand scope timeoutsare implemented differently internally, but if any one of these timeouts hits, a transaction yet to be Complete()d would be rolled back.

事务超时和作用域超时在内部的实现方式不同，但如果这些超时中的任何一个命中，尚未完成的事务Complete()将被回滚。

Btw, aforementioned TransactionManager.MaximumTimeoutonly applies to transaction timeouts. Scope timeoutsdo not have an upper bound. Not that it really matters, as the shortest timeout is what counts anyway.

顺便说一句，上述TransactionManager.MaximumTimeout仅适用于事务超时。范围超时没有上限。并不是说这真的很重要，因为最短的超时时间才是最重要的。

3. Is it possible to prevent Oracle from creating a distributed transaction for transactions where the connectionstring is the same?

3. 是否可以阻止 Oracle 为连接字符串相同的事务创建分布式事务？

As long as you have only one "physical" DB connection open at any single point in time, the scope will not escalate to DTC. If I recall correctly, this works with Oracle ODP.Net, despite (this) seemingly claiming the opposite (maybe it did not work with the version at the time?).

只要您在任何一个时间点只打开一个“物理”数据库连接，范围就不会升级为 DTC。如果我没记错的话，这适用于 Oracle ODP.Net，尽管 ( this) 似乎声称相反（也许它当时不适用于该版本？）。

You may or may not be able to prevent concurrent connections even with nested scopes, and for different databases(as long as they are on the same server).

即使使用嵌套作用域，对于不同的数据库（只要它们在同一台服务器上），您也可能无法阻止并发连接。

回答by mcauthorn

Is it possible for you to please show a snippet of code? From what you mentioned The only thing I could find was related with System.Transactions. The discussion is here. Of course their "solution" is to make sure you are using at least ODP.NET 11.1.0.6.20 or higher.

能否请您展示一段代码？从你提到的我能找到的唯一一件事是与 System.Transactions 相关。讨论在这里。当然，他们的“解决方案”是确保您至少使用 ODP.NET 11.1.0.6.20 或更高版本。