Re: Locking on insert

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 23 Mar 2004 23:37:08 -0800
Message-ID: <1080113803.392457@yasure>

Connor McDonald wrote:

> Daniel Morgan wrote:
>

>>Connor McDonald wrote:
>>
>>
>>>Matthew Keene wrote:
>>>
>>>
>>>>"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<8vN7c.60968$1p.1003888_at_attbi_s54>...
>>>>
>>>>
>>>>>What version of Oracle? In 9i there is an "upsert" functionality (see merge)
>>>>>Jim
>>>>
>>>>Oops, sorry, I meant to specify in the original post that this was
>>>>8.1.7 and therefore merge functionality was not available.
>>>
>>>
>>>You can synthesize this with dbms_lock
>>>
>>>hth
>>>connor
>>
>>Would you have a demo you could send me?
>>
>>Thanks.
>>
>>--
>>Daniel Morgan
>>http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
>>http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
>>damorgan_at_x.washington.edu
>>(replace 'x' with a 'u' to reply)

>
>
> For simplicity sake I'll assume a numeric primary key (if its not, you
> can use dbms_utility.get_hash_value).
>
> The algorithm would then change from:
>
> select for update where col = N (the primary key value)
> if found, then update
> if not found, then insert
>
> to:
>
> dbms_lock.request(N)
> select for update where col = N (the primary key value)
> if found, then update
> if not found, then insert
> dbms_lock.release
>
> hth
> connor

Thanks.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Wed Mar 24 2004 - 01:37:08 CST