Problems with TransactionScope and Oracle

Question

Hello,

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

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!

Answer 1

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.

Answer 2

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.