OraFAQ Forum: Windows » Oracle Non Transaction Inserts

Home » Infrastructure » Windows » Oracle Non Transaction Inserts (Oracle, 10g Express, Windows Server 2003)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Oracle Non Transaction Inserts [message #529605]

Wed, 02 November 2011 02:42

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.

Report message to a moderator

Re: Oracle Non Transaction Inserts [message #529606 is a reply to message #529605]

Wed, 02 November 2011 02:53

Michel Cadot
Messages: 54247
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

Report message to a moderator

Re: Oracle Non Transaction Inserts [message #529608 is a reply to message #529606]

Wed, 02 November 2011 03:02

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.

Report message to a moderator

Re: Oracle Non Transaction Inserts [message #529611 is a reply to message #529608]

Wed, 02 November 2011 03:16

Littlefoot
Messages: 16998
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.

Report message to a moderator

Re: Oracle Non Transaction Inserts [message #529616 is a reply to message #529611]

Wed, 02 November 2011 03:29

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?

Report message to a moderator

Re: Oracle Non Transaction Inserts [message #529620 is a reply to message #529608]

Wed, 02 November 2011 03:37

Michel Cadot
Messages: 54247
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

Report message to a moderator

Previous Topic:	install forms and reports 11g
Next Topic:	Client Logging

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 25 15:45:24 CDT 2013

Total time taken to generate the page: 1.46770 seconds