Devart Forums

Posted: **Sun 16 Jan 2011 22:34**

I'm using devart version 6.0.70.0
.NET 4.0 application hosted on IIS 7 windows server 2008 64bit.
Oracle DB 11.2g proper oci installed.
Using EF.

When using transaction scope with pooling I'm getting this exception:

System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: ORA-24776: cannot start a new transaction
(Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
Devart.Data.Oracle.OracleException: ORA-24776: cannot start a new transaction at Devart.Data.Oracle.ah.b(Int32
A_0) at Devart.Data.Oracle.a2.b(Boolean A_0) at Devart.Data.Oracle.OracleInternalConnection.Rollback() at
Devart.Common.s.a(Enlistment A_0) at System.Transactions.VolatileEnlistmentAborting.EnterState(InternalEnlistment
enlistment) at System.Transactions.TransactionStateAborted.EnterState(InternalTransaction tx) at
System.Transactions.Transaction.Rollback() at System.Transactions.TransactionScope.InternalDispose() at
System.Transactions.TransactionScope.Dispose()

*************

Tried to run with connection validation but it didn't helped.

Currently running with pooling=false.
What I'm doing wrong?

Posted: **Tue 18 Jan 2011 15:43**

Could you please describe the scenario in which the problem occurs in more details? Also, please specify the connection string (at least the parameters not related to connectivity itself) you are using.

For example, did you try changing the 'Transaction Scope Local' or 'Enlist' connection string parameters? The first one specifies that only a single internal connection is used inside the TransactionScope environment, the second one allows to automatically enlist the connection in the current transaction context.

Posted: **Tue 18 Jan 2011 21:26**

I didn't tried Enlist because in visual studio wizard when setting the connection I saw it is default true.

I'll try 'Transaction Scope Local' although I read in your blog that it solves different problem.

My service, for each request, work always with transaction scope for calling save changes of 2 entity framework contexts (2 different models) and executing stored procedure in the DB using OracleCommand (ADO).

The problem occurs after few requests. As much as I know, it happens when I receive from the pool a connection that already enlisted to other transaction. During debug, I didn't saw a reference to transaction in the connection but I saw it is enlisted to an other transaction.

Posted: **Wed 19 Jan 2011 17:41**

I will send you a test project in a letter, please check that it was not blocked by your mail filter. Please specify what should be changed in the sample to reproduce the problem, or, if possible, send us your test project.

Posted: **Tue 25 Jan 2011 21:15**

I managed to isolate the problem:

Connection string:

Code: Select all

Service code:

Code: Select all

using System;
using System.Transactions;
using Devart.Data.Oracle;

namespace PoolingProblemService
{
    public class Service1 : IService1
    {
        public void Foo()
        {
            using (var conn = new OracleConnection("User Id=***;Password=***;Server=***;Home=***;Persist Security Info=True"))
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    conn.Open();

                    using (var command = conn.CreateCommand())
                    {
                        command.CommandText = "select 1 from dual";
                        command.ExecuteNonQuery();
                    }

                    scope.Complete();
                    conn.Close();
                }
            }
        }

        public void Foo2()
        {
            using (var conn = new OracleConnection("User Id=***;Password=***;Server=***;Home=***;Persist Security Info=True"))
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    conn.Open();

                    using (var command = conn.CreateCommand())
                    {
                        command.CommandText = "INSERT INTO oh_table3 values (:pId)";
                        var param1 = command.CreateParameter();
                        param1.Value = Guid.NewGuid();
                        param1.ParameterName = "pId";
                        param1.DbType = System.Data.DbType.Binary;
                        command.Parameters.Add(param1);
                        command.ExecuteNonQuery();
                    }

                    scope.Complete();
                    conn.Close();
                }
            }
        }

        public void Boo()
        {
            using (var model = new OHEntities2())
            {
                model.Connection.Open();
                model.OH_TABLE3.AddObject(new OH_TABLE3() { ID = Guid.NewGuid() });
                model.SaveChanges();
                model.Connection.Close();
            }
        }

        public void Boo2()
        {
            using (var model = new OHEntities2())
            using (TransactionScope scope = new TransactionScope())
            {
                model.Connection.Open();
                model.OH_TABLE3.AddObject(new OH_TABLE3() { ID = Guid.NewGuid() });
                model.SaveChanges();
                scope.Complete();
                model.Connection.Close();
            }
        }
    }
}

Edmx file:

Code: Select all

The following steps are:

Foo
then
Boo
=> checking if any data committed to the DB will show it is not committed at all until the connection is closed in the pool (Disconnect in dbMonitor)

