Another ORA-01453: SET TRANSACTION error

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
object
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Another ORA-01453: SET TRANSACTION error

Post by object » Fri 14 Jan 2011 12:36

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]

object
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Post by object » Fri 14 Jan 2011 12:58

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.

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

Post by AndreyR » Mon 17 Jan 2011 18:08

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.

object
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Post by object » Tue 18 Jan 2011 07:53

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?

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

Post by AndreyR » Wed 19 Jan 2011 17:38

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.

object
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Post by object » Thu 20 Jan 2011 08:34

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?

object
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Post by object » Thu 20 Jan 2011 15:36

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).

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

Post by AndreyR » Fri 21 Jan 2011 16:04

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.

object
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Post by object » Fri 21 Jan 2011 22:04

Thanks but attachment didn't seem to go through.

bhav27
Posts: 20
Joined: Tue 20 Apr 2010 11:53

Post by bhav27 » Mon 24 Jan 2011 09:52

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?

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

Post by AndreyR » Mon 24 Jan 2011 10:15

object, I have sent the attachment once again.
bhav27, I have sent you the same project.

bhav27
Posts: 20
Joined: Tue 20 Apr 2010 11:53

Post by bhav27 » Fri 06 Jan 2012 17:11

We are seeing this problem with direct connections to oracle with DAAB. I've sent support email.

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 13 Jan 2012 12:23

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.

Post Reply