TransactionScope + Pooling
TransactionScope + Pooling
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?
.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?
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
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.
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.
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.
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
I managed to isolate the problem:
Connection string:
Service code:
Edmx file:
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.
Connection string:
Code: Select all
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();
}
}
}
}
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.
I noticed it does not related to ADO.NET
Adding the following functions:
For example:
running Doo
then
Boo
Transaction is committed immediately.
But
running Doo2
then
Boo
Transaction is not committed immediately.
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();
}
}
running Doo
then
Boo
Transaction is committed immediately.
But
running Doo2
then
Boo
Transaction is not committed immediately.
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:
This solution is appropriate in case you don't need distributed transaction (your case doesn't need it at first glance).
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
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.
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.
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.
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();
}
}
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.
For the detailed information about the fixes and improvements available in dotConnect for Oracle 6.10.103, please refer to this post.
-
- Posts: 9
- Joined: Thu 16 Sep 2010 20:10
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.
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.