Example 2:
Foo2
then
Boo
=> the data is committed right after Boo is done.

Example 3:
Foo/Foo2
then
Boo2
=> the data is commited right after Boo is done.

Example 4:
Foo
then
Boo
then
Boo/Foo repeated.
You will get the exception:
ORA-24776: cannot start a new transaction
or
ORA-01453: SET TRANSACTION must be first statement of transaction

Kfir.

Posted: **Wed 26 Jan 2011 11:32**

I noticed it does not related to ADO.NET
Adding the following functions:

Code: Select all

        public void Doo() 
        { 
            using (var model = new OHEntities2()) 
            { 
                model.Connection.Open(); 
                model.OH_TABLE3.FirstOrDefault();
                model.Connection.Close(); 
            } 
        } 


        public void Doo2() 
        { 
            using (var model = new OHEntities2()) 
            using (TransactionScope scope = new TransactionScope()) 
            { 
                model.Connection.Open(); 
                model.OH_TABLE3.FirstOrDefault(); 
                scope.Complete(); 
                model.Connection.Close(); 
            } 
        }

For example:

running Doo
then
Boo
Transaction is committed immediately.

But
running Doo2
then
Boo
Transaction is not committed immediately.

Posted: **Thu 27 Jan 2011 11:56**

Thank you for the report, we have reproduced the problems.
I will let you know about the results of our investigation.
As a workaround you can set the "Transaction Scope Local" connection string parameter to true:

Code: Select all

This solution is appropriate in case you don't need distributed transaction (your case doesn't need it at first glance).

Posted: **Thu 27 Jan 2011 21:05**

I need distributed transactions.
But I found an other work around for now:
always use TranasactionScope when saving changes with entity framework.

It even seems it fixes my other problem (not 100% sure yet):
http://www.devart.com/forums/viewtopic.php?t=20003

I ran 20 hours stress test and the hang didn't occur with the workaround I just mentioned.
I'm still testing to make sure that this is what fixed that too.

Posted: **Fri 28 Jan 2011 15:58**

Thank you for sharing the workaround. Our investigation is in progress.

Posted: **Sun 30 Jan 2011 11:24**

I found an other problem when working with transactions.
When trying to open a new connection with transaction suppress option I get an exception.
When trying the same with sql server. It works fine.

Code: Select all

        public void Hoo() 
        { 
            using (TransactionScope scope = new TransactionScope()) 
            {
               using (var model = new OHEntities2()) 
               using (TransactionScope scope1 = new TransactionScope()) 
               { 
                   model.Connection.Open(); 
                   model.OH_TABLE3.FirstOrDefault();
                   model.Connection.Close(); 
               } 

               using (TransactionScope scope2 = new TransactionScope(TransactionScopeOption.Suppress)) 
               { 
                   model.Connection.Open(); 
                   model.OH_TABLE3.FirstOrDefault();
                   model.Connection.Close(); 
               }

	       scope.Complete();
            }
        }

Posted: **Mon 31 Jan 2011 15:41**

Thank you for the report, I have reproduced this error as well.
I will let you know about the results of our investigation.

Posted: **Mon 07 Feb 2011 13:31**

We have fixed these problems. The fixed build will be available in a week or so.

Posted: **Mon 07 Feb 2011 23:10**

SWEET!

Posted: **Fri 11 Feb 2011 11:58**

We have released the new build yesterday. This build can be downloaded from the Download page (the trial version) or from Registered Users' Area (for users with active subscription only).
For the detailed information about the fixes and improvements available in dotConnect for Oracle 6.10.103, please refer to this post.

Posted: **Tue 15 Feb 2011 18:01**

Audrey,
I downloaded the latest version (103) of dotconnect and i still have problems with the transaction.

I am connecting to oracle in Direct mode and i am using transaction scope 'RequiredNew'. I have two functions 'Add' and 'Update'. I add an entity in the first function and update the entity in the second.

public entity Add();
public entity update(entity);

Both functions use seperate transaction scopes. But when i do a performace test against this, the entity added in the first call is not getting committed. As a result of this, the update call fails (coz its not able to find the added entity).

The exact error message is "EXCEPTION (LEVEL 1): Devart.Data.Linq.ChangeConflictException
EXCEPTION OUTPUT: 1 of 4 updates failed.".

I have been having this issue since 5.70.190.0. This seems to work fine in 5.70.180.0.

Devart Forums

TransactionScope + Pooling

TransactionScope + Pooling