Problems with TransactionScope and Oracle

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

This application has a batch process where a long running task is performed making various calls to the database within a 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:

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

We currently use a time-out of one day for 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.

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.

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

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:

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:

Okay, so as it turns out, there is a 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.

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 abo开发者_开发技巧rted 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?

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)

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

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

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

Thanks for reading.

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

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.

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.

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.

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

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.

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?

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

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

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?

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

However, every nested scope participating in an existing transaction (i.e. any Required scope that has a Required or RequiresNew scope one level up the stack) will establish its own scope timeout that runs in addition to the transaction timeout mentioned above.

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

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

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

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

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