Another ORA-01453: SET TRANSACTION error

[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]

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

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

[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?

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

[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?

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

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

[object]

Thanks but attachment didn't seem to go through.

[bhav27]

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]

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

[bhav27]

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

[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:

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.

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.