Click here to Skip to main content
14,330,041 members
Rate this:
Please Sign up or sign in to 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:

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
Updated 20-Dec-10 6:15am
v2
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.
Rate this:
Please Sign up or sign in to vote.

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";
   
Comments
Reddy Prakash 21-Dec-10 0:27am
   
what is enlist here?
Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   
v5
Comments
Espen Harlinn 13-Jan-11 16:22pm
   
5+ Right again :)

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100