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
adavey_at_competitrack.com
718-482-4200 x106
On 2/24/2003 2:49 PM, Rick Stephenson <RStephenson_at_Ovid.com> 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: adavey_at_competitrack.com
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: ListGuru_at_fatcity.com (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).
Received on Mon Feb 24 2003 - 14:12:54 CST
