TransactionScope + Pooling

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

TransactionScope + Pooling

Post by peledkfir » 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?

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » 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.

peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

Post by peledkfir » 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.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » 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.

peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

Post by peledkfir » 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.

peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

Post by peledkfir » 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » 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).

peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

Post by peledkfir » 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 28 Jan 2011 15:58

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

peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

Post by peledkfir » 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();
            }
        } 

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 07 Feb 2011 13:31

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

peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

Post by peledkfir » Mon 07 Feb 2011 23:10

SWEET!

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » 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.

meetkarthik
Posts: 9
Joined: Thu 16 Sep 2010 20:10

Post by meetkarthik » 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.

Post Reply