Home » SQL & PL/SQL » SQL & PL/SQL » Multiple insert for the same row (Oracle 9.2.0.1.0)
Multiple insert for the same row [message #408591] Wed, 17 June 2009 00:45 Go to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
Hi Friends

I have two sessions, when i insert row from one session and don't commit or rollback at the same time from second session user insert the same row and its goes into wait.

My Question is instead of wait can second session user return with message or error.

Regards

Chintan
Re: Multiple insert for the same row [message #408594 is a reply to message #408591] Wed, 17 June 2009 00:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What error? You can't tell that user that such a record already exists, because you don't know if user one will commit.
This means that user two cannot create any detail records for the record he wants to insert, etc.
Re: Multiple insert for the same row [message #408595 is a reply to message #408591] Wed, 17 June 2009 01:01 Go to previous messageGo to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks For the reply

since first user decide to commit or rollback, second user is in wait if he gets prompt reply then he tries later and do some other work.

Chintan
Re: Multiple insert for the same row [message #408600 is a reply to message #408591] Wed, 17 June 2009 01:13 Go to previous messageGo to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
another think is that user one is never do rollback is he finds any error he changes the status of record.

Chintan
Re: Multiple insert for the same row [message #408612 is a reply to message #408591] Wed, 17 June 2009 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use MERGE instead INSERT.
In 10g, you can omit the "WHEN MATCHED" clause and then get an error.
In 9i, you have to put an invalid formula in WHEN MATCHED (for instance 1/0) and get the associated error.

Regards
Michel
Re: Multiple insert for the same row [message #408614 is a reply to message #408600] Wed, 17 June 2009 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
chintan.patel wrote on Wed, 17 June 2009 08:13
another think is that user one is never do rollback is he finds any error he changes the status of record.

Chintan

MERGE is then definitively the statement you want.

[Sorry my previous answer has been in wait state during 1/2 hour and so is a little bit desynchronize with the other posts.]

Re: Multiple insert for the same row [message #408628 is a reply to message #408612] Wed, 17 June 2009 02:15 Go to previous messageGo to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks

i know the merge statement. with merge statement first user insert the row and he doesn't apply commit, at the same time second user apply merge statement. still first user doesn't apply commit how to find record in WHEN MATCHED clause.

Second thing should i reduce the wait time.

Should u provide any example or related link.

Chintan
Re: Multiple insert for the same row [message #408637 is a reply to message #408628] Wed, 17 June 2009 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
still first user doesn't apply commit how to find record in WHEN MATCHED clause.

It can't unless it is a unique/primary key but in this case it is blocked.
So my solution is not a solution. Sad

Regards
Michel
Re: Multiple insert for the same row [message #408643 is a reply to message #408628] Wed, 17 June 2009 03:42 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If this is such a big issue, it must occur often.
Sounds like a flaw in your business process, rather then your application. How can it be that time and time again different people are handling the same data?
Previous Topic: view modification
Next Topic: Creating dynamic table
Goto Forum:
  


Current Time: Sun Dec 04 10:53:41 CST 2016

Total time taken to generate the page: 0.06067 seconds