Home » Infrastructure » Windows » Oracle Non Transaction Inserts (Oracle, 10g Express, Windows Server 2003)
Oracle Non Transaction Inserts [message #529605] Wed, 02 November 2011 02:42 Go to next message
eftenpuften
Messages: 3
Registered: November 2011
Location: Ankara
Junior Member
hi everybody;
I have been facing with a problem on a multi client-server software. We are using Oracle 10g, on Windows 2003 server. We run a software which is acting as a server for our clients. Well; the problem occurs when there occurs simultaneously connections. We have a code block which runs on non-transaction base inserts. If each inserts and other calculations does not fire any exception, we commit all the inserts at the end of these operations. Each table has an auto increment primary key with a sequence. In briefly; when that simultaneuosly connections occurs; i got an unique primary key error, and sometimes "Collection was modified; enumeration operation may not execute" exception is fired.
Could anyone help me ? I think the problem occurs becuase i used non-transaction base inserts, but i am not sure.
Thanks for your helps from now on.
Re: Oracle Non Transaction Inserts [message #529606 is a reply to message #529605] Wed, 02 November 2011 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58860
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Collection was modified; enumeration operation may not execute

afaik, This is not an Oracle message.

What is an "non-transaction base inserts"?
All Oracle inserts are transactional (in the meaning they are part of a transaction).

Regards
Michel
Re: Oracle Non Transaction Inserts [message #529608 is a reply to message #529606] Wed, 02 November 2011 03:02 Go to previous messageGo to next message
eftenpuften
Messages: 3
Registered: November 2011
Location: Ankara
Junior Member
there are lots of insert statements; update statements and calculations in a function. and i manually start a transaction; and if there is not any exception occurrs in any part of the function; again i manually commit the transaction at the end of that function to be able to rollback if an exception occurs all the inserts and updates.
such as;
function a(){
   try{
       begin_transaction();
       inserts 
       updates
       commit();
   }catch()
   {
    rollback();
   }
}

in insert part; it gives the unique constraint violated exception since all tables have an autoincrement primary key. this code block works well if there is 1 connection to the server; however it explodes when there are more connection and trying to insert to the same table at the same time.
Re: Oracle Non Transaction Inserts [message #529611 is a reply to message #529608] Wed, 02 November 2011 03:16 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did you enforce an "autoincrement primary key"? That shouldn't happen if it was a sequence.
Re: Oracle Non Transaction Inserts [message #529616 is a reply to message #529611] Wed, 02 November 2011 03:29 Go to previous messageGo to next message
eftenpuften
Messages: 3
Registered: November 2011
Location: Ankara
Junior Member
it's sequence. its really strange i know, and i could not find any solution to handle this issue :s could the problem occur because i start transaction as Read Committed?
Re: Oracle Non Transaction Inserts [message #529620 is a reply to message #529608] Wed, 02 November 2011 03:37 Go to previous message
Michel Cadot
Messages: 58860
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i manually start a transaction;

You cannot do this in Oracle.

It seems you have not an Oracle problem but an application one that badly implements its (concurrent) accesses to Oracle.

Regards
Michel
Previous Topic: install forms and reports 11g
Next Topic: Client Logging
Goto Forum:
  


Current Time: Fri Aug 22 05:21:06 CDT 2014

Total time taken to generate the page: 0.13350 seconds