oracle connection cannot support parallel transactions

Question

1.00/5 (1 vote)

See more:

Hi All,

Actually my windows service does some database transactions like insert, select and update.
Based on the incoming requests scripts will be executed. I am maintaining one Oracle connection for whole the application. I am facing this problem when my service receives two requests in which it is trying to perform simultaneous inserts and updates using the same oracle connection and begintransaction() method.

My code is some thing like this:

C#

public static oracleconnection  dbconn = new oracleconnection("...");
OracleTransaction dbtrans=null;
public void insert()
{
    dbconn.open();
    dbconn.begintx();
    // more code here ...
}

public void update()
{
    dbconn.open();
    dbconn.begintx();
    // more code here ...
}
public transaction begintx()
{
    return dbconn.BeginTransaction();
}

When both the methods are called simultaneously I am getting the error
"oracle connection cannot support parallel transactions" since the same Oracle connection is used here and only one transaction is allowed as I found out per google search.

Can any one suggest a solution how to handle this.

Thank you!

Posted 20-Dec-10 5:51am

Reddy Prakash

Updated 20-Dec-10 6:15am

Manfred Rudolf Bihy

v2

Add a Solution

Comments

Manfred Rudolf Bihy 20-Dec-10 12:16pm

Edit for grammar and spelling, added code tags.

Manfred Rudolf Bihy 21-Dec-10 4:04am

Did you vote 2? If you did please reconsider voting higher as you have accepted my answer. Or if there is a reason for the vote of 2 please leave a comment on my answer why it was voted so low.

2 solutions

Solution 2

Been a long time since I used Oracle, but try this

string yourconnectionstring = "user id=username;" +
                              "password=yourpassword;" +
                              "data source=yourdatasource;" +
                              "enlist=false" +
                              "pooling=false";

Posted 20-Dec-10 7:15am

Gonzoox

Comments

Reddy Prakash 21-Dec-10 0:27am

what is enlist here?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Accepted Answer · 2010-12-20T06:07:00

1. Sorry, the only way to go here if you need to have two parallel transactions is to use two separate connections, or better yet a separate connection for each concurrent transaction.
If you use connection pooling the time to get a new (free) connection should also be tolerable if you initalize the connection pool with an appropriate amount of connections.

2. You can also go for the option to serialize all operations on the connection. You queue up work items as the requests come in and in a separate thread you handle them by dequeueing them and doing the DB processing stuff. Caveat!: This is not an advisable scenario for a service as doing the DB requests sequentially will slow everything down and it's not a scalable solution.
It might be the only way to go though if you're not allowed to make more than one connection.

Note on the side:
One thing you have to look out for is the Oracle DB administrators though. One time at work I swamped one of their servers with 50 parallel connections and a heavy load of queries and they almost fried me for that, since the server was also used by others and they were no longer able to use the database. So it might be diligent to inquire how many parallel connections you're allowed to use. :)

Regards,

Manfred