Why not just have Connection B trap the Unique Constrait Error and branch to some different code? What would Connection B have done if it had found the record where id=1?
-- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 2/24/2003 2:49 PM, Rick Stephenson <[EMAIL PROTECTED]> wrote: > >OS: Solaris 2.8 >Database: Oracle 9.2.0.2 >� >Situation in chronological order >Connection A: select * from table A where id = 1;� Result: no rows >returned� -- This means I need to insert the row, as it does not >exists yet. >Connection B: select * from table A where id = 1;� Result: no rows >returned� -- This means I need to insert the row, as it does not >exists yet. >Connection A: insert into table A(id) values = 1;� Result: 1 row >inserted >Connection B: insert into table A(id) values = 1;� Result: Unique >constraint violated� --� This is the problem.� How do I avoid this >happening? >� >Question:� How can I force connection B to wait for connection A >to insert the new row before it does the select? >� >If I were updating the row, I could use the "for update" clause to >force the wait.� Is there a clean way to do that for an insert? >� >Thanks for your help, >� >Rick Stephenson >� > > >This email and any files transmitted with it are confidential and >intended solely for the use of the individual or entity to which >they are addressed. This message contains confidential information >and is intended only for the individual named. If you are not the >named addressee you should not disseminate, distribute or copy this >e-mail. Please notify the sender immediately by e-mail if you have >received this e-mail by mistake and delete this e-mail from your >system. If you are not the intended recipient you are notified that >disclosing, copying, forwarding or otherwise distributing or taking >any action in reliance on the contents of this information is strictly >prohibited. > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
