Page 1 of 1
Another ORA-01453: SET TRANSACTION error
Posted: Fri 14 Jan 2011 12:36
by object
I started getting such errors:
"ORA-01453: SET TRANSACTION must be first statement of transaction"
I searched for this error and found similar posts, most of them related to either multi-threading issues or concurrent use of several connection. Neither of them is my case, but I've found what causes the problem for me: I have a view that reads data from a different Oracle database. The problem occurs only when there is an attempt to read data from that view from within a transaction created by OData service.
Suggested workaround (wrapping in a transaction scope) did not help.
Any tips are appreciated.
P.S. I managed to extract a small piece of code that triggers this problem:
Code: Select all
var roles = new Roles { Role = Guid.NewGuid().ToString() };
_service.AddEntity(roles);
string progId = Guid.NewGuid().ToString();
int piViewCount = (from p in _service.Entities.PiProgramView where p.ProgId == progId && true select p).Count();
_service.SaveChanges();
If I remove the lines that select entries from PiProgramView then everything works fine. And as I mentioned, this view selects data from a different Oracle database.[/code]
Posted: Fri 14 Jan 2011 12:58
by object
And this might be a workaround:
Code: Select all
using (var tx = new TransactionScope(TransactionScopeOption.Suppress))
{
int piViewCount =
(from p in _service.Entities.PiProgramView where p.ProgId == "123" && true select p).Count();
tx.Complete();
}
I thought it was because first time I ran it it worked. Looks like I can still get the error even when using transaction scope.
Posted: Mon 17 Jan 2011 18:08
by AndreyR
Could you please create a small test project illustrating the issue and send it to us (use the support * devart * com address or our
contact form).
This should speed up the investigation.
Posted: Tue 18 Jan 2011 07:53
by object
Andrey,
I am not sure it will be easy to extract a project from my code because it uses tables from a large corporate database. However it was easy to build a small test that exposes the problem. Here it is:
Code: Select all
var entityToSave = new MyEntities/();
context.MyEntities.AddObject(entityToSave);
using (var tx = new TransactionScope())
{
var otherContext = new MyContext(ConfigurationManager.ConnectionStrings["OtherConnectionString"].ConnectionString);
int count = (from e in otherContext.LinkedDatabaseView select e).Count();
}
context.SaveChanges();
The above code does the following:
1. Using an Oracle db context ("context" variable) creates a new entity ("entityToSave").
2. In a new transaction scope creates a new context ("otherContext") and reads something from an Oracle view exposed by LinkedDatabaseView. LinkedDatabaseView is a view that fetches some data from a different Oracle database. The link to a database was established using "CREATE DATABASE LINK" statement.
3. Saves newly created entityToSave.
What is principle here is that we read data from a different schema/database (via local view) while there are pending changes. This works as long as fetch data from a view using a different db connection. If I don't use "otherContext" and perform all db operations using "context", then I get the error!
Does this sound clear?
Posted: Wed 19 Jan 2011 17:38
by AndreyR
I have performed a test using the latest dotConnect for Oracle 6.0.86 and obtained an ORA-24777 error. Then I have set the "Transaction Scope Local" connection string parameter to "true" and my test succeeded.
Setting this parameter to true makes TransactionScope to use local transactions instead of distributed ones (it is the way TransactionScope is implemented in ODP.NET, and it is useful in some scenarios).
Please let me know if this advice is not helpful.
Posted: Thu 20 Jan 2011 08:34
by object
I can't find how to specify this in a connection string.
http://www.connectionstrings.com/oracle says nothing about such option. I found a blog post that mentions PROMOTABLE TRANSACTION=LOCAL. Is this the one you used?
Posted: Thu 20 Jan 2011 15:36
by object
Now I tried to add "Transaction Scope Local=True" to the connection string, and it didn't help.
What may be specific to my case is that view that I am reading data from points to data in a different Oracle database (linked to my local database).
Posted: Fri 21 Jan 2011 16:04
by AndreyR
I have sent a test project to the e-mail address you have provided in the forum profile.
Please either change the project or create a new one reproducing the problem.
Posted: Fri 21 Jan 2011 22:04
by object
Thanks but attachment didn't seem to go through.
Posted: Mon 24 Jan 2011 09:52
by bhav27
AndreyR wrote:I have sent a test project to the e-mail address you have provided in the forum profile.
Please either change the project or create a new one reproducing the problem.
we are getting the same error when using dotConnect for Oracle with Entity Framework. Can you advice the work around?
Posted: Mon 24 Jan 2011 10:15
by AndreyR
object, I have sent the attachment once again.
bhav27, I have sent you the same project.
Posted: Fri 06 Jan 2012 17:11
by bhav27
We are seeing this problem with direct connections to oracle with DAAB. I've sent support email.
Posted: Fri 13 Jan 2012 12:23
by Shalex
Try the latest (6.60.283) version of dotConnect for Oracle, because it includes the changed behaviour: pending operations on a connection are rolled back before returning the connection to pool now. This fix may resolve ORA-01453. If upgrade doesn't help:
bhav27 wrote:We are seeing this problem with direct connections to oracle with DAAB.
Please modify the test project (just sent to you by e-mail) and send it back to us so that we can reproduce the issue in our environment.
bhav27 wrote:we are getting the same error when using dotConnect for Oracle with Entity Framework. Can you advice the work around?
Modify the test project, which was sent to you by AndreyR (Jan 24, 2011), and send it back to us so that we can reproduce the issue in our environment.