Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Select/Insert Locking

Re: Oracle Select/Insert Locking

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: Fri, 13 Feb 2004 07:37:43 -0500
Message-ID: <U93Xb.676$FO.314@fe01.usenetserver.com>


M2 wrote:
> I actually just noted that I said that "Process 2 inserts", I should have
> said "tries to insert" as it actually causes a constraint violation and
> doesn't success (as you'd expect). This is trapped in the code and causes no
> dire consequences I just don't like the design that relies on this as the
> trap.
>
>
> "M2" <m2_at_nowhere.com> wrote in message
> news:M9%Vb.576$KW.36672_at_news.optus.net.au...
>

>>Perhaps I misexplained myself. This DOES use a sequence and they ARE

>
> unique
>
>>(as mentioned they're the primary key after all). Regardless, I do not
>>understand how this actually relates to my problem as it' essentially

>
> about
>
>>how to avoid a race condition.
>>
>>
>>"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
>>news:1076392827.490976_at_yasure...
>>
>>>M2 wrote:
>>>
>>>
>>>>Hi,
>>>>I have an application that records when a user has read a message in a
>>
>>table
>>
>>>>that looks like so:
>>>>
>>>>MESSAGE_ID
>>>>USERNAME
>>>>
>>>>The code that inserts into this table does a select prior to the

>
> insert
>
>>to
>>
>>>>check that it won't try to do something that will cause a constraint
>>>>violation (there's a PK on the two columns). This works fine most of

>
> the
>
>>>>time but every now and again when really hitting the site you can get

>
> a
>
>>>>problem. As far as I can tell the processes overlap so that one

>
> inserts
>
>>in
>>
>>>>between the select and the insert .e.g
>>>>
>>>>process 1 selects to see if cool for insert
>>>>process 2 selects to see if cool for insert
>>>>process 1 finds ok so inserts
>>>>process 2 finds ok so inserts
>>>>
>>>>How can I block against this? I thought perhaps SELECT FOR UPDATE

>
> might
>
>>be
>>
>>>>the key but it didn't seem to do much.
>>>>
>>>>Matt.

Well, you cannot SELECT...FOR UPDATE what isn't there yet.

Catching the EXCEPTION is about the best way to handle this.

The only way to do this without having to at least be prepared to handle the occasional exception is to install Clairvoyant Software (TM). In your example it would look into the future to determine if the first transaction is going to COMMIT or ROLLBACK, and then allow the second transaction to proceed accordingly.

Now installing Clairvoyant Software (TM) is not easy. You first need to obtain a thyotimadine (sp?) chip. Unfortunately they have not yet made it out of the research labs. Azimov Labs is the place to contact.

8^)

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost
Received on Fri Feb 13 2004 - 06:37:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US